Hidden difference between copied cell and entered text when data & formatting is the same

[tly0227]

I'm trying to figure out what's going on.

I have a database of employees. Column E: Employee ID#
I have a table on a 2nd sheet, Files, that is a list of documents that I can't find Employee Folders for. Column B: Employee ID
I have a column, T: Unfiled docs flag, with the following formula: =IFERROR(IF(MATCH([@[Employee ID'#]],Table3[Employee ID],0),"Yes"),"No")
Works great, that's not the problem.

The cell formatting for both employee ID columns is identical. I've verified this every which way is possible.

The rows in the Employees sheet are a combination of either manually inputted by me, or copied/pasted from another file. The Files sheet is all manually inputted.

There is some hidden difference between the 2 and it's causing my formula to not work.

As an example, I have an employee ID # 1003711. On the files sheet, I create a row and type the ID number. On the Employees sheet, that row was originally copied from another file. Even though the formatting is exactly the same, the entered text is exactly the same, they don't come up as a match. Trying to figure this out, I did an if(entered cell = copied cell, "Match") formula and it does not show as a match. Screenshots below. B37 is the manually entered, B38 is the copied (from the employees sheet). 37 won't trigger the formula flag, 38 will.

What the heck is happening/how do I fix this? I'm so confused.

 

[Toadstool]

Hi Tly0227,

Formatting won't change how Excel hold a value so it won't change the results of a MATCH.

Two challenges spring to mind:

1. One field is held as text and one as a number (this is why fields to be keys should never be totally numeric). You can format both as General and remove any cell alignment. Text will default to left aligned and numeric as right aligned. You could also use =TYPE(celladdresssuwanttocheck) and numeric will give 1, text 2. They should both be the same.
2. Maybe both cells are text but "that row was originally copied from another file" contains strange characters. Try =LEN(celladdresssuwanttocheck) and from the look of your sample they should both be 7.

 

[tly0227]

Hi Tly0227,

Formatting won't change how Excel hold a value so it won't change the results of a MATCH.

Two challenges spring to mind:

1. One field is held as text and one as a number (this is why fields to be keys should never be totally numeric). You can format both as General and remove any cell alignment. Text will default to left aligned and numeric as right aligned. You could also use =TYPE(celladdresssuwanttocheck) and numeric will give 1, text 2. They should both be the same.
2. Maybe both cells are text but "that row was originally copied from another file" contains strange characters. Try =LEN(celladdresssuwanttocheck) and from the look of your sample they should both be 7.

I have a custom formatting for both because I need my leading zeroes to remain (not on the example, obvs, but others).

Looking at alignment....when removing that, cells that were copied are aligned left, and entered text is aligned right.

Looking at the =type...the entered cell is numeric, and copied is text. So not the same. How do I change that while still keeping my custom formatting (or in some way making sure my leading zeroes don't disappear (without having to type a ' at the front)? Out of curiosity, I changed the format to number on the copied cell and that didn't make any difference. I've got 7000 rows of copied data...would be a lot to manually change.

=LEN is 7 for both

I don't know if it matters, but I am working within a table on each sheet.

 

[tly0227]

Hi Tly0227,

Formatting won't change how Excel hold a value so it won't change the results of a MATCH.

Two challenges spring to mind:

1. One field is held as text and one as a number (this is why fields to be keys should never be totally numeric). You can format both as General and remove any cell alignment. Text will default to left aligned and numeric as right aligned. You could also use =TYPE(celladdresssuwanttocheck) and numeric will give 1, text 2. They should both be the same.
2. Maybe both cells are text but "that row was originally copied from another file" contains strange characters. Try =LEN(celladdresssuwanttocheck) and from the look of your sample they should both be 7.

Alright so I ended up turning on error checking and converting all the text cells to number, which overrode my customer format to keep leading zeros, but once everything was converted I was able to switch back to that custom format and all is good now.

Thanks for the help, I appreciate it.

 

[Toadstool]

You're welcome!

So you've changed Employee Id to be numeric throughout ...but please remember "cells that were copied are aligned left, and entered text is aligned right" means that if you copy any Employee Id from another book then it's very possible it will be text, and MATCH will become a lie to its name.

I first came across the challenge of using all numeric for a key field on computers back in 1977. It's a shame it's still happening and I know we've seen more than one other post on the forums with that challenge.