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

tly0227

New Member
Joined
Oct 2, 2012
Messages
37
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.

1625077662943.png



1625077710158.png
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,013
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Oct 2, 2012
Messages
37
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

New Member
Joined
Oct 2, 2012
Messages
37
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

Well-known Member
Joined
Mar 5, 2018
Messages
2,013
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,144,424
Messages
5,724,249
Members
422,542
Latest member
jedidia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top