AMERGE

[Xlambda]

AMERGE arrays Merge, same functionality as in Power Querry join/merge scenario called Left Outer. Calls ATEXTJOIN , ARRANGE , APPEND2H
Other functions used on minisheet ARRANGE , APIVOT

=LAMBDA(l,r,klf,krt,
    LET(d,"|",kl,UNIQUE(IF(klf=0,1,klf),1),kr,UNIQUE(IF(krt=0,1,krt),1),
        cl,COLUMNS(l),ql,SEQUENCE(,cl),cr,COLUMNS(r),qr,SEQUENCE(,cr),rl,ROWS(l),sl,SEQUENCE(rl),rr,ROWS(r),sr,SEQUENCE(rr),
        ckr,COLUMNS(kr),a,INDEX(l,sl,kl),b,INDEX(r,sr,kr),ta,ATEXTJOIN(a,,,d),tb,ATEXTJOIN(b,,,d),
        x,XMATCH(ta,tb),ar,ARRANGE(INDEX(r,x,qr),kr),
        ml,OR(ISNA(XMATCH(kl,ql))),mr,OR(ISNA(XMATCH(kr,qr))),
        IF(OR(ml,mr),"check keys",APPEND2H(l,ar,ckr+1))
    )
)

LAMBDA 7.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	PQ name: Left Outter (Classic lookup in right array)															PQ name: Right Outter (Classic lookup in left array)
2	ex. 1									=AMERGE(A3:C15,E3:H11,{3,2},{2,4})						=AMERGE(E3:H11,A3:C15,{2,4},{3,2})
3	Units	Color	Product		Code	Product	Price	Color		Units	Color	Product	Code	Price		Code	Product	Price	Color	Units
4	48	Red	Quad		CR-r	Carlota	26	Red		48	Red	Quad	QD-r	43		CR-r	Carlota	26	Red
5	156	Blue	Quad		KK-b	Kiki	13	Blue		156	Blue	Quad	QD-b	41		KK-b	Kiki	13	Blue
6	168	Red	Quad		QD-r	Quad	43	Red		168	Red	Quad	QD-r	43		QD-r	Quad	43	Red	48
7	132	Blue	Carlota		SH-r	Sunshine	19	Red		132	Blue	Carlota				SH-r	Sunshine	19	Red	108
8	72	Blue	Carlota		CR-g	Carlota	24	Green		72	Blue	Carlota				CR-g	Carlota	24	Green
9	108	Red	Sunshine		QD-b	Quad	41	Blue		108	Red	Sunshine	SH-r	19		QD-b	Quad	41	Blue	156
10	128	Blue	Quad		SH-b	Sunshine	18	Blue		128	Blue	Quad	QD-b	41		SH-b	Sunshine	18	Blue	24
11	96	Red	Gigi		KK-r	Kiki	20	Red		96	Red	Gigi				KK-r	Kiki	20	Red
12	60	Red	Sunshine							60	Red	Sunshine	SH-r	19
13	24	Blue	Sunshine							24	Blue	Sunshine	SH-b	18		Obs:	If there are duplicates of keys in
14	120	Blue	Carlota							120	Blue	Carlota					left array,only first occurrence is shown
15	24	Blue	Quad							24	Blue	Quad	QD-b	41
16
17	ex. 2									error debug.
18	Invoice	Product	Sales		Invoice	Discount				=AMERGE(A4:C15,E4:H11,{4,2},{2,4})
19	4588	Carlota	130		4588	0.065				check keys
20	4588	Quad	559		4589	0.0375
21	4588	Sunshine	114		4590	0.12				(key left columns out of range)
22	4589	Quad	559
23	4589	Sunshine	209							=ARRANGE(J3#,{3,4,2,5})							other functions used on minisheet
24	4590	Carlota	2869							Product	Code	Color	Price	Units			ARRANGE
25										Quad	QD-r	Red	43	48			APIVOT
26	=AMERGE(A18:C24,E18:F21,,)									Quad	QD-b	Blue	41	156
27	Invoice	Product	Sales	Discount	Total					Quad	QD-r	Red	43	168
28	4588	Carlota	130	6.50%	121.55					Carlota		Blue		132
29	4588	Quad	559	6.50%	522.665					Carlota		Blue		72
30	4588	Sunshine	114	6.50%	106.59					Sunshine	SH-r	Red	19	108
31	4589	Quad	559	3.75%	538.0375					Quad	QD-b	Blue	41	128
32	4589	Sunshine	209	3.75%	201.1625					Gigi		Red		96
33	4590	Carlota	2869	12.00%	2524.72					Sunshine	SH-r	Red	19	60
34										Sunshine	SH-b	Blue	18	24
35	=APIVOT(A28:E33,1,2,5,)									Carlota		Blue		120
36	(1\2) 5 vf=0	Carlota	Quad	Sunshine	Grand Total					Quad	QD-b	Blue	41	24
37	4588	121.55	522.665	106.59	750.805
38	4589	0	538.0375	201.1625	739.2
39	4590	2524.72	0	0	2524.72
40	Grand Total	2646.27	1060.7025	307.7525	4014.725
41
AMERGE post

Cell Formulas
Range	Formula
J2,P2,A35,A26,J23,J18	J2	=FORMULATEXT(J3)
J3:N15	J3	=AMERGE(A3:C15,E3:H11,{3,2},{2,4})
P3:T11	P3	=AMERGE(E3:H11,A3:C15,{2,4},{3,2})
J19	J19	=AMERGE(A4:C15,E4:H11,{4,2},{2,4})
J24:N36	J24	=ARRANGE(J3#,{3,4,2,5})
A27:D33	A27	=AMERGE(A18:C24,E18:F21,,)
E28:E33	E28	=C28:C33*(1-D28:D33)
A36:E40	A36	=APIVOT(A28:E33,1,2,5,)
Dynamic array formulas.

 

[Xlambda]

Here are the general notes addendum of the 6 Power Querry scenarios for merging/joining tables.
4 functions will cover all 6 scenarios, for any arrays not only tables, no PQ or refresh needed: AIJOIN, ALMERGE, AMERGE, AJOIN
1. Inner Join (AND logical test) , our function AIJOIN
2. Left Anti (Only in left array), our function ALMERGE
3. Left Outer (Classic Lookup in right array), our function AMERGE (most common)
4. Right Anti (Only in right array), we can use ALMERGE inverting the order of arrays in the formula
5. Right Outer (Classic Lookup in left array), we can use AMERGE inverting the order of arrays in the formula
6. Full Outer (OR logical text), our function AJOIN
The "links" for this types of joining/merging are called "keys", primary (unique) or foreign keys. They represents common column or multiple columns found in both arrays (they share the same type of data of the respective columns and should have same dimension, in most of the cases).
The functions can handle also dups of keys along with unique keys, and for that reason we can have different outcomes if we apply same functions from left to right or right to left. Plenty of examples on minisheets to reveal this behavior.
Attached picture, courtesy of Mike Girvin ExcelIsFun together with some of the sample data found on these posts.
View attachment 37624

 

[Xlambda]

2nd addendum: All the formulas except AJOIN, can handle multiple columns "keys". In most cases they should have the same dimension. Anyhow, the functions can handle dif dimension "keys", but only if we are positive that their join values are similar and can be matched. As an example, an array can have as key, 2 sep columns, first names and last names, and the other array has only a single column key for first names and last names joined with a delimiter in between. The default delimiter used in my formula is "|", did not used it as a separate argument, but can be changed inside formulas. It is variable "d" defined after LET. Or we can use our text manipulation formulas to change any delimiter with "|". It has powerful versatility but has to be handled with care.

LAMBDA 7.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	different keys dimensions scenario					array 1					array 2				Left Anti (Only in left array)					Right Anti (Only in right array)
2	list 1 sample		list 2 sample,dif delimiters			First Name	Last Name	Units			Name	Price			=ALMERGE(F3:H14,K3:L14,{1,2},)					=ALMERGE(K3:L14,F3:H14,,{1,2})
3	Sol Marroquin		Raven,Beatty			Sol	Marroquin	29			Raven|Beatty	123			First Name	Last Name	Units			Name	Price
4	Kiera Mcfall		Roxanna Mercier			Kiera	Mcfall	14			Roxanna|Mercier	213			Sol	Marroquin	29			Roxanna|Mercier	213
5	Raven Beatty		Fanny,Denning			Raven	Beatty	23			Fanny|Denning	323			Kiera	Mcfall	14			Fanny|Denning	323
6	Elinore Dees		Lesha Nobles			Elinore	Dees	15			Lesha|Nobles	211			Elinore	Dees	15			Lesha|Nobles	211
7	Wei Lockwood		Wei/Lockwood			Wei	Lockwood	19			Wei|Lockwood	124			Donald	Eldridge	24			Gertrudis|Fitzpatrick	245
8	Donald Eldridge		Gertrudis Fitzpatrick			Donald	Eldridge	24			Gertrudis|Fitzpatrick	245			Claudio	Beam	34			Bernita|Crutcher	100
9	Claudio Beam		Angelita Packer			Claudio	Beam	34			Angelita|Packer	267			Reyna	Luke	7			Shiela|Anaya	110
10	Angelita Packer		Beaulah Wenger			Angelita	Packer	23			Beaulah|Wenger	213			Vivan	Keeney	14			Yolonda|Armstead	423
11	Reyna Luke		Malvina Hamer			Reyna	Luke	7			Malvina|Hamer	321
12	Beaulah Wenger		Bernita\Crutcher			Beaulah	Wenger	8			Bernita|Crutcher	100			Inner Join (And logical test)					Inner Join (And logical test)
13	Malvina Hamer		Shiela Anaya			Malvina	Hamer	11			Shiela|Anaya	110			=AIJOIN(F3:H14,K3:L14,{1,2},)					=AIJOIN(K3:L14,F3:H14,,{1,2})
14	Vivan Keeney		Yolonda Armstead			Vivan	Keeney	14			Yolonda|Armstead	423			First Name	Last Name	Units	Price		Name	Price	Units
15															Raven	Beatty	23	123		Raven|Beatty	123	23
16	functions					Left Outter (Classic lookup in right array)					Right Outter (Classic lookup in left array)				Wei	Lockwood	19	124		Wei|Lockwood	124	19
17	used in minisheet		formula to change all delimiters			=AMERGE(F3:H14,K3:L14,{1,2},)					=AMERGE(K3:L14,F3:H14,1,{1,2})				Angelita	Packer	23	267		Angelita|Packer	267	23
18	AREMOVE		=AREMOVE(C3:C14,T_CHARS(,,", /\"),"|")			Name	Last Name	Units	Price		Name	Price	Units		Beaulah	Wenger	8	213		Beaulah|Wenger	213	8
19	T_CHARS		Raven|Beatty			Sol	Marroquin	29			Raven|Beatty	123	23		Malvina	Hamer	11	321		Malvina|Hamer	321	11
20	AMERGE		Roxanna|Mercier			Kiera	Mcfall	14			Roxanna|Mercier	213			Inner Join has the same outcome in different structures
21	ALMERGE		Fanny|Denning			Raven	Beatty	23	123		Fanny|Denning	323
22	AIJOIN		Lesha|Nobles			Elinore	Dees	15			Lesha|Nobles	211			Due to the way the functions are constructed, AJOIN can not be used
23	AJOIN		Wei|Lockwood			Wei	Lockwood	19	124		Wei|Lockwood	124	19		with different keys dimensions
24			Gertrudis|Fitzpatrick			Donald	Eldridge	24			Gertrudis|Fitzpatrick	245			=AJOIN(F3:H14,K3:L14,{1,2},1)					=AJOIN(K3:L14,F3:H14,,{1,2})
25			Angelita|Packer			Claudio	Beam	34			Angelita|Packer	267	23		First Name	Last Name	Units	Price		Name	Price	Units
26			Beaulah|Wenger			Angelita	Packer	23	267		Beaulah|Wenger	213	8		Sol	Marroquin	29			Raven|Beatty	123	23
27			Malvina|Hamer			Reyna	Luke	7			Malvina|Hamer	321	11		Kiera	Mcfall	14			Roxanna|Mercier	213
28			Bernita|Crutcher			Beaulah	Wenger	8	213		Bernita|Crutcher	100			Raven	Beatty	23	123		Fanny|Denning	323
29			Shiela|Anaya			Malvina	Hamer	11	321		Shiela|Anaya	110			Elinore	Dees	15			Lesha|Nobles	211
30			Yolonda|Armstead			Vivan	Keeney	14			Yolonda|Armstead	423			Wei	Lockwood	19	124		Wei|Lockwood	124	19
31															Donald	Eldridge	24			Gertrudis|Fitzpatrick	245
32															Claudio	Beam	34			Angelita|Packer	267	23
33															Angelita	Packer	23	267		Beaulah|Wenger	213	8
34															Reyna	Luke	7			Malvina|Hamer	321	11
35															Beaulah	Wenger	8	213		Bernita|Crutcher	100
36															Malvina	Hamer	11	321		Shiela|Anaya	110
37															Vivan	Keeney	14			Yolonda|Armstead	423
38															#VALUE!	#VALUE!	#VALUE!	213		Sol		29
39															#VALUE!	#VALUE!	#VALUE!	323		Kiera		14
40															#VALUE!	#VALUE!	#VALUE!	211		Elinore		15
41															#VALUE!	#VALUE!	#VALUE!	245		Donald		24
42															#VALUE!	#VALUE!	#VALUE!	100		Claudio		34
43															#VALUE!	#VALUE!	#VALUE!	110		Reyna		7
44															#VALUE!	#VALUE!	#VALUE!	423		Vivan		14
45
Key dif dim post

Cell Formulas
Range	Formula
O2,T24,O24,K17,F17,T13,O13,T2	O2	=FORMULATEXT(O4)
F3:G14	F3	=ASPLIT(A3:A14," ")
K3:K14	K3	=AREMOVE(C3:C14,T_CHARS(,,", /\"),"|")
O4:Q10	O4	=ALMERGE(F3:H14,K3:L14,{1,2},)
T4:U10	T4	=ALMERGE(K3:L14,F3:H14,,{1,2})
O15:R19	O15	=AIJOIN(F3:H14,K3:L14,{1,2},)
T15:V19	T15	=AIJOIN(K3:L14,F3:H14,,{1,2})
C18	C18	=FORMULATEXT(C19)
C19:C30	C19	=AREMOVE(C3:C14,T_CHARS(,,", /\"),"|")
F19:I30	F19	=AMERGE(F3:H14,K3:L14,{1,2},)
K19:M30	K19	=AMERGE(K3:L14,F3:H14,1,{1,2})
O26:R44	O26	=AJOIN(F3:H14,K3:L14,{1,2},1)
T26:V44	T26	=AJOIN(K3:L14,F3:H14,,{1,2})
Dynamic array formulas.