Solved

Matching the number in two excel file

Posted on 2013-01-10
6
332 Views
Last Modified: 2013-01-13
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
0
Comment
Question by:desmondwkng
  • 3
  • 2
6 Comments
 
LVL 1

Expert Comment

by:andy_collette
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

by:Steve
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

by:desmondwkng
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:desmondwkng
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

by:
Steve earned 400 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

by:desmondwkng
ID: 38772979
thanks
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now