Solved

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

Posted on 2013-01-06
11
341 Views
Last Modified: 2013-01-16
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
0
Comment
Question by:swjtx99
  • 5
  • 4
  • 2
11 Comments
 
LVL 24

Expert Comment

by:Steve
Comment Utility
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:
=SUMIFS('[Master-List.xlsx]Sheet1'!$C:$C,'[Master-List.xlsx]Sheet1'!$A:$A,F2,'[Master-List.xlsx]Sheet1'!$B:$B,C2)

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+?
0
 
LVL 5

Expert Comment

by:sbaughan
Comment Utility
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
compare-v2.xls
0
 

Author Comment

by:swjtx99
Comment Utility
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
0
 
LVL 24

Expert Comment

by:Steve
Comment Utility
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
0
 
LVL 5

Accepted Solution

by:
sbaughan earned 500 total points
Comment Utility
OK ...
I uploaded the complete file ... following the explanation I gave you in the first post.
This new file does exactly what you describe.

The only thing you need is to copy your data from your source files to the sheet 2 and 3 of this file I provide. The cell you need to check before you begin are in green. Click the big button and copy the results back to your customer file if needed (you can save the "Customer" sheet to replace the Customer file).

You can add as many test-column pairs as you want in lines 1 and 2 of the 1st sheet.
Cells B1 and B2 represent a pair, C1 and C2 an other pair ... add D1 and D2 as a 3rd pair if you want, for example.
To take them into account, just modify cell B7 (very simple, you'll see) and extend formulas in B5 and B6 to the right (to match the number of pairs)
The cell you need to check before you begin are in green
compare-v2.xls
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Closing Comment

by:swjtx99
Comment Utility
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
0
 
LVL 5

Expert Comment

by:sbaughan
Comment Utility
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.
0
 

Author Comment

by:swjtx99
Comment Utility
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
0
 
LVL 5

Expert Comment

by:sbaughan
Comment Utility
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)
0
 

Author Comment

by:swjtx99
Comment Utility
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
0
 
LVL 5

Expert Comment

by:sbaughan
Comment Utility
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 ...
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

744 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

10 Experts available now in Live!

Get 1:1 Help Now