count if match based on two criterias not working with text

for some reason, the space between the names is different in sheet 1 compared to sheet 2. Sheet1 space is code 32, sheet2 space is 160

on sheet2, use this formula, copied down, to changethe 160 "space" to a 32 "space"...
=LEFT(A2,FIND(",",A2,1))&" "&RIGHT(A2,FIND(",",A2,1)+2)

then use your vlookup to find what you need

Never mind I figured out what was happening. Turns out the software from which I got the info for sheet 2 export from, claims to be an excel export however the file encoding tells a different story.

It is unbelievable, just gimme me the file as it truly is! it is like the true file format should have been ".openoffice" and they just changed the file extension to ".xls" and claimed it was an excel export. yeah excel will open it but making it work with formulas is another story. for example the instead of space for space, the file uses "~" to delimit spaces...so if i compare anything with spaces I would not get the same result even though the "~" is not visible unless you open the file in a HEX editor.

WOW!!!!!!!!!!!!!!!!!! So frustrating!!!!!!!!!!!!!! Wasted a whole day on this.............................


Edit: thanks for your help FDibbins! I saw your reply after I posted.

glad to help, at least you can use my formula to get both sets of data talking to each other if you need to

Hi,

Although the names look the same they aren't.
Take the name Alan, Lucille. What appears to be a space character between the , and L is the character whose code is 32 on sheet 1 and code 160 on sheet 2.

You need to get them changed. The easiest would be to revert to the data from whence you extracted these lists (perhaps a csv file extracted from some other software) and ensure they are consistent. If not you'll need to copy the character from a name on each sheet and use Find Replace on the whole column and replace the character with a space character.

When you've done this you can then use
=MATCH(A2,'sheet 2'!A:A,FALSE)
in C2 on sheet1 copied down. Then filter on column C for #N/A and copy the filtered rows to a third sheet.

i used =exact() and narrowed it down to the space. then i used =code() to see what the code was for each space (all done in excel)

In Excel with =Code(Mid(A2,6,1))

good to know. that would have saved me hours!

Now I know thanks to you guys!

Thanks