Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching data/columns from two seperate files
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching data/columns from two seperate files
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. "Mike" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching data/columns from two seperate files
Great, but no experience with Macro's.. Can I get a description?
"Sheeloo" wrote: 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. "Mike" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching data/columns from two seperate files
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" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching data/columns from two seperate files
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" wrote: 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" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching data/columns from two seperate files
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" wrote: 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" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data connectivity across two seperate .xls files, interesting | Excel Worksheet Functions | |||
Seperate the data in two columns | Excel Discussion (Misc queries) | |||
Combining Data from Seperate Files | Excel Worksheet Functions | |||
Matching data from seperate excel files | Excel Worksheet Functions | |||
Vlook up for matching data in two seperate sheets | Excel Discussion (Misc queries) |