Forum Discussion

jthomsoun's avatar
jthomsoun
Copper Contributor
Feb 23, 2023

How to have column values arranged in pivot as in the picture?

Hello,   In pivot, I basically want to arrange 2 values - Agent & Ticket ID.   The current view is like this,   I want to view as like this,                   The pivot is...
excel
Excel on Mac
Lorenzo's avatar
Lorenzo
Silver Contributor
Feb 23, 2023

Hijthomsoun 

 

No way with a PivotTable and the 2 fields you exposed + bad luck you're on Mac (a cheaty option exists on Windows with Power Query). Alternative if you run 365:

 

 

in E4:

=TicketsByAgent(TableSource[Agent], TableSource[Ticket ID])


in K4:

=TicketsByAgent(TableSource[Agent],TableSource[Ticket ID],TRUE)

 

Where TicketsByAgent is LAMBDA function:

=LAMBDA(Agents, Tickets, [remove_duplicates],
  LET(
    StackAgentTickets, LAMBDA(seed, agent_k,
      LET(
        tickets_agent_k, FILTER(Tickets, Agents=agent_k),
        HSTACK(seed, SORT(IF(remove_duplicates, UNIQUE(tickets_agent_k), tickets_agent_k)))
      )
    ),
    u_Agents, TOROW(SORT(UNIQUE(Agents))),
    VSTACK(u_Agents, IFNA(DROP(REDUCE("",u_Agents, StackAgentTickets),,1),""))
  )
)

Resources