Compare the sum of the values of two mapped elements with one having duplicates

[yinka]

I have a very somewhat complicated question (at least to me ) and I'm hoping someone will be kind enough to help. I hope my explanation is good enough

I have a mapping table like below, we have multiple elements b mapped to element a



| Element a |Element b|
|-------------|-------- |
| B10 | 212010 |
| B101 | 212010 |
| B102 | 212010 |
| B103 | 212010 |
| B104 | 212010 |
| B301 | 111030 |
| B302 | 111030 |
| B303 | 111030 |
| B304 | 111030 |
|---------------|-------- |



Each element in the above table will have a value in another table:




| Element A | Value |
|-----------|-------------|
| B10 | 1,000.00 |
| B101 | 2,000.00 |
| B102 | 6,000.00 |
| B103 | 1,000.00 |
| B104 | 10,000.00 |
| B301 | 1,000.00 |
| B302 | 2,000.00 |
| B303 | 6,000.00 |
| B304 | 1,000.00 |
|-----------|-------------|


| Element B | Value |
|-----------|-------------|
| 212010 | 20,000.00 |
| 111030 | 10,000.00 |
|-----------|-------------|




I have to compare the value of the corresponding elements above using the mapping table, I'm looking for a solution that looks like the table below -



snip.PNG

Steps:

1. Compare the value of each element side by side without duplicating element b
2. if possible put a box around each mapped element and value

I understand it may only be possible to the above using only VBA macros, but a similar solution using only excel formula will be very appreciated.

The solution should be compatible with Excel 2003.

Thank you very much

[Olly]

In cell C34, put:

Please Login or Register  to view this content.

In cell D34 put:

Please Login or Register  to view this content.

In cell E34 put:

Please Login or Register  to view this content.

Copy these formulae down to row 42.

Sorry, I can't upload attachments from this computer! Hope the above is clear.

[XOR LX]

Cross-posted:

http://www.mrexcel.com/forum/excel-q...uplicates.html

Regards