Matching the number in two excel file

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.

thanks for your help in advance.


Desmond
desmondwkngAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SteveConnect With a Mentor Commented:
This should do it for you...

=IF(ISNA(MATCH(A1,OrderNumbers Book 2 Sheet A:A, 0)),"N","Y")
0
 
Andrew ColletteCommented:
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
 
SteveCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
desmondwkngAuthor Commented:
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
 
desmondwkngAuthor Commented:
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
 
desmondwkngAuthor Commented:
thanks
0
All Courses

From novice to tech pro — start learning today.