Need help Pivoting a dynamic amount of data

[BJS13]

Hi,

I'm having trouble using the cross tab for 2 rows of data where not all groups have the same amount of data. Any help is appreciated.

 

[jdunkerley79 · Accepted solution]

I suggest the following:

- Use a multi row formula to create a ranking in groups

- Use two cross tabs using the ranking as a field name - one for CandyID, one for CandyName

- Use dynamic renames to sort names out

- Use a join to put it back together

[DavidxL]

See the attached workflow for an example. It should work with any size of data, but the final order of columns will have to be manual at the moment. I'm sure it could be extended to automatically reorder the columns as well.