0
I have 2 tables in Excel:
Table1 with ID, PointsA ... &
Table2 with ID, PointsB ...
ID from Table1 and Table2 have duplicates and some in Table1 are in Table2 and vice versa but not all of them. I want a sort of table that combines these two tables and show a list of unique IDs with SUM of PointsA and SUM of PointsB.
e.g. I have:
| ID | PointsA |
| A | 3 |
| C | 1 |
| A | 1 |
and
| ID | PointsB |
| C | 2 |
| B | 2 |
| C | 1 |
| A | 0 |
I want:
| ID | PointsA | PointsB |
| A | 4 | 0 |
| B | 0 | 2 |
| C | 1 | 3 |
How do I go about doing this? Each table has about 25000 items and 30 columns. (Ideally I want a pure excel solution and not use Macro.)
I've tried creating a pivot table that combines the two tables, but I can't seem to find a way to treat ID in Table1 and ID in Table2 as the same thing. I tried creating a relationship but this wouldn't work because of duplicate values.