Transposing and compressing data

NArawna

New Member
Joined
Dec 5, 2018
Messages
10
Hi, I am exporting from a database as a csv which provides the following ID,NAME,RATING

ID1BEN1
ID2BEN1
ID3BEN2
ID4BEN3
ID5JAMES1
ID1SAM1
ID4SAM2
ID2DAN1
ID9DAN2
ID10DAN2

<tbody>
</tbody>

I need to transform this into the following

ID1ID2ID3ID4ID5ID6ID7ID8ID9ID10
BEN1123
JAMES1
SAM12
DAN122

<tbody>
</tbody>

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?
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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.
 
Last edited:

BGY23

Well-known Member
Joined
Aug 13, 2008
Messages
515
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

New Member
Joined
Dec 5, 2018
Messages
10
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

New Member
Joined
Dec 5, 2018
Messages
10

ADVERTISEMENT

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

Well-known Member
Joined
Aug 13, 2008
Messages
515
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

New Member
Joined
Dec 5, 2018
Messages
10
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,992
Messages
5,526,109
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top