Select first occurrence

sharshra

Active Member
Joined
Mar 20, 2013
Messages
417
Office Version
  1. 365
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
BCD
68namecodeqty
69abc129
70mno987
71bcd638
merge tables


Table 2
excel problems.xlsx
FGH
68namecodeqty
69bc634
70pqr4572
71mno986
72def4715
merge tables


Table 3
excel problems.xlsx
JKL
68namecodeqty
69pq4573
70mno981
71abc122
merge tables


Combined table
excel problems.xlsx
OPQ
68namecodeqty
69abc1211
70bcd6312
71def4715
72mno9813
73pqr4575
merge tables
 
Try O69:

Excel Formula:
=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))
 
Upvote 0
Noting, that I'm still learning myself, maybe this could also work:

test.xlsx
ABCDEFGHIJKLMNO
1namecodeqtynamecodeqtynamecodeqtynamecodeqty
2abc129bc634pq457312abc9
3mno987pqr4572mno98198mno7
4bcd638mno986abc12263bcd8
5def4715457pqr2
647def15
Sheet1
Cell Formulas
RangeFormula
M2:O6M2=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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top