how to convert horizontal data table to vertical when there are duplicate head values

[wzhu717]

hello, 

i need help to convert horizontal data to vertical. currently i have the some headers are the same values and when i tried to do a compose and cross tool it group the headers together. so here are the data and i want it to look like this. I need help.

[danilang]

Hi there @wzhu717 

The trick here is add a record ID and then use this as a key field in the Transpose and as the Column Headers field in the Cross Tab

InputOutput

Dan

[wzhu717]

thanks, i tried to that way but then it starts to skipping rows.here is what it looks like.

[afv2688]

Hi @wzhu717 ,

As @danilang said thats a way to do it, but if you want to set the first field as a column name:

You can use this one too :)

Cheers

[afv2688]

You have on your workflow a sample tool. Maybe that is the reason for rows being skipped

cheers

[danilang]

Hi @wzhu717 

Does your input data have holes in the first column?  Have you tried my workflow on your data?

Dan

[wzhu717]

yes i tried your method. all i want to do is flipping the entire data to vertical so i can do a dynamic select after that base on the Name. the reason i used a sample tool its because there are data in the first 4 rows i dont need to be included. 

[danilang]

After looking at your image more closely I see what is happening.  The Crosstab tool sorts the rows alphabetically.  This isn't a problem if you have less than 10 rows but with more than that you have the order F1, F10, F2, F3, etc, since F10 comes before F2 alphabetically.  This new version builds a row number field and uses that to order the rows.  You shouldn't have any skipped rows now.

Dan

[wzhu717]

this is what i am looking for. thank you so much Dan. much appreciated. 

[danilang · Accepted solution]

Hi @wzhu717 

Glad to help.  Since this is what you're looking for please consider marking this solution as accepted.  This will help people to find a solution to similar problems that they may have in the future

Dan