Custom Lists

[cmerenbloom]

Hello- I need to figure out a way to make a 2-column list, pulling values from several other lists.
I want the first column to list the items, and the second column to indicate from which original list the item came. If items appear in more than one list, they should appear multiple times.

I want to take this:
[List 1][List 2][List 3]
A A F
B D B
C E E

And get this:
[Item][List that Item is In]
A List 1
A List 2
B List 1
B List 3
C List 2
D List 2
E List 2
E List 3
F List 3

[sandy666]

With PowerQuery (Get&Transform)

is that what you want?

[cmerenbloom]

Yes! I just don't know the steps to get there...

[sandy666]

steps in PowerQuery or ... ?

It's a good point to read this: Getting Started with Get & Transform in Excel 2016

[cmerenbloom]

Correct, I've never used PowerQuery

[sandy666]

So read article from the link above

in the meantime:
click on the range
go to DATA tab - Get&Transform
From Table
in new window select all columns
from the menu select Unpivot Columns
sort column(s) as you wish
Close&Load result query into the sheet

[ranman256]

try:

Please Login or Register  to view this content.