Transpose data

raaool1701

New Member
Joined
Sep 17, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hello,

My data is organized in rows as shown below:
(Sorry about the formatting of the data below)

The same person can be assigned multiple badges. However, the same badge cannot be assigned to more than 1 person.

NameEmailBadge ID
aa1123
aa1234
aa1345
bb1456
cc1567
dd1678
dd1789

I would like to convert it to a single row per name/email either with a formula, pivot or some form of automation so that the final product looks something like

NameEmailBadge ID1Badge ID2Badge ID3
aa1123234345
bb1456
cc1567
dd1678789

Possible? Thanks in advance.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi & welcome to MrExcel.
How about
+Fluff New.xlsm
ABCDEF
1NameEmailBadge ID
2aa1123
3aa1234
4aa1345
5bb1456
6cc1567
7dd1678
8dd1789
9
10
11aa1123234345
12bb1456
13cc1567
14dd1678789
15
Master
Cell Formulas
RangeFormula
A11:B14A11=UNIQUE(A2:B8)
C11:E11,C14:D14,C12:C13C11=TRANSPOSE(FILTER($C$2:$C$8,$A$2:$A$8=A11))
Dynamic array formulas.
 
Upvote 0
another approach with Power Query
NameEmailBadge IDNameEmailBadge ID.1Badge ID.2Badge ID.3
aa1123aa1123234345
aa1234bb1456
aa1345cc1567
bb1456dd1678789
cc1567
dd1678
dd1789

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Name", "Email"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Badge ID", each [Count][Badge ID]),
    Extract = Table.TransformColumns(List, {"Badge ID", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    MaxCount = List.Max(Table.AddColumn(Extract, "SCount", each List.Count(Text.Split([Badge ID],",")))[SCount]),
    Split = Table.SplitColumn(Extract, "Badge ID", Splitter.SplitTextByDelimiter(","), MaxCount)
in
    Split
 
Upvote 0
Thank you Fluff for the response. I have Office Professional Plus 2016. I don't see the UNIQUE formula or the FILTER formula available. Is there an add-in or something I need to download?

Thanks Sandy666. I will give that a try in a bit and see if that works.
 
Upvote 0
Another formula solution
+Fluff New.xlsm
ABCDEF
1NameEmailBadge ID
2aa1123
3aa1234
4aa1345
5bb1456
6cc1567
7dd1678
8dd1789
9
10
11aa1123234345 
12bb1456   
13cc1567   
14dd1678789  
15      
16      
Master
Cell Formulas
RangeFormula
A11:A16A11=IFERROR(INDEX($A$2:$A$8,AGGREGATE(15,6,(ROW($A$2:$A$8)-ROW($A$2)+1)/(ISNA(MATCH($A$2:$A$8,A$10:A10,0))),1)),"")
B11:B16B11=IF(A11="","",INDEX($B$2:$B$8,MATCH(A11,$A$2:$A$8,0)))
C11:F16C11=IFERROR(INDEX($C$2:$C$8,AGGREGATE(15,6,(ROW($C$2:$C$8)-ROW($C$2)+1)/($A$2:$A$8=$A11),COLUMNS($C11:C11))),"")
 
Upvote 0
Another formula solution
+Fluff New.xlsm
ABCDEF
1NameEmailBadge ID
2aa1123
3aa1234
4aa1345
5bb1456
6cc1567
7dd1678
8dd1789
9
10
11aa1123234345 
12bb1456   
13cc1567   
14dd1678789  
15      
16      
Master
Cell Formulas
RangeFormula
A11:A16A11=IFERROR(INDEX($A$2:$A$8,AGGREGATE(15,6,(ROW($A$2:$A$8)-ROW($A$2)+1)/(ISNA(MATCH($A$2:$A$8,A$10:A10,0))),1)),"")
B11:B16B11=IF(A11="","",INDEX($B$2:$B$8,MATCH(A11,$A$2:$A$8,0)))
C11:F16C11=IFERROR(INDEX($C$2:$C$8,AGGREGATE(15,6,(ROW($C$2:$C$8)-ROW($C$2)+1)/($A$2:$A$8=$A11),COLUMNS($C11:C11))),"")

Bingo! That worked like a charm! Thank you thank you thank you. You saved the day for me.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top