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 NameAirHotelJoe200100Mary250100 Set 2 NameAirHotelGroundJoe400250100Jane400100 Mary50025050 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) NameAirHotelGroundJoe200150100Jane400100 Mary25015050 Thanks for your help!

Compare two sets of data and subtract one from the other

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!

Common Use Cases

Accepted answers

andyuttley

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.