Compare & combine tables using dynamic arrays

[sharshra]

I´m trying to compare 2 tables & get an output based on the following logic.

Table 1 is the source table. Table 2 is for comparison. In table 1, I want to take the names having 'status=ok' & check if they have 'keep<>y' in table 2. Take those values & provide an output. I´m trying to use dynamic arrays to the the desired output, but not successful. I'm sure it is quite simple for the experts in this forum. Can you help please?

Table 1

excel problems.xlsx
	B	C	D
1	table 1
2	name	status	details
3	dfds	ok	kjhk
4	gfh	not ok
5	uir	not ok	5t
6	qewr	ok	nmnm
7	xcvz	ok	xcx
8	cmvn	ok	dsds
9	dhgdh	ok	oioi
10	teywu	not ok
11	sdf	ok
12	gd	ok
13	hkfi	not ok
Sheet9

Table 2

excel problems.xlsx
	G	H	I
1	table 2
2	name	keep	comments
3	qewr		34u
4	dfds	y
5	xcvz
6	sdf	n	com2
7	cmvn	y
8	uir	n	no7
9	dhgdh	lk	8y
Sheet9

Desired output

excel problems.xlsx
	L	M	N	O	P
1	desired output
2	name	status	details	keep	comments
3	qewr	ok	nmnm		34u
4	xcvz	ok	xcx
5	dhgdh	ok	oioi	lk	8y
6	sdf	ok		n	com2
Sheet9

 

[hagia_sofia]

Hello, here is an attempt:

=LET(
a,FILTER(B3:D13,ISNUMBER(XMATCH(B3:B13,G3:G9))),
b,XMATCH(CHOOSECOLS(a,1),G3:G9),
c,CHOOSEROWS(H3:I9,b),
d,HSTACK(a,c),
e,FILTER(d,(CHOOSECOLS(d,2)="ok")*(CHOOSECOLS(d,4)<>"y")),
IF(ISBLANK(e),"",e))

 

[sharshra]

Thanks a lot, @hagia_sofia. It is of great help

 

[smozgur]

Python formula as an alternative (entered by =PY and Tab)

table1 = xl("B2:D13", headers=True)
table2 = xl("G2:I9", headers=True)
byStatus = table1[table1["status"] == "ok"]
byKeep = table2[table2["keep"] != "y"]
table3 = pd.merge(byStatus, byKeep, on="name").replace({None: ""})

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		table1					table 2
2		name	status	details			name	keep	comments			name	status	details	keep	comments
3		dfds	ok	kjhk			qewr		34u			qewr	ok	nmnm		34u
4		gfh	not ok				dfds	y				xcvz	ok	xcx
5		uir	not ok	5t			xcvz					dhgdh	ok	oioi	lk	8y
6		qewr	ok	nmnm			sdf	n	com2			sdf	ok		n	com2
7		xcvz	ok	xcx			cmvn	y
8		cmvn	ok	dsds			uir	n	no7
9		dhgdh	ok	oioi			dhgdh	lk	8y
10		teywu	not ok
11		sdf	ok
12		gd	ok
13		hkfi	not ok
Sheet1

 

[sharshra]

I´m trying to get similar results using another method & getting error. Not sure what is going wrong. Can the experts point me the mistake please?

excel problems.xlsx
	B
15	#VALUE!
merge tables

Cell Formulas
Range	Formula
B15:D21	B15	=LET( srct1, B3:D13, srct1Name, B3:B13, srct1Status, C3:C13, reft2, G3:I9, reft2Name, G3:G9, reft2Keep, H3:H9, tSrcStatusOk, FILTER(srct1,srct1Status="ok"), tRefKeepNoY, FILTER(reft2, reft2Keep="y"), fOutput, MAP(tSrcStatusOk, LAMBDA(x, XLOOKUP(x,tRefKeepNoY, CHOOSECOLS(tRefKeepNoY,3)))), fOutput)
Dynamic array formulas.

 

[alansidman]

An alternative with Power Query

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FilterRows = Table.SelectRows(T1, each ([status] = "ok")),
    T2=Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    FilterRows2 = Table.SelectRows(T2, each ([keep] <> "y")),
    MQ = Table.NestedJoin(FilterRows, {"name"}, FilterRows2, {"name"}, "Table2", JoinKind.FullOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(MQ, "Table2", {"keep", "comments"}, {"keep", "comments"})

in
    #"Expanded Table2"

 

[sharshra]

Thanks, @alansidman. It's really useful.

However, I'm trying to get it using dynamic arrays with MAP or REDUCE functions, but it is going wrong somewhere. Requesting the experts to point the mistake.

 

[hagia_sofia]

One problem could be that "tSrcStatusOk" consists of 3 columns which is then provided to XLOOKUP. As a first step e.g. use CHOOSECOLS or INDEX to limit lookup_value to one column, i.e. the one which is to be compared against lookup_array.

 

[sharshra]

@hagia_sofia, Thanks for your suggestion. I made the following changes & I could see it is working without error.

But, if I give tRefKeepNoY in XLOOKUP, it throws ·VALUE error. Why it happens? It should be possible to use the entire array instead of 1 column?

Also, if I want to pull 2nd & 3rd column from tRefKeepNoY, it throws #CALC error. Any reasons for this?

No error

excel problems.xlsx
	B	C	D	E
15	dfds	ok	kjhk	#N/A
16	qewr	ok	nmnm	34u
17	xcvz	ok	xcx	0
18	cmvn	ok	dsds	#N/A
19	dhgdh	ok	oioi	8y
20	sdf	ok	0	com2
21	gd	ok	0	#N/A
merge tables

Cell Formulas
Range	Formula
B15:E21	B15	=LET( srct1, B3:D13, srct1Name, B3:B13, srct1Status, C3:C13, reft2, G3:I9, reft2Name, G3:G9, reft2Keep, H3:H9, tSrcStatusOk, FILTER(srct1,srct1Status="ok"), tRefKeepNoY, FILTER(reft2, reft2Keep<>"y"), fOutput, MAP(CHOOSECOLS(tSrcStatusOk,1), LAMBDA(x, XLOOKUP(x,CHOOSECOLS(tRefKeepNoY,1), CHOOSECOLS(tRefKeepNoY,3)))), HSTACK(tSrcStatusOk,fOutput))
Dynamic array formulas.

#VALUE error:

excel problems.xlsx
	B	C	D	E
15	dfds	ok	kjhk	#VALUE!
16	qewr	ok	nmnm	#VALUE!
17	xcvz	ok	xcx	#VALUE!
18	cmvn	ok	dsds	#VALUE!
19	dhgdh	ok	oioi	#VALUE!
20	sdf	ok	0	#VALUE!
21	gd	ok	0	#VALUE!
merge tables

Cell Formulas
Range	Formula
B15:E21	B15	=LET( srct1, B3:D13, srct1Name, B3:B13, srct1Status, C3:C13, reft2, G3:I9, reft2Name, G3:G9, reft2Keep, H3:H9, tSrcStatusOk, FILTER(srct1,srct1Status="ok"), tRefKeepNoY, FILTER(reft2, reft2Keep<>"y"), fOutput, MAP(CHOOSECOLS(tSrcStatusOk,1), LAMBDA(x, XLOOKUP(x,tRefKeepNoY, CHOOSECOLS(tRefKeepNoY,3)))), HSTACK(tSrcStatusOk,fOutput))
Dynamic array formulas.

#CALC

excel problems.xlsx
	B
15	#CALC!
merge tables

Cell Formulas
Range	Formula
B15	B15	=LET( srct1, B3:D13, srct1Name, B3:B13, srct1Status, C3:C13, reft2, G3:I9, reft2Name, G3:G9, reft2Keep, H3:H9, tSrcStatusOk, FILTER(srct1,srct1Status="ok"), tRefKeepNoY, FILTER(reft2, reft2Keep<>"y"), fOutput, MAP(CHOOSECOLS(tSrcStatusOk,1), LAMBDA(x, XLOOKUP(x,CHOOSECOLS(tRefKeepNoY,1), CHOOSECOLS(tRefKeepNoY,2,3)))), HSTACK(tSrcStatusOk,fOutput))

 

[hagia_sofia]

Both problems seem to be of the same kind as the previous one: XLOOKUP will work here only with one column in any argument, e.g. the latter could work with XLOOKUP if you HSTACK two XLOOKUPs, one per column (the original bypass above was to use CHOOSEROWS).