A question from a seminar... how can you use the consolidate command to subtract one data set from another data set? Episode 787 shows you the workaround.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
On yesterday's netcast, I introduced the data consolidate feature, data consolidate.
There's a great way to take 2 data sets and add them together, but the question that came in, question was that, you know, I have 2 data sets.
I need to subtract this data set from the first data set, and when we go into data consolidation, it's a little frustrating.
There is no operation that allows us to subtract.
We can SUM, but then it's the other 11 functions that we typically see in subtotals or pivot tables -- COUNT, AVERAGE, MAX, MIN, and so on.
There is no difference there, I think.
So, my solution here is a little bit convoluted but fairly simple once you get the hang of it.
We're going to go to a blank cell, any blank cell, and put in the number –1, and then I'm going to copy that cell, so CONTROL+C to copy, and basically, I've copied this –1 to the clipboard.
Now I'm going to go to all of the numbers in my second data set and I'm going to use EDIT, PASTE SPECIAL, and say that I want to MULTIPLY whatever is on the clipboard by everything in this selection.
To make sure that I don't change any formatting, I'm going to PASTE VALUES as well.
Click OK, and what that's going to do is it's going to take all of our numbers here and multiply them by –1.
Now it's a simple matter of using DATA, CONSOLIDATE just as before.
So, DATA, CONSOLIDATE.
I want to consolidate everything from A1 to D27, click ADD, and then also everything from F1 to H15, click ADD, use the labels in the TOP ROW and LEFT COLUMN, just like yesterday, but basically now we are subtracting.
We're subtracting the second data set from the first data set.
Of course, in real life, you’d want to make a copy of this data set so that way you didn't make all of that negative, but, basically, using this little trick of putting –1 in the clipboard, and then using PASTE SPECIAL, MULTIPLY to change everything in the second data set to negative allows you to do a subtraction using DATA, CONSOLIDATE.
Well, hey.
Thanks for stopping by.
We’ll see you next time for another netcast for MrExcel.
On yesterday's netcast, I introduced the data consolidate feature, data consolidate.
There's a great way to take 2 data sets and add them together, but the question that came in, question was that, you know, I have 2 data sets.
I need to subtract this data set from the first data set, and when we go into data consolidation, it's a little frustrating.
There is no operation that allows us to subtract.
We can SUM, but then it's the other 11 functions that we typically see in subtotals or pivot tables -- COUNT, AVERAGE, MAX, MIN, and so on.
There is no difference there, I think.
So, my solution here is a little bit convoluted but fairly simple once you get the hang of it.
We're going to go to a blank cell, any blank cell, and put in the number –1, and then I'm going to copy that cell, so CONTROL+C to copy, and basically, I've copied this –1 to the clipboard.
Now I'm going to go to all of the numbers in my second data set and I'm going to use EDIT, PASTE SPECIAL, and say that I want to MULTIPLY whatever is on the clipboard by everything in this selection.
To make sure that I don't change any formatting, I'm going to PASTE VALUES as well.
Click OK, and what that's going to do is it's going to take all of our numbers here and multiply them by –1.
Now it's a simple matter of using DATA, CONSOLIDATE just as before.
So, DATA, CONSOLIDATE.
I want to consolidate everything from A1 to D27, click ADD, and then also everything from F1 to H15, click ADD, use the labels in the TOP ROW and LEFT COLUMN, just like yesterday, but basically now we are subtracting.
We're subtracting the second data set from the first data set.
Of course, in real life, you’d want to make a copy of this data set so that way you didn't make all of that negative, but, basically, using this little trick of putting –1 in the clipboard, and then using PASTE SPECIAL, MULTIPLY to change everything in the second data set to negative allows you to do a subtraction using DATA, CONSOLIDATE.
Well, hey.
Thanks for stopping by.
We’ll see you next time for another netcast for MrExcel.
