Using Exact or If function to compare data, not working as expected

**allcentury** · 05-04-2012, 03:22 PM

I have 2 sources of data which I've attached. Tab 1 is coming from someone else, Tab 2 is from me. I know for a fact, there are cells in Tab 2 that match exactly to Tab 1 (simply by using Ctrl F). When I use Exact, all the values return as "False", same when I use an If statement to compare. Vlookups aren't working, the only thing that works is when I copy over info from Tab 2 into Tab 1 and use conditional formatting to find duplicates, which is fine on a small scale but the amount of data I'll need to validate later will be far too much.

I've tried changing the format of the columns to match but it hasn't helped, I've tried removing the leading apostrophe (though other threads on here have said formula's don't take that into account), I'm unsure what I'm doing wrong and would like to consult the jedi council at excelforum

Thanks in advance!

Anthony

**Cutter** · 05-04-2012, 03:40 PM

When you do the Ctrl F are you using the "Match entire cell contents" option? If not you aren't finding an exact match. It may be that one of your data sets has trailing spaces while the other does not. So they LOOK the same but they aren't the same.

BTW - not many of us (including me) will look at a file that isn't uploaded directly to the forum.

**allcentury** · 05-04-2012, 03:47 PM

Hi Cutter - Thanks for the reply, I tried running "Clean" on both sets of data but it did not help. I also tried your advice on "Match entire cell contents", ctrl + f still finds them on each tab. I can try and shorten the data to make it fit the 1mb requirements but ideally I didn't want to "edit" anything if I didn't need to. If the consensus around here is to not touch outside links, I understand and I'll do what I can.

Anthony

**Cutter** · 05-04-2012, 03:56 PM

Pick out one of the pairs of values that you think should match but are not matching. Click on each cell containing the values and, in the formula bar, click well to the right of the last visible character. The cursor should be immediately right of the last character. If it isn't, there is an extra character.

**ChemistB** · 05-04-2012, 04:13 PM

You can also zip the file before uploading it.

**allcentury** · 05-04-2012, 05:41 PM

Originally Posted by ChemistB

You can also zip the file before uploading it.

Thanks ChemistB! I tried to originally do a .rar but it didn't like that, now I have the .zip in the original post. Cheers.

@Cutter - Wouldn't clean solve that? Either way, I'm not finding any extra data in the cells, now that the file is attached, do you mind taking a peek?

**ChemistB** · 05-07-2012, 10:49 AM

Hi AllCentury
In your spreadsheet, you're comparing a single value (i.e. value in Cell A15992) to an entire array and thus getting that they are not a match.

You can use VLOOKUP like so
=IF(A15992=VLOOKUP(A15992,MyCatalog!A:A,1,0), "Duplicate", "Unique")
or more simply
=IFERROR(MATCH(A15992,MyCatalog!A:A,0),"Unique")
If there is a duplicate, it will return the row number (i.e 2297)
Does that work for you?