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

megera716

Board Regular
Joined
Jan 3, 2013
Messages
93
Office Version
365
Platform
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>
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,831
like this?

IDAttribute
10
A
C
20
B
E
F
30
D

or post expected result
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,831
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:

megera716

Board Regular
Joined
Jan 3, 2013
Messages
93
Office Version
365
Platform
Windows
Shoot, I don't think he has PowerQuery. Is there another way?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,831
Excel version?

PowerQuery = Get&Transform

I don't know another way, maybe someone else
 

megera716

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

Well-known Member
Joined
Oct 24, 2015
Messages
5,831
You are welcome
Have a nice day

hope you didn't load QueryTable into the sheet :)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,630
Messages
5,488,004
Members
407,617
Latest member
Samanthad2007

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top