Your VLOOKUP isn't working or the Sync Slicers isn't working. You look at the data and it *looks* the same, but it is not really the same. This video shows how to use MID and CODE to find out why the cells are not really the same.
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2384. Why don't these match? They look the same.
Hey, welcome back to MrExcel Netcast. I am Bill Jelen.
This is a question that comes up all the time all the time.
In this case it was for this episode 2104 about syncing slicers.
They're saying, hey, it's not working. And the problem, when they send me the file.
I ask them to send me the file.
The problem is they have two lists of data that look the same, but the items aren't really the same.
Sometimes Excel fools you.
You see things that appear to be the same. And they're not, right?
So, in that episode we had a data set over the left hand side.
You need to create a list tiny little table a one-column table of all of the unique items there, right? And there's a second list.
And both lists have to have the product's there in column L.
So when this starts not working, the first thing I want to know is do they really match?
So, I'm going to use a extra column here called Match.
=MATCH. Go look for Fig over in this list, right here.
I press F4 that way I locked that down. Comma Zero for an exact match.
And a six. That's good.
That means that it's there. It tells me it's in the 6th row.
I don't care what row it's in, right? I just want to know am I getting a number or not?
And we're going to see something here that are #N/A.
So for some reason Plum is here and plum is here and it says they don't match right?
And when that happens here are the steps that I go through.
First thing I want to do is I want to grab the value from the left hand side.
And I want to grab the value from the right hand side using a similar formula there.
And see if in fact they are matching. So, equal F4 equal G4.
It says they're not. Alright, are there trailing or leading spaces?
That happens a lot, right?
So the next thing I checked is what's the length of this item? Four. What's the length of this item? 4?
Alright, so we know it's the same number of characters.
It is not an issue with trailing spaces.
Then I break it down character by character so we can compare each character.
So, equal MID of this. Now I'm not going to press that F4 just once.
Because that will lock it the column F. But I press it a second time.
F dollar sign four.
That way I'm always pointing at row 4, but it's allowed to move to the right when I copy it over to column G.
And then which character do I want? I want the character 1.
But if I type a one there then I'd have to edit the next formula to be 2, 3, 4, 5, 6.
So, a sneaky little thing that we do here is ROW of A1.
Some people put ROWS, some people put ROW(1:1). Whatever you want to do.
For a length of 1. And that should give us a capital P.
Copy that down long enough to handle the longest value in the list, right?
And we have Plum, and….
Yeah, I can already see it, but here let's just do a little test here. Equal this is equal to that.
And it is not. So this plum is P-L-U-M.
This plum is not P-L-U-M. It is P-1-U-M.
If you can't see it for some reason then ask for the CODE.
That tells us the ASCII character code of those items.
And what we're seeing is that we have a 80 and 80. That's a capital P. But a 108 on the left (a lower case L).
And a 49 on the right, which is 1. Why would there be a one there?
You have to be a certain age. I am going back to Mrs.
Marhefka's typing class in 10th grade where we actually learn how to type on typewriters.
And back then there was no “1” key. I'm not making this up.
There was no “1” key on the keyboard.
When you need to type 1, you just went to lower case “L” So, people of a certain age, they still touch type by pressing the L instead of a 1. Luckily I was never a good typer.
I didn't learn that “L” trick and it's not an issue for me.
But I see that happen. So we're just going to come through here.
Control H and replace every occurrence of 1 with a lowercase L. Replace All.
Excel made six replacements. Beautiful.
That fixed a lot of them. But hey, now here's one.
It says that Nectarine is not over in the other list.
What's up with that? So here we grab this one.
Equal nectarine. Then equal nectarine.
Here we are. So length of 10 and length of nine.
So this one doesn't have a trailing space. This one for some reason does.
Now, if I had a bunch of these, I would use the TRIM function.
Insert a new column =TRIM(), Enter, Copy, Paste as Values. But in this case, F2 and backspace.
And then here, Musk melon is not a match. So we'll take a look at those.
So grab Muskmelon from the left hand side, bring it over into our little test area here.
Alright, they're both 10 characters, so that's not the issue.
M-U-S-K. Those are all the same. Copying my formula down a little bit more here.
Oh, look at that. This one started having back about 1997.
Spaces: There are two spaces that you see a lot in Excel.
When you just type a space bar you get a character 32 here.
But if the data came from the Internet, it's likely a character 160.
That's called a non breaking space.
So for whatever reason over here this data Musk melon that came from the Internet.
And we need to get rid of that weird space and type a regular space and things start to work alright.
So three different things we found there. Someone typed at 1 instead of L.
Someone put trailing spaces at the end. And then character 160 space.
Now that's if you have text. What about when you are trying to match numbers?
I've seen all sorts of things where you would swear the numbers are the same, but it turns out that there not.
So here we're going to test if the number on the left is the same as the number on the right. In all of these cases, they are not.
Alright, so let's take a look at the top one up here. We have 68 and 68.
How can not those not be a match?
When I look over in column C It's 68 but here it is 68.25. And they used decrease decimal.
A fast way to see all these at once is go to Formulas and choose Show Formulas.
A great shortcut key is Ctrl+`. It's on the US keyboard just above the Tab.
It has a tilde and a backwards accent.
Press that an you will see the decimals are in there but not being revealed. Alright, next group down here.
This is funny. People get so focused on the digits.
They will look at this 68 and the .68 and they will swear they are the same thing.
Obviously they are not. Here's one. It was actually more digits than this.
A guy showed up in one of my seminars. He had these two datasets.
He's says, “hey, my VLOOKUP is not working”.
And when I looked he was really studying the number of decimal places. 26 point 859. 26 point 859.
But this one was negative and for the life of him, he could not see the problem.
Another one with dates.
Dates are tricky things and here if we go in to Show Formulas mode. These all look the same.
Show formulas you see that this is 48382. That's the serial number.
Over here 44323.
And what had happened is this is July 5th, 2021, a U.S. date.
This, however is May 7th but it's formatted as European date.
Instead of Month, Day, Year, it is Day, Month, Year.
They look the same, but they're not the same in Excel.
So my purpose in making this video is to help the person where the sync slicers is not working.
I really want you to go through this process here and then repeat that Really Match column over on the other data set going back into your slicer table.
Try and figure out which ones aren't matching and then break it down why they aren't matching.
I don't think I've ever done a video of this before, I always just have people send me a file and then I get hidden away in a dark cave somewhere and use this method.
So now I can at least point people back to this is the method I used to try and figure out which character is the bad one?
And then using the CODE to figure out exactly what the problem is.
Well, tips like this are in my book MrExcel 2020.
Click that I in the top right hand corner for more information.
Please, if you like these tips then click Like, Subscribe and ring the Bell.
Feel free to post any questions or comments down in the comments below. Well, I want to thank you for stopping by.
We’ll see next time for another netcast from MrExcel.
Hey, welcome back to MrExcel Netcast. I am Bill Jelen.
This is a question that comes up all the time all the time.
In this case it was for this episode 2104 about syncing slicers.
They're saying, hey, it's not working. And the problem, when they send me the file.
I ask them to send me the file.
The problem is they have two lists of data that look the same, but the items aren't really the same.
Sometimes Excel fools you.
You see things that appear to be the same. And they're not, right?
So, in that episode we had a data set over the left hand side.
You need to create a list tiny little table a one-column table of all of the unique items there, right? And there's a second list.
And both lists have to have the product's there in column L.
So when this starts not working, the first thing I want to know is do they really match?
So, I'm going to use a extra column here called Match.
=MATCH. Go look for Fig over in this list, right here.
I press F4 that way I locked that down. Comma Zero for an exact match.
And a six. That's good.
That means that it's there. It tells me it's in the 6th row.
I don't care what row it's in, right? I just want to know am I getting a number or not?
And we're going to see something here that are #N/A.
So for some reason Plum is here and plum is here and it says they don't match right?
And when that happens here are the steps that I go through.
First thing I want to do is I want to grab the value from the left hand side.
And I want to grab the value from the right hand side using a similar formula there.
And see if in fact they are matching. So, equal F4 equal G4.
It says they're not. Alright, are there trailing or leading spaces?
That happens a lot, right?
So the next thing I checked is what's the length of this item? Four. What's the length of this item? 4?
Alright, so we know it's the same number of characters.
It is not an issue with trailing spaces.
Then I break it down character by character so we can compare each character.
So, equal MID of this. Now I'm not going to press that F4 just once.
Because that will lock it the column F. But I press it a second time.
F dollar sign four.
That way I'm always pointing at row 4, but it's allowed to move to the right when I copy it over to column G.
And then which character do I want? I want the character 1.
But if I type a one there then I'd have to edit the next formula to be 2, 3, 4, 5, 6.
So, a sneaky little thing that we do here is ROW of A1.
Some people put ROWS, some people put ROW(1:1). Whatever you want to do.
For a length of 1. And that should give us a capital P.
Copy that down long enough to handle the longest value in the list, right?
And we have Plum, and….
Yeah, I can already see it, but here let's just do a little test here. Equal this is equal to that.
And it is not. So this plum is P-L-U-M.
This plum is not P-L-U-M. It is P-1-U-M.
If you can't see it for some reason then ask for the CODE.
That tells us the ASCII character code of those items.
And what we're seeing is that we have a 80 and 80. That's a capital P. But a 108 on the left (a lower case L).
And a 49 on the right, which is 1. Why would there be a one there?
You have to be a certain age. I am going back to Mrs.
Marhefka's typing class in 10th grade where we actually learn how to type on typewriters.
And back then there was no “1” key. I'm not making this up.
There was no “1” key on the keyboard.
When you need to type 1, you just went to lower case “L” So, people of a certain age, they still touch type by pressing the L instead of a 1. Luckily I was never a good typer.
I didn't learn that “L” trick and it's not an issue for me.
But I see that happen. So we're just going to come through here.
Control H and replace every occurrence of 1 with a lowercase L. Replace All.
Excel made six replacements. Beautiful.
That fixed a lot of them. But hey, now here's one.
It says that Nectarine is not over in the other list.
What's up with that? So here we grab this one.
Equal nectarine. Then equal nectarine.
Here we are. So length of 10 and length of nine.
So this one doesn't have a trailing space. This one for some reason does.
Now, if I had a bunch of these, I would use the TRIM function.
Insert a new column =TRIM(), Enter, Copy, Paste as Values. But in this case, F2 and backspace.
And then here, Musk melon is not a match. So we'll take a look at those.
So grab Muskmelon from the left hand side, bring it over into our little test area here.
Alright, they're both 10 characters, so that's not the issue.
M-U-S-K. Those are all the same. Copying my formula down a little bit more here.
Oh, look at that. This one started having back about 1997.
Spaces: There are two spaces that you see a lot in Excel.
When you just type a space bar you get a character 32 here.
But if the data came from the Internet, it's likely a character 160.
That's called a non breaking space.
So for whatever reason over here this data Musk melon that came from the Internet.
And we need to get rid of that weird space and type a regular space and things start to work alright.
So three different things we found there. Someone typed at 1 instead of L.
Someone put trailing spaces at the end. And then character 160 space.
Now that's if you have text. What about when you are trying to match numbers?
I've seen all sorts of things where you would swear the numbers are the same, but it turns out that there not.
So here we're going to test if the number on the left is the same as the number on the right. In all of these cases, they are not.
Alright, so let's take a look at the top one up here. We have 68 and 68.
How can not those not be a match?
When I look over in column C It's 68 but here it is 68.25. And they used decrease decimal.
A fast way to see all these at once is go to Formulas and choose Show Formulas.
A great shortcut key is Ctrl+`. It's on the US keyboard just above the Tab.
It has a tilde and a backwards accent.
Press that an you will see the decimals are in there but not being revealed. Alright, next group down here.
This is funny. People get so focused on the digits.
They will look at this 68 and the .68 and they will swear they are the same thing.
Obviously they are not. Here's one. It was actually more digits than this.
A guy showed up in one of my seminars. He had these two datasets.
He's says, “hey, my VLOOKUP is not working”.
And when I looked he was really studying the number of decimal places. 26 point 859. 26 point 859.
But this one was negative and for the life of him, he could not see the problem.
Another one with dates.
Dates are tricky things and here if we go in to Show Formulas mode. These all look the same.
Show formulas you see that this is 48382. That's the serial number.
Over here 44323.
And what had happened is this is July 5th, 2021, a U.S. date.
This, however is May 7th but it's formatted as European date.
Instead of Month, Day, Year, it is Day, Month, Year.
They look the same, but they're not the same in Excel.
So my purpose in making this video is to help the person where the sync slicers is not working.
I really want you to go through this process here and then repeat that Really Match column over on the other data set going back into your slicer table.
Try and figure out which ones aren't matching and then break it down why they aren't matching.
I don't think I've ever done a video of this before, I always just have people send me a file and then I get hidden away in a dark cave somewhere and use this method.
So now I can at least point people back to this is the method I used to try and figure out which character is the bad one?
And then using the CODE to figure out exactly what the problem is.
Well, tips like this are in my book MrExcel 2020.
Click that I in the top right hand corner for more information.
Please, if you like these tips then click Like, Subscribe and ring the Bell.
Feel free to post any questions or comments down in the comments below. Well, I want to thank you for stopping by.
We’ll see next time for another netcast from MrExcel.
