ALMERGE

[Xlambda]

ALMERGE arrays Left Merge, same functionality as in Power Querry join/merge scenario called Left Anti. Calls ATEXTJOIN
Other functions used on minisheet ARRANGE , AIJOIN

=LAMBDA(l,r,klf,krt,
    LET(d,"|",t,"not found",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),
       a,INDEX(l,sl,kl),b,INDEX(r,sr,kr),ta,ATEXTJOIN(a,,,d),tb,ATEXTJOIN(b,,,d),
       x,XMATCH(ta,tb),xn,ISNUMBER(x),ia,FILTER(sl,NOT(xn),t),al,INDEX(l,ia,ql),
       ml,OR(ISNA(XMATCH(kl,ql))),mr,OR(ISNA(XMATCH(kr,qr))),
       IFS(OR(ml,mr),"check keys",ia=t,t,TRUE,al)
    )
)

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
1	PQ name: Left Anti (Only in left array)														array 1		array 2		find names only in left array
2	Units	Color	Product		Code	Product	Price	Color		=ALMERGE(A3:C14,E3:H10,{3,2},{2,4})					Sol Marroquin		Raven Beatty		=ALMERGE(O2:O13,Q2:Q13,,)
3	48	Red	Quad		CR-r	Carlota	26	Red		132	Blue	Carlota			Kiera Mcfall		Roxanna Mercier		Sol Marroquin
4	156	Blue	Quad		KK-b	Kiki	13	Blue		72	Blue	Carlota			Raven Beatty		Fanny Denning		Kiera Mcfall
5	168	Red	Quad		QD-r	Quad	43	Red		96	Red	Gigi			Elinore Dees		Lesha Nobles		Elinore Dees
6	132	Blue	Carlota		SH-r	Sunshine	19	Red		120	Blue	Carlota			Wei Lockwood		Wei Lockwood		Donald Eldridge
7	72	Blue	Carlota		CR-g	Carlota	24	Green		left to right					Donald Eldridge		Gertrudis Fitzpatrick		Claudio Beam
8	108	Red	Sunshine		QD-b	Quad	41	Blue		=ALMERGE(A3:C14,E3:H10,3,2)					Claudio Beam		Angelita Packer		Reyna Luke
9	128	Blue	Quad		SH-b	Sunshine	18	Blue		96	Red	Gigi			Angelita Packer		Beaulah Wenger		Vivan Keeney
10	96	Red	Gigi		KK-r	Kiki	20	Red							Reyna Luke		Malvina Hamer		(if we invert right and left arrays)
11	60	Red	Sunshine							error debug.					Beaulah Wenger		Bernita Crutcher		PQ name: Right Anti (Only in right array)
12	24	Blue	Sunshine		=ALMERGE(E3:H10,A3:C14,{2,4},{3,2})					=ALMERGE(A3:C14,E3:H10,2,5)					Malvina Hamer		Shiela Anaya		find names only in right array
13	120	Blue	Carlota		CR-r	Carlota	26	Red		check keys					Vivan Keeney		Yolonda Armstead		=ALMERGE(Q2:Q13,O2:O13,,)
14	24	Blue	Quad		KK-b	Kiki	13	Blue											Roxanna Mercier
15					CR-g	Carlota	24	Green		(key right column out of range)									Fanny Denning
16					KK-r	Kiki	20	Red											Lesha Nobles
17					right to left														Gertrudis Fitzpatrick
18					PQ name: Right Anti (Only in right array)					=ALMERGE(A3:C14,E3:H10,2,4)									Bernita Crutcher
19										not found									Shiela Anaya
20																			Yolonda Armstead
21										(keys in range but
22										no data only in left array)									PQ name: Inner Join
23																			find common names in both arrays
24										=ARRANGE(J3#,{3,2})									=AIJOIN(O2:O13,Q2:Q13,,)
25										Carlota	Blue	132							Raven Beatty
26										Carlota	Blue	72							Wei Lockwood
27										Gigi	Red	96							Angelita Packer
28										Carlota	Blue	120							Beaulah Wenger
29																			Malvina Hamer
30										other functions used on minisheet
31										ARRANGE
32										AIJOIN
ALMERGE post

Cell Formulas
Range	Formula
J2,S24,J24,J18,S13,E12,J12,J8,S2	J2	=FORMULATEXT(J3)
J3:L6	J3	=ALMERGE(A3:C14,E3:H10,{3,2},{2,4})
S3:S9	S3	=ALMERGE(O2:O13,Q2:Q13,,)
J9:L9	J9	=ALMERGE(A3:C14,E3:H10,3,2)
E13:H16	E13	=ALMERGE(E3:H10,A3:C14,{2,4},{3,2})
J13	J13	=ALMERGE(A3:C14,E3:H10,2,5)
S14:S20	S14	=ALMERGE(Q2:Q13,O2:O13,,)
J19	J19	=ALMERGE(A3:C14,E3:H10,2,4)
J25:L28	J25	=ARRANGE(J3#,{3,2})
S25:S29	S25	=AIJOIN(O2:O13,Q2:Q13,,)
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.