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
Table 2
Table 3
Combined table
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 |