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

megera716

Board Regular
Joined
Jan 3, 2013
Messages
139
Office Version
  1. 365
Platform
  1. Windows
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?

ID #ABCDEF
1011
20111
301

<tbody>
</tbody>
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
source
IDABCDEF
10​
1​
1​
20​
1​
1​
1​
30​
1​

then PowerQuery:

Code:
[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:
IDAttributeValue
10​
A
1​
10​
C
1​
20​
B
1​
20​
E
1​
20​
F
1​
30​
D
1​

and on the end PivotTable from QueryTable

Pivot
IDAttribute
10
A
C
20
B
E
F
30
D

edit:
don't quote whole post, please!
 
Last edited:
Upvote 0
Shoot, I don't think he has PowerQuery. Is there another way?
 
Upvote 0
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!
 
Upvote 0
You are welcome
Have a nice day

hope you didn't load QueryTable into the sheet :)
 
Last edited:
Upvote 0
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

ID​
Provider​
10​
A​
C​
20​
B​
E​
F​
30​
D​

<tbody>
</tbody>


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

M.
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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