While I've talked about Go To Special many times, I usually use Blanks, Visible Cells Only, or Formulas. Someone asked how to use the Row Differences option. This is a good way to compare if anything changed from one column to another, but it is limited. Episode 725 explores this option.
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.
Well, I'm a big fan of Go To, Special.
There's lots of cool things we can do with Go To, Special.
I always talk about using blanks or visible cells only and while I was showing this in the seminar someone said hey, what does Row differences do?
And I have to admit I never used row differences. It's actually pretty cool.
We have a series of values here in A the original values and some values that we got back from someone else, they might have changed some of those values and I need to see which ones changed.
So, I select the entire range and you'll notice that column A is the active cell within this range.
If I would press enter, it would switch to another column in this case column B, but I want column a to be the active cell and we go to Edit, Go To, Special, and choose Row differences and what happens is anytime that column B more column C, D, E or F.
If that was included my selection, is different then the active cell column in this case A it selects just those cells, so there's three cells here.
Let me color them for you by you hitting the paint bucket those are the cells that changed, Pretty cool, but rather limited to be honest because in real life you would have several columns Invoice, Date and Amount and another set with Invoice, Date and Amount, you have to see if anything change, between the two of those.
Well, if you actually wanted to do that, we'd have to build a concatenation formula to get A, B and C into a single column.
Well, let's take a look at this formula here basically, I'm taking A4, B4 and C4 and joining it all together with in quotes and semicolon.
So, that way we see the three values with semicolons.
I'll take this formula and copy it over next to my second data set and I need these to now be adjacent to each other.
So, I'll use cut and paste to paste them into adjacent columns.
We need to convert those formulas to values of course several ways to do that we can just use copy and then open the paste icon and choose paste values.
Now, I have the two column selected you'll still see that the active cell is in the first column.
So, we'll go to, Go To and then Special choose Row differences and color those you'll see there are two rows where something from one data set to the other change we just need to go find these.
So, it's interesting, it looks really great when we just have two columns we need to compare, when you get two more columns it kind of becomes convoluted and really why wouldn't we just build one formula check to see if the concatenation of the left, was equal to the concatenation of the right and be done with it.
So it's an interesting option in limited uses. It'll be pretty cool.
Now, when you get to several columns which is really the real life case.
It might be easier just to build a concatenation formula and check to see if those are equal or not.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Well, I'm a big fan of Go To, Special.
There's lots of cool things we can do with Go To, Special.
I always talk about using blanks or visible cells only and while I was showing this in the seminar someone said hey, what does Row differences do?
And I have to admit I never used row differences. It's actually pretty cool.
We have a series of values here in A the original values and some values that we got back from someone else, they might have changed some of those values and I need to see which ones changed.
So, I select the entire range and you'll notice that column A is the active cell within this range.
If I would press enter, it would switch to another column in this case column B, but I want column a to be the active cell and we go to Edit, Go To, Special, and choose Row differences and what happens is anytime that column B more column C, D, E or F.
If that was included my selection, is different then the active cell column in this case A it selects just those cells, so there's three cells here.
Let me color them for you by you hitting the paint bucket those are the cells that changed, Pretty cool, but rather limited to be honest because in real life you would have several columns Invoice, Date and Amount and another set with Invoice, Date and Amount, you have to see if anything change, between the two of those.
Well, if you actually wanted to do that, we'd have to build a concatenation formula to get A, B and C into a single column.
Well, let's take a look at this formula here basically, I'm taking A4, B4 and C4 and joining it all together with in quotes and semicolon.
So, that way we see the three values with semicolons.
I'll take this formula and copy it over next to my second data set and I need these to now be adjacent to each other.
So, I'll use cut and paste to paste them into adjacent columns.
We need to convert those formulas to values of course several ways to do that we can just use copy and then open the paste icon and choose paste values.
Now, I have the two column selected you'll still see that the active cell is in the first column.
So, we'll go to, Go To and then Special choose Row differences and color those you'll see there are two rows where something from one data set to the other change we just need to go find these.
So, it's interesting, it looks really great when we just have two columns we need to compare, when you get two more columns it kind of becomes convoluted and really why wouldn't we just build one formula check to see if the concatenation of the left, was equal to the concatenation of the right and be done with it.
So it's an interesting option in limited uses. It'll be pretty cool.
Now, when you get to several columns which is really the real life case.
It might be easier just to build a concatenation formula and check to see if those are equal or not.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
