De-duplicating/merging rows while keeping any data

[garyarubin]

I need to determine whether any people have downloaded any applications. But the database that spits out whether a person has done so can only generate lists for one application at a time. So, I have one spreadsheet showing that Bob downloaded Messenger, and another showing that Bob downloaded WhatsApp. Ultimately, I had 50 spreadsheets like this and had to combine them into a single spreadsheet. I did this by copying the original in each case into a destination file, creating a new column for each app. So, the name of each person is in Column A, and then, if that person has downloaded an app, that information appears in Column B . . . Column ZZZ. The result is that this new file is ~50,000 rows long, with multiple repeats in Column A. In this example, Bob now has two rows, showing his name twice in Column A (A4 and A5), Messenger in Column C (C4), and WhatsApp in Column DD (DD). Is there a way to say, if the name Bob repeats in Column A, then combine those rows into a single row, while preserving the value from Columns C and DD (or whatever columns are populated)? Thanks!

 

[Kerryx]

Could you provide some dummy data to allow us to work with use the XL2BB addin provided in the link
XL2BB

 

[garyarubin]

Thanks very much. I can't add in XL2BB, but here is a screenshot. You see Joe Jones has a number of different channels, all imported from different spreadsheets. Question is whether I can collapse all those rows into a single row showing multiple channels.

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Device enrolled", "Employee Nr", "Type"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Device enrolled	Employee Nr	Type	Channel1	Channel2	Channel3	Channel4	Channel5	Channel6	Channel7	Channel8	Channel9	Channel10
2	Jjones	1234	Employee										Channel 10
3	Jjones	1234	Employee									Channel 9
4	Jjones	1234	Employee
5	Jjones	1234	Employee
6	Jjones	1234	Employee
7	Jjones	1234	Employee						Channel 6
8	Jjones	1234	Employee
9	Jjones	1234	Employee				Channel 4
10	Jjones	1234	Employee			Channel 3
11	Jjones	1234	Employee		Channel 2
12	Jjones	1234	Employee	Channel 1
13
14
15	Device enrolled	Employee Nr	Type	Channel10	Channel9	Channel6	Channel4	Channel3	Channel2	Channel1
16	Jjones	1234	Employee	Channel 10	Channel 9	Channel 6	Channel 4	Channel 3	Channel 2	Channel 1
Sheet1

 

[garyarubin]

This was fantastic, thank you. It took me a bit to debug given how much more complex and error-ridden the real spreadsheet was, but this worked, and I really appreciate it.

 

[alansidman]

Thanks for the feedback and glad to hear it worked for you.