Not sure how to title this - longer explanation and example table provided inside!

[megera716]

Trying to help a friend. A, B, C. etc. are names of software providers and ID numbers are assigned to customers. Friend has this data set and is trying to get a list of all the software providers that a given ID number has (not just a count). I tried copying the data and then saying if B2>=1, replace with A1 so that at least the names would be in there instead of a "1" and then Pivot off of it. But then that puts you in the position of having to add all the software provider names as column values in the Pivot (and there are more like 40-50 providers, not 6).

He would like to see if a customer has Provider A AND Provider D (or E or F or...). Is that even possible?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID #[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ID[/td][td=bgcolor:#DDEBF7]Attribute[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10​

[/td][td]A[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]C[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

20​

[/td][td]B[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]E[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]F[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

30​

[/td][td]D[/td][/tr]
[/table]

or post expected result

 

[megera716]

like this?

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]ID[/TD]
[TD="bgcolor: #DDEBF7"]Attribute[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

10​

[/TD]
[TD]A[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[TD]C[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

20​

[/TD]
[TD]B[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[TD]E[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[TD]F[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

30​

[/TD]
[TD]D[/TD]
[/TR]
</tbody>[/TABLE]


or post expected result

Yes, I think that would work! How did you do it?!

 

[sandy666]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]source[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]ID[/td][td=bgcolor:#5B9BD5]A[/td][td=bgcolor:#5B9BD5]B[/td][td=bgcolor:#5B9BD5]C[/td][td=bgcolor:#5B9BD5]D[/td][td=bgcolor:#5B9BD5]E[/td][td=bgcolor:#5B9BD5]F[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

10​

[/td][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

20​

[/td][td][/td][td]

1​

[/td][td][/td][td][/td][td]

1​

[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

30​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]
[/table]

then PowerQuery:

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UnPivot = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value")
in
    UnPivot[/SIZE]

QueryTable looks like this but this is in background:
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]ID[/td][td=bgcolor:#70AD47]Attribute[/td][td=bgcolor:#70AD47]Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

10​

[/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10​

[/td][td]C[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

20​

[/td][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

20​

[/td][td]E[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

20​

[/td][td=bgcolor:#E2EFDA]F[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

30​

[/td][td]D[/td][td]

1​

[/td][/tr]
[/table]

and on the end PivotTable from QueryTable

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Pivot[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ID[/td][td=bgcolor:#DDEBF7]Attribute[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10​

[/td][td]A[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]C[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

20​

[/td][td]B[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]E[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]F[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

30​

[/td][td]D[/td][/tr]
[/table]

edit:
don't quote whole post, please!

 

[megera716]

Shoot, I don't think he has PowerQuery. Is there another way?

 

[sandy666]

Excel version?

PowerQuery = Get&Transform

I don't know another way, maybe someone else

 

[megera716]

Excel version?

PowerQuery = Get&Transform

I don't know another way, maybe someone else

Totally didn't connect those dots at first but yes, we were able to run it and it worked perfectly! Thanks so much!

 

[sandy666]

You are welcome
Have a nice day

hope you didn't load QueryTable into the sheet

 

[Marcelo Branco]

Maybe something like this

1. Normalize data
see
https://www.youtube.com/watch?v=xmqTN0X-AgY

2. Delete blank row
F5 > Special > Blanks

3. Create a pivot table like this

[TABLE="class: grid"]
<tbody>[TR]
[TD]

ID​

[/TD]
[TD]

Provider​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

A​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

30​

[/TD]
[TD]

D​

[/TD]
[/TR]
</tbody>[/TABLE]


Tabular format
Subtotals --> none
Grand Total row --> No

M.