Select first occurrence

[sharshra]

I have to combine 2 tables based on the code in these tables. If the codes are same, it should add up the qty & display the name, code & qty in the combined table. This is done.

But, when the names are different having same code in these tables, the combined table should have the name of the first occurrence of the name. How can this be done? Any suggestions from the experts please?

Consider the following example.
'Name' of 'code' 63 is different in tables 1 & 2. The formula will add the 'qty' of 'code' 63 in tables 1 & 2. 'Name' is different for 'code' 63. The formula should pick the 'name' of first occurrence, which is 'bcd' in this case in the combined table.

Similarly, 'name' of 'code' 457 is different in tables 2 & 3. Formula should pick the first occurrence, which is 'pqr' in this case in the combined table.

Table 1

excel problems.xlsx
	B	C	D
68	name	code	qty
69	abc	12	9
70	mno	98	7
71	bcd	63	8
merge tables

Table 2

excel problems.xlsx
	F	G	H
68	name	code	qty
69	bc	63	4
70	pqr	457	2
71	mno	98	6
72	def	47	15
merge tables

Table 3

excel problems.xlsx
	J	K	L
68	name	code	qty
69	pq	457	3
70	mno	98	1
71	abc	12	2
merge tables

Combined table

excel problems.xlsx
	O	P	Q
68	name	code	qty
69	abc	12	11
70	bcd	63	12
71	def	47	15
72	mno	98	13
73	pqr	457	5
merge tables

 

[StephenCrump]

Try O69:

=LET(d,VSTACK(B69:D71,F69:H72,J69:L71),c,INDEX(d,,2),u,UNIQUE(c),SORT(HSTACK(XLOOKUP(u,c,INDEX(d,,1)),u,BYROW(u,LAMBDA(r,SUM((c=r)*(INDEX(d,,3)))))),1))

 

[Holger]

Noting, that I'm still learning myself, maybe this could also work:

test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	name	code	qty		name	code	qty		name	code	qty		name	code	qty
2	abc	12	9		bc	63	4		pq	457	3		12	abc	9
3	mno	98	7		pqr	457	2		mno	98	1		98	mno	7
4	bcd	63	8		mno	98	6		abc	12	2		63	bcd	8
5					def	47	15						457	pqr	2
6													47	def	15
Sheet1

Cell Formulas
Range	Formula
M2:O6	M2	=LET(a, VSTACK(A2:C4,E2:G5,I2:K4), v, UNIQUE(INDEX(VSTACK(A2:C4,E2:G5,I2:K4),,2)), HSTACK(v,XLOOKUP(v,INDEX(VSTACK(A2:C4,E2:G5,I2:K4),,2),INDEX(VSTACK(A2:C4,E2:G5,I2:K4),,1)),XLOOKUP(v,INDEX(VSTACK(A2:C4,E2:G5,I2:K4),,2),INDEX(VSTACK(A2:C4,E2:G5,I2:K4),,3))))
Dynamic array formulas.