Compare two sets of data and subtract one from the other

[steeldad]

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!

[andyuttley · Accepted 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

[steeldad · Accepted solution]

Thanks Andy!

[Savannah]

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!