Query or Xlookup: filling in material from assembly list

[shredr]

I have to file a regulatory report
To prep I need to fill in what parts are made or assembled from.

I have a list of about a thousand P/N that needs the material column populated (ListA).

Some PN are made from a single item, some are assemblies.

To fill this I need to query / lookup a second list that has almost 10000 items (ListB)
Some of the items in ListB are assemblies, so I need to put all assembly items to the right of the PN in ListA

Logically, I need to compare PN between the lists, and if PN matches, fill in the cell to the right.
If the PN in ListB has several instances of a PN, I need to keep filling cells to the right until there is not a match for the PN, then move on to the next PN in ListA
Does that make sense? (example attached)

Thanks!

table to be filled

example to fill in material list.xlsx
	A	B	C	D	E	F	G	H	I	J
1	PN	Material
2	1	A						want to populate ColB using data from other tab, what's shown is desired results
3	2	B						if multiple items used for assy, want to fill addional cells to the right
4	3	C
5	4	D
6	5	E	EE	EEE
7	6	F
8	7	g
9	8	H	Hh	HhH	HhHH	HHhHH
10	9	J
11	10	K	KJ
12
PN matl to be filled

Table to use to lookup materials

example to fill in material list.xlsx
	A	B	C	D
1	PN	matl used / assy itms
2	1	A
3	2	B
4	3	C
5	4	D
6	5	E
7	5	EE
8	5	EEE
9	6	f
10	7	g
11	8	H
12	8	Hh
13	8	HhH
14	8	HhHH
15	8	HHhHH
16	9	J
17	10	K
18	10	KJ
19
20
21
Sheet2

 

[hagia_sofia]

Hello, could you please share here what version of Excel are you using?

 

[shredr]

Think it's 365

Hello, could you please share here what version of Excel are you using?

365
(which is awful, why are MS upgrades so frustrating?)

 

[lrobbo314]

Adjust ranges as necessary.

EXCEL
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	PN	Material										PN	matl used / assy itms
2	1	A										1	A
3	2	B										2	B
4	3	C										3	C
5	4	D										4	D
6	5	E	EE	EEE								5	E
7	6	F										5	EE
8	7	g										5	EEE
9	8	H	Hh	HhH	HhHH	HHhHH						6	f
10	9	J										7	g
11	10	K	KJ									8	H
12												8	Hh
13												8	HhH
14												8	HhHH
15												8	HHhHH
16												9	J
17												10	K
18			EE	EEE								10	KJ
Sheet1

Cell Formulas
Range	Formula
C2:C5,C7:C8,C10:C11,C9:F9,C6:D6	C2	=IFERROR(DROP(REDUCE("",$M$2:$M$18,LAMBDA(s,c,IF(UPPER(LEFT(c))=UPPER(B2),HSTACK(s,c),s))),,2),"")

 

[alansidman]

An alternative is with Power Query. Join the two tables and then pivot the data.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	PN	Material		PN	matl used / assy itms		PN	1	2	3	4	5
2	1	A		1	A		1	A
3	2	B		2	B		2	B
4	3	C		3	C		3	C
5	4	D		4	D		4	D
6	5	E		5	E		5	E	EE	EEE
7	6	F		5	EE		6	f
8	7	g		5	EEE		7	g
9	8	H		6	f		8	H	Hh	HhH	HhHH	HHhHH
10	9	J		7	g		9	J
11	10	K		8	H		10	K	KJ
12				8	Hh
13				8	HhH
14				8	HhHH
15				8	HHhHH
16				9	J
17				10	K
18				10	KJ
Sheet1

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MQ = Table.NestedJoin(T1, {"PN"}, T2, {"PN"}, "T3", JoinKind.FullOuter),
    #"Added Custom" = Table.AddColumn(MQ, "Custom", each Table.AddIndexColumn([T3],"Index",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"PN", "matl used  / assy itms", "Index"}, {"PN", "matl used  / assy itms", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-US")[Index]), "Index", "matl used  / assy itms")
in
    #"Pivoted Column"

 

[lrobbo314]

Not sure if it would make a difference performance-wise. But, this is how I would do it with PQ.

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MQ = Table.NestedJoin(T1, {"PN"}, T2, {"PN"}, "T3", JoinKind.FullOuter),
    Transform = Table.TransformColumns(MQ,{{"T3", each if List.Count(_[#"matl used  / assy itms"]) > 1 then List.Skip(_[#"matl used  / assy itms"]) else null}}),
    Split = Table.SplitColumn(Table.TransformColumns(Transform,{{"T3", each if _ <> null then Text.Combine(_,"~") else null}}),"T3",Splitter.SplitTextByDelimiter("~"),{"1"..Text.From(List.Max(List.Transform(Transform[T3],each try List.Count(_) otherwise 0)))})
in
    Split

 

[shredr]

Never used power query before, I'll have to take a look at how it works...how steep is the learning cureve?

 

[lrobbo314]

Maybe not as steep as VBA. I don't know. It's pretty approachable. And just like VBA, you can definitely get into the weeds with it and deep dive. But, for general stuff, like I said, I think it's pretty easy to get into.

 

[alansidman]

I found that this book is a great primer.

Master Your Data with Power Query in Excel and Power BI

Despite the moniker "data monkey," we information workers are often more like data magicians. Our data seldom enters our world in a ready-to-consume format; it can take hours of cleaning, filtering, and reshaping to get things ready to go. Power Query will make this process faster the first time...

www.mrexcel.com