Matching data/columns from two seperate files

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Can someone tell me the best way to list data that comes from two seperate
files?

Example: I have two files
1) A Plant & Store master excel file (list all store numbers and the plant)
2) Another excel file that list store numbers and a store score.

Would like to create a third file/report that shows the plant, store number,
and score (and ensure the master file poplulates the report plant & store
number). This report would be updated from the original two files (they get
saved quarterly so want this report updated automatically).

Thanks for any suggestions.
 
Essentially you need to use VLOOKUP for the store score where you have Plant
and Store Number.

Depending upon your preferences you can write a Macro which copies the
master file and then adds VOOKUP to the third column or keep a template where
you paste the first two rows from the master and have the VLOOKUP in the
third column.
 
You simply need to put lookup formulas in a copy of the Plant & Store file.
Something like:

=VLOOKUP(B2,[Book6]Sheet1!$A$1:$B$5,2,FALSE)

Where B2 is the cell with the store number, [Book6] is the name of the
"Store Score" file, Sheet1 is the name of the sheet in the "Store Score" file
and $A$1:$B$5 is the range on Sheet1 that has the Store Number and the Store
Score, and 2 is the number of the column in that range that has the Store
Score. The Store Number must be the first column in that range.

Let me know if I can confuse you further.

Tom
 
Why would I put the lookup formula in the plant & store file? I am trying to
get the information in the report file, but matching what is in the other two
files.

TomPl said:
You simply need to put lookup formulas in a copy of the Plant & Store file.
Something like:

=VLOOKUP(B2,[Book6]Sheet1!$A$1:$B$5,2,FALSE)

Where B2 is the cell with the store number, [Book6] is the name of the
"Store Score" file, Sheet1 is the name of the sheet in the "Store Score" file
and $A$1:$B$5 is the range on Sheet1 that has the Store Number and the Store
Score, and 2 is the number of the column in that range that has the Store
Score. The Store Number must be the first column in that range.

Let me know if I can confuse you further.

Tom

Mike said:
Can someone tell me the best way to list data that comes from two seperate
files?

Example: I have two files
1) A Plant & Store master excel file (list all store numbers and the plant)
2) Another excel file that list store numbers and a store score.

Would like to create a third file/report that shows the plant, store number,
and score (and ensure the master file poplulates the report plant & store
number). This report would be updated from the original two files (they get
saved quarterly so want this report updated automatically).

Thanks for any suggestions.
Click to expand...
Click to expand...
 
I assumed that the Plant & Store file is static (i.e. the list of plants and
stores does not change). So, your Report file would have a copy of the Plant
& Store data in it and you could just lookup the Score.

Does that make sense?

Mike said:
Why would I put the lookup formula in the plant & store file? I am trying to
get the information in the report file, but matching what is in the other two
files.

TomPl said:
You simply need to put lookup formulas in a copy of the Plant & Store file.
Something like:

=VLOOKUP(B2,[Book6]Sheet1!$A$1:$B$5,2,FALSE)

Where B2 is the cell with the store number, [Book6] is the name of the
"Store Score" file, Sheet1 is the name of the sheet in the "Store Score" file
and $A$1:$B$5 is the range on Sheet1 that has the Store Number and the Store
Score, and 2 is the number of the column in that range that has the Store
Score. The Store Number must be the first column in that range.

Let me know if I can confuse you further.

Tom

Mike said:
Can someone tell me the best way to list data that comes from two seperate
files?

Example: I have two files
1) A Plant & Store master excel file (list all store numbers and the plant)
2) Another excel file that list store numbers and a store score.

Would like to create a third file/report that shows the plant, store number,
and score (and ensure the master file poplulates the report plant & store
number). This report would be updated from the original two files (they get
saved quarterly so want this report updated automatically).

Thanks for any suggestions.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top