How to change data rows to columns.

[Melody12177]

I need the easiest method to convert column data to rows. I am not familiar with macro.

Currently it looks like this:

Provider | County
John Smith | Pinellas
Mary Smith | Pasco
John Smith | Pasco
Bob Doe | Dade
Bob Doe | Lake
John Smith | Orange

I want it to look like this:

Provider | County1 | County 2 | County 3
John Smith | Pinellas | Pasco | Orange
Mary Smith | Pasco
Bob Doe | Dade | Lake


I tried transpose but it just puts the data going across in rows but doesn't have the same results.

Thanks!

[steve.smith]

To change Row data to column, follow below steps:

• Copy all rows data that you want to change.
• Open a new sheet or choose a different place to paste.
• Right click any where on the new sheet.
• Choose Paste Special.
• Then Check mark "Transpose"
• Done

[vlady]

hello

assume in column C
array formula to create list w/out duplicates

=IFERROR(INDEX(nameshere,MATCH(0,COUNTIF(nameshere,"<"&nameshere)-SUM(COUNTIF(nameshere,C$1:C1)),0)),"")

assume in column D
array formula to list all under the names drag horizontally first then down.

=IFERROR(INDEX($B$2:$B$7, SMALL(IF($C2=nameshere, ROW($B$2:$B$7)-MIN(ROW($B$2:$B$7))+1, ""), COLUMN(A2))),"")

note: I used named ranges..


here's a sample datarowstocolumns.xlsx

[Melody12177]

Awesome, Thanks so much! Just want I was looking for. Could you explain the logic behind the function so I know how the function was compiled? Thank you!!

[vlady]

Hello
the first code will search for the unique values (names ) and create a list
i just used the named range here for all the range concerned.

the second code will "search" the names row by row created by the code above in the column of names.
then it will display the values row by row besides each name getting all values from that specific name.

sorry about that i'm not that good in explaining.