Subtract all contents of older file from newer file

[CDunhill]

Hi

I have a  weekly file which contains cumulative numeric data. I'd like a view of the values for each week (non cumulative).

Is there an easy way to take the latest file and subtract from it all values from the previous file for all numeric columns?

Feels like it would be pretty easy except my files have dozens of columns so it would be arduous to have to join and set up formulas for each pair of columns.

If it's useful, the final column contains the date to help identify old from new.

Thank you in advance 🙂

[Emil_Kos · Accepted solution]

Hi @CDunhill,

I changed the data type of the data and multiplied older data by -1. Summarize tool was just a finishing touch 😀

If this helped you please mark my post as a solution!

[apathetichell]

A few questions - are you looking to keep both the original weekly information and the delta in the same file - or will the new version only be the delta? Also - how do you plan to treat records where you had no instances the previous week (ie one file has 4 records, one file has 3 records).

This version shows you only the delta  and has week 2 come after week 1- I do no know if that is a fair assumption.

[CDunhill]

@Emil_Kos It blows me away how quickly people can get their head around problems then come up with such an elegant solution.

Thank you! In hindsight - this is the natural way to do it. Very much appreciated!

[CDunhill]

@apathetichell thank you also for this. I do need to keep the latest data which the other solution manages. I like this approach also though and will try to apply it elsewhere.

[Emil_Kos]

Hi @CDunhill,

This wasn't the first solution that I was thinking about but after a minute or two I made this one and I really enjoy it too 😀

I am happy that I could help 😀

[apathetichell]

Thanks!