Using a formula to compare and highlight differences between two columns of ID numbers

[Anthony2017]

Hi I have two columns of ID numbers on a spread sheet, column 1 has ID numbers that have been imputed into and stored with in an ERP management system, column 2 are all the ID number from the customer.

What I want to do is compare the customer column to the ID numbers in the system so I know what has not been imputed.

Iv tried Vlookup and match but cant get it to work.

Attached is an example of the numbers in the columns.

The actual spread sheets have around 100 + ID numbers.

Any help would be appreciated.

[tim201110]

=isnumber(match(c3,$a$3:$a$22,))

[Anthony2017]

Thanks Tim, can I use conditional format with that formula to colour high light what is missing.

[Zer0Cool]

I usually do this using either a COUNTIF or using remove duplicates.

COUNTIF has the benefit of giving you a count for each ID. 0 means its absent from the other sheet, >0 means its there. Using this method I am either looking for a boolean there/not there or am looking for a pure count (in case I want to check for duplicates too). In the example ill assume your ID's to check for in another sheet start on C3

Return count (so you can see how many times its on the other sheet)

Please Login or Register  to view this content.

Return boolean (replacing isthere and isntthere with whatever you want it to return in those cases)

Please Login or Register  to view this content.

Remove duplicates is maybe not as helpful but can still get you there. I make a new blank sheet, paste the ID's from both sheets to it in a single column and use the built in remove duplicates. This leaves me with the ID's that are on only 1 of the 2 sheets but doesnt tell me which sheet. You could prior paste the sheet name in a 2nd column next to the ID's so that when you remove duplicates the unique ID's left also have what sheet they are on to the right. You then have a list of whats on sheet A but not be and whats on B but not A, thus allowing you to cross compare instead of comparing one way/direction.

tim201110's recommendation isnt bad either. His version gives boolean. If you did it without the ISNUMBER you could get which relative row it was on in the other sheet which may be helpful as well.

Many people try what you did using VLOOKUP. VLOOKUP is great when you want to extract information from the same row as a matching lookup value in a range, but its overly complex and not really intended for the simple evaluation you are trying to make.

[Zer0Cool]

Thanks Tim, can I use conditional format with that formula to colour high light what is missing.

You certainly could. You could conditional format on his true/false, on 0 or >0, etc.

A conditional format can essentially be based off of anything you can use formulas to break down to true/false or using the built in meeting a range of conditions.

[Anthony2017]

I other quick question. The spread sheet I have are from two different people, can formatting cause problems, if one guy copy and pasted numbers and the other spread sheet is exported form management software. Is it possible blank spaces are copied over and are causing problems. shot in the dark form me to be honest.

the formula is working on the sample I uploaded but not on my the work spread sheet.

[Zer0Cool]

Yes. Anytime you are doing a comparison or lookup you need to have an apples to apples comparison. What this usually means is going through a process called "normalizing your data".

Excel is very specific with its comparisons. For example "1" and 1 (1 as text and 1 as a number) are not the same thing to Excel even though you and I both see a 1 in the cell and think they are the same. By extension "Dave" and " Dave " look the same in a cell but are indeed not the same.

So you have a coulpe of things to evaluate. If the ID numbers are all numeric then formatting could be an issue (text vs number). If you cant solve that by setting the cells to be formatted as general/text then you can use 1 of 3 methods to change it. Sometimes cells that are numeric in nature but stored as text have a little box/exclamation point when you select the cell(s) with a warning that numbers are stored as text with an option to convert to numbers. You could alternatively put a number 1 in any blank cell, copy it, select all of your ID's and do a paste special | multiple (any number times 1 is the number) and Excel is smart enough to know text cant be multiplied to it effectively converts your values to numbers. The last option is select the column and use text to columns. Uncheck delimiters, select general or text as the format, finish. This converts them to the desired format without actually splitting them across columns.

If your ID numbers are alphanumeric then you likely have excess spaces and/or nonprint characters in the string. In this case you create a helper column next to each ID column and use a formula like:

Please Login or Register  to view this content.

Presuming A2 is the cell to the left/right with the ID and fill the formula down along all your ID's. Copy the results in the helper column and paste special | values over your original IDs, then delete your helper column. This removes non-print characters (invisible characters) and excess spaces other than single spaces between words from your IDs.

You can try some of the following to compare 2 values you think should match:

true if both are exactly the same, false otherwise

Please Login or Register  to view this content.

basically same as above

Please Login or Register  to view this content.

gives you number of characters in the cell (ie: if cell 1 is 8 characters and cell 2 is 9..they arent the same)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Anthony2017]

Thanks guy for your help it worked a treat, I'm wondering if I can extract the false values into a table. Attached is a spreadsheet with the numbers in yellow in the management system and white are not.

I'm just trying to tidy it up as much as possible.

[Glenn Kennedy]

Hi. Use this array formula in G2, copied down:

=IFERROR(INDEX(C:C, SMALL(IF(ISERROR(MATCH($C$2:$C$90, $A$2:$A$139, 0)), (ROW($C$2:$C$90))), ROWS($G$1:G1))),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Anthony2017]

Thanks a million Glenn, bit of work in that formula. Cheers appreciate it. Tough one to recreate. If I was to look up youtube videos what type for function would this be called.

[Glenn Kennedy]

A good question!! maybe "Excel list missing values between two columns". if you delete all but 4 or 5 rows, the easiest way to watch it work is to use Formulas/Evaluate formula and step through the process. You do need to seriously prune it down as the tiny window cannot be resized.

Anyhow.... You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[Anthony2017]

Ill try that and see how I go. I'm new to excel but I am enjoying discovering what its capable of. Manager at work had me using search within the filter function, one number at a time, thought to myself "this is madness"

Cant wait to go back after the holidays with this.

[Glenn Kennedy]

That's what managers are for... to drive their staff nuts.

(Ex-manager in public sector science).

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.