Cross Table

[Saro_2306]

Hi Friends,

I have Data Column name with Month and Category ( i.e Jan 2021 - Attrition). 3 columns for each Month. While loading excel with cross table i can able to direct column name only. But my need is Month should be in separate column , Category should be in 2nd column and data should be in 3rd Column. Please refer attached image for easy understand. 

Is there any way to do like this. Any body can help. Thanks in Advance.

Jan - 2019 - Annualized

Jan - 2019 - Total Attrition

Jan - 2019 - Headcount

Feb - 2019 - Annualized

Feb - 2019 - Total Attrition

Feb - 2019 - Headcount

[rubenmarin]

Hi, I didn't tested but maybe you can do this on 2 steps

- First step: standar Crosstabla, creating a "ColumnHeader" field

- Second step creating the fields you want from this one:

  Date#(Left([ColumnHeader], Index([ColumnHeader],'2021')+3),'MMM - YYYY) as Month,
  Mid([ColumnHeader],  Index([ColumnHeader],'2021')+4) as Category

The '2021' string would be better if it's changed by something more dynamic, like a variable that stores Year(Today()) or something like that, it depends on the data you want to load.