x
Solved

# Matching the number in two excel file

Posted on 2013-01-10
Medium Priority
341 Views
Hi Experts,

I've two excel files (File A and File B) that have order no.(both on colum A) which are similar but not exact.

Now, I would like to know what order number listed on File A is missing from File B and vice versa.

Desmond
0
Question by:desmondwkng
• 3
• 2

LVL 1

Expert Comment

ID: 38765989
Why are they on different shees?

Open both files, create named ranges, and do a vlookup.
would you like an example of what that formula would look like?
0

LVL 24

Expert Comment

ID: 38766140
Open both files and Perform a MATCH (VLOOKUP would be slow and overkill)

So in book 1 (row 1 in this case) you would have a formula...
=MATCH(A1,OrderNumbers Book 2 Sheet A:A, 0)
The 0 is nescessary to show exact matches only.
Then any rows which are #N/A are missing from the second workbook.
Reverse the procedure to find those in second workbook missing from first.
0

Author Comment

ID: 38770752
Dear Barman

Thanks, it's perfect,

But if I would like to show that the matched number as "Y" and the unmatched as "N", how should I modify the formula?

Desmond
0

Author Comment

ID: 38770757
Dear Andy,

There are two different files is because if come from different report and we would like to compare the result.

regards,

Desmond
0

LVL 24

Accepted Solution

Steve earned 1600 total points
ID: 38771760
This should do it for you...

=IF(ISNA(MATCH(A1,OrderNumbers Book 2 Sheet A:A, 0)),"N","Y")
0

Author Closing Comment

ID: 38772979
thanks
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.