troubleshooting Question

Compare two files and fill in data from one to the other

Avatar of swjtx99
swjtx99 asked on
Microsoft ExcelVisual Basic.NET
11 Comments1 Solution466 ViewsLast Modified:
The attached "compare list.xls" contains a Macro.

What is does is compare column A in one file "master" with a column you select in the "customer file". When it finds a match, it copies the data in Column B of the "master" to a column you select in the "customer file".

Example:

Master file                                      Customer file
      
A                 B                                   E            K
Coke          12                                 Coke      12    <copied to K      

The Macro found "Coke" and copied "12" from column B of the master to column K of the
customer file.


The change I need is for the macro to match 2 columns of data and do the same thing.

Example:

Master File                                                        Customer File

A            B                  C                                   E               J                L
Diet       Coke             33                                Diet       Coke          33 < copied to L


macro matches E and J and copies 33 from C in master file to L in customer file.


In the attached "Master File" There are 3 columns of data. I need the macro to match the first column (A) to a column I select in the "customer file" and match the second column (B) to a column I select in the customer file, then copy the data in column C of the master file to a column I choose in the customer file.

I need to be able to pick the columns because the customer file could have the info I'm trying to match in any column and the header could have any name.

In the example files, I'd pick column F in the customer file to match to column A in the master file, then I'd pick column C in the customer file to match to Column B in the master file, then I'd pick column G in the customer file to insert the data from column C in the master file.

Even better would be if the macro had an option to compare either one or two columns of data with the data to insert always coming from C in the master but having the option to match A from the master file to 1 column in the customer file or both A and B in the master to 2 columns in the customer file.  

It is important that the customer file not be modified, sorted or reformatted.

P.S. The compare lists macro i'm attaching was given to me and has been very useful for more than 12 years although for the last 11 years, I've always wished it could do 2 columns of data, hence my submission here.    

Thanks in advance,

swjtx99
Master-List.xlsx
Customer-List.xlsx
compare-lists-12-Dec-00.xls
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros