MrExcel's Learn Excel #787 - Consolidate Minus

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jan 15, 2009.
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!
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top