Avatar of swjtx99
 asked on

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

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".


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.


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,

Microsoft ExcelVisual Basic.NET

Avatar of undefined
Last Comment

8/22/2022 - Mon

in the example you have there, you can do the same thing using formula:

With both customer list and master list open...
in cell G2 paste the following formula:

then auto fill down (double click the black dot in G2)
then copy paste values.

I will have a look at reprogramming the code you have... do you also wish to be able to select just one criteria.. or three+?

I attached an updated file that is far simpler than the one you use
You can use it to check a match over as many columns as you need
What you need to do is create (I leave it to you, I provide most of the code) a very simple macrao that:
Copies data from Master to sheet 3
Copies data from Customer to sheet 2
(you can actually skip these 2 first steps if you modify the formulas in sheet1 to point to your external data)
Runs a double loop (a for loop nested into another one)
For i = Sheets("Sheet1").cells(3,2).value to max_row_master
    For j = Sheets("Sheet1").cells(4,2).value to max_row_customer
        If Sheets("Sheet1").cells(7,2).value = 1 Then ' There is a match!
        ' Short code portion to copy your values from Source to Destination Sheet
        End If
    Next j
Next i

Open in new window

Just modify cell B7 and extend formulas in B5 and B6 to the right in sheet1 to add or quit as many columns that you need to your test. Indicate which are the column pairs you're interested in on the forst 2 rows

Don't forget to make the new macro auto-run on Workbook_open event instead of the former one ...

Hope you like it

HI The_Barman,

Copying formulas into the "customer" sheet won't work. This has to be usable by people that don't know how to do that. It has to work the way my 1 column compare tool works, just comparing 2 columns instead of 1.

Hi sbaughan,

I'm not following. I ran the macro on the attachment and it looks like it just does 1 column. Not sure where the code you inserted comes in.

Here's what I envisioned:

In the master, I know I have 3 columns "MFG", "Model" and "Price"

So, the macro should ask me to open the master file (as it does now), then ask me to open the customer file (as it does now). Then it should tell me to enter the column letter of the data in the customer file that I want to compare to column A (MFG)  in the master file.

then it should ask me if I want to add another column (Y/N)

If No, it should do what it does now: ask me to enter the row # in the customer file to start comparing, then ask me to enter what column in the customer file do I want the data from column C in the master file to copy into.

It should then copy the data in C from the master file to the column I selected in the customer file in each row where there is a match. (this is what it does now). The change is in if the answer to adding another column is Yes.

If Yes, it should ask me to enter the column letter of the data in the customer list to compare to column B in the master list. It should then ask me to enter the row # the customer file to start comparing, then ask me to enter what column in the customer file do I want the data from column C in the master file to copy into.

It should then compare both selected columns in the customer list to A and B iin the master list and when it finds a match in the same row of both columns, copy the data from C in the master list to the colomn I selected, into the customer list.

The "master list" will always be the same with Mfg, Model, Price in A, B, C. The customer list could have Mfg, Model in any column and the name (header) of the column could be anything.

Hope this helps clarify and thanks for your time and attention.

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

hi there sw, attached is a file for the two column match using a dictionary object.

it is a tad rough around the edges at the moment.
but if you could test it then I can look at making it neater after we get the basics covered.

note that for now the column numbers (not letters) should be entered.
so in the example you provided it should be 6 3 7.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Hi sbaughan,

I could not stop laughing for almost half an hour after opening the file and seeing this HUGE blue button. I'm still laughing.

Thanks for your  patience and the additional explanation. I think what you've done here is even better than what I asked for. The ability to extend this to as many (or as few) columns to match as needed will make this extremely useful for me.  

Followup question: My "master list" can get huge (100K rows +) but most customer lists are 200 to 3000 rows. Will a huge master list cause any problems? I expect it could be slow but I can live with that.

Not in the original question but can I config Sheet1 to pull 2 columns of data from the master to the customer file?

Great solution. Thank you!!


Glad you found it useful ;)

As for the size of the master file, the time will be the only problem you may face, a 100K rows master file for a 3000 rows customer file would result in a 300 millions loops ...
Excel does not take much advantage of the multi-core architecture, on my quad-core, a 100K rows master with a 1000 rows customer requires a 10+ hours run ... it might not be acceptable to you. Is there a way you can filter the master data by type or something?
For example, you can run consecutively the macro on several selected master files (1 file by needed type) and avoid the run on the others master files to save time from unneeded loops ...

For the columns to pull, in the macro, you can follow the same logic and code to pull as many columns as you want.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

Thanks for the additional info.

I'll think about the way the masters are organized to improve run time. Not a biggie since I have an extra dedicated machine to do it and the real issue is unattended automation.

How do I shrink the blue button? jk ;)



The button is just a shape inserted from the Insert>Illustrations>Shapes>Basic Shapes menu
Right-click on it to get access to the corners and re-size it.
Another way to improve the run of the macro, would be to add an Exit For in your loop:
    For j = Sheets("Sheet1").Cells(4, 2).Value To Sheets("Sheet1").Cells(4, 3).Value
        Sheets("Sheet1").Cells(4, 2) = j
        If Sheets("Sheet1").Cells(8, 2).Value = 1 Then ' There is a match!
        ' Short code portion to copy your values from Source to Destination Sheet
        Sheets("Customer").Range(Cells(5, 2).Value & j).Value = Sheets("Master").Range("C" & i).Value
        Exit For
        End If
    Next j

Open in new window

So when the match is found, the macro just doesn't run through the rest of the list (I guess there is only 1 possible match per item)

Hi sbaughan,

Hmm. I don't think this is going to work. I just got a master list inserted of 151,000 rows (after saving as .xlsm) and a customer list of 2000 rows and it looks like it is comparing each row of the master to each row of the customer list. Seems like it would be better to compare each row of the customer list to each row of the master. That way, it has 2000 compares to do vs. 151,000 to do.

I know I said run time isn't a big issue but even a small customer list would take a really long time.

Is it possible to reverse the logic?


Your help has saved me hundreds of hours of internet surfing.

The logic is a loop nested into another loop, so it'll be either 2.000 * 151.000 or 151.000 * 2.000 so that will be the same at the end.
That's why the best way would be to have several master files, just run the macro on the few ones that are useful, discard the run on the other ones (that would reduce the 151.000 items list) and add that "Exit For" command in the code as shown in the previous post. It'll save precious time.
Actually, you don't have many (simple) options if you want to check a match over several columns. You have to check them all or go for something really complicated.

A (not so) more complicated option is to have your master file sorted A->Z for the 1st column check. Start your checking loop from the row that corresponds to the Value you're checking in the customer's list, for example: You're looking for a "Diet" "Coke", like in your example, start your loop in the master file at the first row that starts with a "D" for "Diet" and exit the loop when you reach a row that starts with an "E" so you limit the number of rows you run you loop on. I can reduce from 151.000 to something like a few hundreds ...