How do I compare two Ranges of Data? 'Comparison Operators' are the key. Today, in Episode #1597, Bill looks at the need to compare two Ranges of Data to be sure they are Matched - and then he shows us how to do it. Follow along to learn the basics of Comparing Data from two different sources to reconcile for the final True result.
Many 'Beginner Oriented' Excel How-To books say they can bring you from zero to familiar with the important features of Microsoft Excel -- 'Don't Fear The Spreadsheet' actually will. Why are we so confident? Because the questions in this book were asked by an absolute Excel Beginner -- Tyler Nash -- with the answers provided by three (3) Microsoft MVPs specializing in and dedicated to teaching others Microsoft Excel, from the ground up -- Kevin Jones, Tom Urtis and Bill Jelen. Don't Fear the Spreadsheet
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Many 'Beginner Oriented' Excel How-To books say they can bring you from zero to familiar with the important features of Microsoft Excel -- 'Don't Fear The Spreadsheet' actually will. Why are we so confident? Because the questions in this book were asked by an absolute Excel Beginner -- Tyler Nash -- with the answers provided by three (3) Microsoft MVPs specializing in and dedicated to teaching others Microsoft Excel, from the ground up -- Kevin Jones, Tom Urtis and Bill Jelen. Don't Fear the Spreadsheet
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Don’t Fear The Spreadsheet, Podcast Episode Number 10: Do These Cells Match?
Bill: Hey. Alright. Welcome back to Don’t Fear The Spreadsheet podcast.
I’m Bill Jelen from MrExcel. Today’s question, Tyler Nash.
Tyler: Is there any way that I can compare 2 different ranges of data to see whether or not they match?
Bill: Alright.
Tyler.
Can we compare 2 different ranges of data?
Now, this happens a lot, you know.
We have a spreadsheet, we send a spreadsheet off to a co-worker, and then, later on, we give them the spreadsheet again and they need to see if we changed anything, and they could just ask us if we changed anything, but, you know, she's just not friendly, whatever.
Okay.
So, we need to see if SUE FLAY matches SUE FLAY.
I have to give credit to Tom Urtis, one of the MVPs who wrote the book.
This is his list of names.
This file is straight from the list of sample files that come with a book.
So, you know, for every topic, we give the file that we [ use to create – 01:12 ].
So I just pulled up Tom’s.
I think he’s a funny guy.
Anyway, so, in the last episode, episode 9, we talked about formula operators like +, -, the * for multiply, and the / for divide.
There's some other operators called comparison operators, and to test if something is the same, the operator is = but there's also a >, >=, <, <=, and <>. So, this is pretty cool to be able to test to see if 2 cells are =, and what we do is, of course every formulas has to start with what?
It has to start with an = sign, and we click on the first cell, cell A4, and then we say is that = to D4, alright?
So, I press…I'm going to press…I'm going to hold down CONTROL when I press ENTER just to stay in the same cell, and if they're =, then we get the answer of TRUE.
If they're not =, then we get the answer of FALSE, alright?
Let me undo, aright?
[ =A4=D4 ] So, we're going to take that formula.
I'm going to copy that formula down and I'm going to copy that whole range across, and what we'll see is every time that the items match, we have a TRUE, and every time that the items do not match, alright?
So, MAE O’NAYZ is now MAY B. SOH.
Maybe she got married, I don't know.
We have a FALSE, alright?
So, this helps us to see which items changed or didn’t change.
Now, another way to go.
I'm going to go into the next worksheet here, is to use these comparison operators right inside of something called conditional formatting, alright?
Really what we want to do is we want to see the things that change.
So, I'm going to use the not the same, the <, > symbol, but watch how we do this.
Okay.
I'm going to select the second range of data and I'm going to notice that the active cell is in D4, D4, right now.
With conditional formatting, it's on the HOME tab.
Just almost outside your view here, I'm going to do conditional formatting and then, down at the bottom, it says NEW RULE.
NEW RULE.
You can also get here with ALT+O D. ALT+O D.
We want to USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT, and, remember, we're writing this formulas as it refers to D4 so we say = -- all formulas start with = -- D4 <> to A4, alright?
So, go check and see if D4 is <> to A4.
If they're not equal, then I want to flag it somehow, and so I'll use a…I could change the font color.
I'm going to change the fill color because that is much more easy to see, click OK down here, and then click OK again, and then anything that has changed is now highlighted in red, alright?
So, you know, then, as I come back and I update my data, I could have just copied and pasted there, you'll see that the red goes away.
So, using conditional formatting is a good way to go.
[ =D4<>A4 ] Let’s talk about one more use for these comparison operators.
Sometimes, we want to have a formula to identify everyone who falls into a certain group.
Like, for example, maybe we want to find everyone who’s over 40, 40 or over, 40 or over.
That means we're going to have to use >=. So, a formula to figure out if this person is 40 or over is =, everything starts with =, the cell that we're trying to compare and then >=40, and we'll copy that formula down using the fill handle, that little square dot, and anytime that the person is over 40, we're getting a TRUE.
If they're under 40, we're getting a FALSE.
[ =B4>=40 ] Comparison operator is a cool feature to make your Excel formulas a little bit more powerful.
Tyler: Thanks for stopping by.
We'll see you next episode.
Check out Don't Fear The Spreadsheet.
This book [ unintelligible – 05:05 ] look like it was written for rocket scientists.
Don’t Fear The Spreadsheet, Podcast Episode Number 10: Do These Cells Match?
Bill: Hey. Alright. Welcome back to Don’t Fear The Spreadsheet podcast.
I’m Bill Jelen from MrExcel. Today’s question, Tyler Nash.
Tyler: Is there any way that I can compare 2 different ranges of data to see whether or not they match?
Bill: Alright.
Tyler.
Can we compare 2 different ranges of data?
Now, this happens a lot, you know.
We have a spreadsheet, we send a spreadsheet off to a co-worker, and then, later on, we give them the spreadsheet again and they need to see if we changed anything, and they could just ask us if we changed anything, but, you know, she's just not friendly, whatever.
Okay.
So, we need to see if SUE FLAY matches SUE FLAY.
I have to give credit to Tom Urtis, one of the MVPs who wrote the book.
This is his list of names.
This file is straight from the list of sample files that come with a book.
So, you know, for every topic, we give the file that we [ use to create – 01:12 ].
So I just pulled up Tom’s.
I think he’s a funny guy.
Anyway, so, in the last episode, episode 9, we talked about formula operators like +, -, the * for multiply, and the / for divide.
There's some other operators called comparison operators, and to test if something is the same, the operator is = but there's also a >, >=, <, <=, and <>. So, this is pretty cool to be able to test to see if 2 cells are =, and what we do is, of course every formulas has to start with what?
It has to start with an = sign, and we click on the first cell, cell A4, and then we say is that = to D4, alright?
So, I press…I'm going to press…I'm going to hold down CONTROL when I press ENTER just to stay in the same cell, and if they're =, then we get the answer of TRUE.
If they're not =, then we get the answer of FALSE, alright?
Let me undo, aright?
[ =A4=D4 ] So, we're going to take that formula.
I'm going to copy that formula down and I'm going to copy that whole range across, and what we'll see is every time that the items match, we have a TRUE, and every time that the items do not match, alright?
So, MAE O’NAYZ is now MAY B. SOH.
Maybe she got married, I don't know.
We have a FALSE, alright?
So, this helps us to see which items changed or didn’t change.
Now, another way to go.
I'm going to go into the next worksheet here, is to use these comparison operators right inside of something called conditional formatting, alright?
Really what we want to do is we want to see the things that change.
So, I'm going to use the not the same, the <, > symbol, but watch how we do this.
Okay.
I'm going to select the second range of data and I'm going to notice that the active cell is in D4, D4, right now.
With conditional formatting, it's on the HOME tab.
Just almost outside your view here, I'm going to do conditional formatting and then, down at the bottom, it says NEW RULE.
NEW RULE.
You can also get here with ALT+O D. ALT+O D.
We want to USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT, and, remember, we're writing this formulas as it refers to D4 so we say = -- all formulas start with = -- D4 <> to A4, alright?
So, go check and see if D4 is <> to A4.
If they're not equal, then I want to flag it somehow, and so I'll use a…I could change the font color.
I'm going to change the fill color because that is much more easy to see, click OK down here, and then click OK again, and then anything that has changed is now highlighted in red, alright?
So, you know, then, as I come back and I update my data, I could have just copied and pasted there, you'll see that the red goes away.
So, using conditional formatting is a good way to go.
[ =D4<>A4 ] Let’s talk about one more use for these comparison operators.
Sometimes, we want to have a formula to identify everyone who falls into a certain group.
Like, for example, maybe we want to find everyone who’s over 40, 40 or over, 40 or over.
That means we're going to have to use >=. So, a formula to figure out if this person is 40 or over is =, everything starts with =, the cell that we're trying to compare and then >=40, and we'll copy that formula down using the fill handle, that little square dot, and anytime that the person is over 40, we're getting a TRUE.
If they're under 40, we're getting a FALSE.
[ =B4>=40 ] Comparison operator is a cool feature to make your Excel formulas a little bit more powerful.
Tyler: Thanks for stopping by.
We'll see you next episode.
Check out Don't Fear The Spreadsheet.
This book [ unintelligible – 05:05 ] look like it was written for rocket scientists.
