Hey folks - first question on the forum. I have two sets of summary data with almost the same headings and rows for example;
Set 1
Name | Air | Hotel |
Joe | 200 | 100 |
Mary | 250 | 100 |
Set 2
Name | Air | Hotel | Ground |
Joe | 400 | 250 | 100 |
Jane | 400 | 100 | |
Mary | 500 | 250 | 50 |
I want to compare or join the data then subtract the data from Set 2 from the data in Set 1 to get a net result for Set 3:
Set 3 (Set 2 less Set 1)
Name | Air | Hotel | Ground |
Joe | 200 | 150 | 100 |
Jane | 400 | 100 | |
Mary | 250 | 150 | 50 |
Thanks for your help!
Solved! Go to Solution.
Hi @steeldad
There are two methods to do this, both attached here (and picture below). Both involve joining the data sets. The first is quicker/simpler, but isn't dynamic to how data might change in future (e.g. new columns being added); my preference would be method two (the second container) which transposes the data first, so we can join on the name (e.g. Joe) and field name (e.g. Air) and then just compare numbers side by side dynamically.
Hope this helps!
Andy
Thanks Andy!
Hi Andy!
Would you please explain how the 'Name2' suddenly appears in Method 2, between the transpose tool and the select tool?
Edit: Never mind, I figured it out! :)
Many thanks!