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".
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
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
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:
Don't forget to make the new macro auto-run on Workbook_open event instead of the former one ...
Hope you like it
compare-v2.xls
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 iDon't forget to make the new macro auto-run on Workbook_open event instead of the former one ...
Hope you like it
compare-v2.xls
ASKER
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.
swjtx99
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.
swjtx99
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.
compare-lists-12-Dec-00.xls
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.
compare-lists-12-Dec-00.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!!
swjtx99
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!!
swjtx99
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.
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.
ASKER
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 ;)
Regards,
swjtx99
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 ;)
Regards,
swjtx99
The button is just a shape inserted from the Insert>Illustrations>Shape
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:
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 jASKER
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?
Thanks
swjtx99
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?
Thanks
swjtx99
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 ...
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 ...
With both customer list and master list open...
in cell G2 paste the following formula:
=SUMIFS('[Master-List.xlsx
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+?