Match not matching all

**LoriM1** · 10-13-2006, 11:40 AM

Good Morning,
I have two spreadsheets that I am trying to match columns. After inserting my formula most items are matching but I have found at least 2 that didn't. I'm afraid there's more. I did have to change the font on one spreadsheet to match the other one. I concatenated and that didn't help. Any suggestions why this isn't working?

Thanks!!! Lori

**Vito** · 10-13-2006, 01:33 PM

There could be extraneous spaces in some of the cells of the lookup columns.

If that's not it, please post your formula for review.

**LoriM1** · 10-18-2006, 09:18 AM

I've submitted a portion of my spreadsheet for your review and highlighted the two items not matching.
Lori

**Bryan Hessey** · 10-18-2006, 09:35 AM

Originally Posted by LoriM1

I've submitted a portion of my spreadsheet for your review and highlighted the two items not matching.
Lori

A3 has a space before the comma, it's Len = 12
the table element doesn't - it's Len = 11

A5 does not appear in the table.

hth
---

**LoriM1** · 10-18-2006, 11:06 AM

Thanks alot! I reconcated column a and it did it again. Any idea why so I can make sure this doesn't happen again? Is there a formala to check for extraneous spaces? This file is actually over 1000 rows. I really appreciate the help.
Lori

**Bryan Hessey** · 10-20-2006, 01:44 AM

Originally Posted by LoriM1

Thanks alot! I reconcated column a and it did it again. Any idea why so I can make sure this doesn't happen again? Is there a formala to check for extraneous spaces? This file is actually over 1000 rows. I really appreciate the help.
Lori

I guess the problem here is the definition of 'extraneous spaces', as the space you had would probably only be found by replace space-comma by comma, but there are many cases where space-comma is legitimate.

However, in your sheet you can Replace All space-comma by comma.

You could also concatenate as

=Trim(A1)&", "&Trim(B1)

which might avoid that in future.

hth
---