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.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
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.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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
 

raaool1701

New Member
Joined
Sep 17, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
I have Office Professional Plus 2016.
In that case please change your account details. Members often tailor their replies based on this information.
 

raaool1701

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

ADVERTISEMENT

Oh. Whoops, my bad. Updated my profile now.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
Thanks for that (y)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
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))),"")
 

raaool1701

New Member
Joined
Sep 17, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

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
Top