Transposing and compressing data

[NArawna]

Hi, I am exporting from a database as a csv which provides the following ID,NAME,RATING

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID1[/TD]
[TD]BEN[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ID2[/TD]
[TD]BEN[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ID3[/TD]
[TD]BEN[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]ID4[/TD]
[TD]BEN[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]ID5[/TD]
[TD]JAMES[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ID1[/TD]
[TD]SAM[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ID4[/TD]
[TD]SAM[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]ID2[/TD]
[TD]DAN[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ID9[/TD]
[TD]DAN[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]ID10[/TD]
[TD]DAN[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

I need to transform this into the following

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]ID1[/TD]
[TD]ID2[/TD]
[TD]ID3[/TD]
[TD]ID4[/TD]
[TD]ID5[/TD]
[TD]ID6[/TD]
[TD]ID7[/TD]
[TD]ID8[/TD]
[TD]ID9[/TD]
[TD]ID10[/TD]
[/TR]
[TR]
[TD]BEN[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JAMES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SAM[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DAN[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

As you can see it has changed the ID from the horizontal to the vertical,
Filled in the missing ID numbers, in this case 6,7,and 8
it has changed the NAME from individual instances to a single unique instance for each
it has assigned the RATING under the ID for each NAME

My only progress towards the change so far is using paste-special with transpose to change the values from a horizontal to vertical layout.
Onward I am unsure and assume it would be quite VBA heavy?

 

[Special-K99]

Are you able to manually enter the ID numbers across as a header row in row 1 or are there too many?
Are you able to manually add the names as a header column in column A or are there too many?

If you're able to do both then a formula solution is possible otherwise it would probably have to be VBA which I'm not an expert.

 

[BGY23]

Hi,
You can do what you want to do wit a pivot table. I just did it using your source data. You Just need to give your columns headings like ID, Name an rating and he pivot table will do the rest

 

[NArawna]

Are you able to manually enter the ID numbers across as a header row in row 1 or are there too many?
Are you able to manually add the names as a header column in column A or are there too many?

If you're able to do both then a formula solution is possible otherwise it would probably have to be VBA which I'm not an expert.

Manual entry is not really an option as there are over 100 ID's.

 

[NArawna]

Hi,
You can do what you want to do wit a pivot table. I just did it using your source data. You Just need to give your columns headings like ID, Name an rating and he pivot table will do the rest

Hi, could you let me know the pivot settings you used?
Thanks.

 

[BGY23]

Hi
Copy your data to B1:C11.
Put column headings A1:C1, ID, Name, Rating.
Create pivot table
Rows = Names
Columns = ID
Values = ratings

If you don't know how to do it PM me your email and I'll send you the file I made.

Brian

 

[NArawna]

Hi
Copy your data to B1:C11.
Put column headings A1:C1, ID, Name, Rating.
Create pivot table
Rows = Names
Columns = ID
Values = ratings

If you don't know how to do it PM me your email and I'll send you the file I made.

Brian

Got it, thank you.