Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically merging 2 data sets that share some values
I'm guessing this is a fairly common problem. I have product return data for
each month in the year. Each sheet is similar: the 1st column is the product name & the second column is the # of returns for that product for that month. I would like to combine all of these sheets into a single sheet with the 1st column being the product name & the subsequent columns being the # of returns for each month in the year. The problem I have is that if a product has no returns in any given month, it is not included in that months data. The upshot is that each month's data has a variable # of rows & the 1st column of each month's data has some of the same values as the next month, but not all. Example of the data I have: Jan. Data: Feb. Data: Name Returns Name Returns product-a 2 product-b 3 product-c 5 product-c 4 product-d 4 product-d 2 product-f 2 product-e 6 product-g 1 product-f 1 Example of combined data I'd like to have: Name Jan. returns Feb. returns product-a 2 0 product-b 0 3 product-c 5 4 product-d 4 2 product-e 0 6 product-f 2 1 product-g 1 0 Any help you can provide in pointing me in the right direction for a VBA type macro to help merge these data sets would be greatly appreciated. I'm not even sure of the search terms to use for something like this. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically merging 2 data sets that share some values
Do you have a master list of all the product names or do you want the code
to generate such a list from all the sheets? If the code generates the list, that list would include only those products that have had at least one return that year. What are the names of the monthly sheets? HTH Otto "srsammsiam" wrote in message ... I'm guessing this is a fairly common problem. I have product return data for each month in the year. Each sheet is similar: the 1st column is the product name & the second column is the # of returns for that product for that month. I would like to combine all of these sheets into a single sheet with the 1st column being the product name & the subsequent columns being the # of returns for each month in the year. The problem I have is that if a product has no returns in any given month, it is not included in that months data. The upshot is that each month's data has a variable # of rows & the 1st column of each month's data has some of the same values as the next month, but not all. Example of the data I have: Jan. Data: Feb. Data: Name Returns Name Returns product-a 2 product-b 3 product-c 5 product-c 4 product-d 4 product-d 2 product-f 2 product-e 6 product-g 1 product-f 1 Example of combined data I'd like to have: Name Jan. returns Feb. returns product-a 2 0 product-b 0 3 product-c 5 4 product-d 4 2 product-e 0 6 product-f 2 1 product-g 1 0 Any help you can provide in pointing me in the right direction for a VBA type macro to help merge these data sets would be greatly appreciated. I'm not even sure of the search terms to use for something like this. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically merging 2 data sets that share some values
Otto,
Thank you for your time & expertise! No, I don't have a master list, so the code would need to generate that list by combining all the 1st columns & eliminating duplicates. As for file names, let's keep it simple for now & assume the files are all named "1.xls", "2.xls", etc. (I know enough programming to modify at a later time). If you're actually writing/adapting code could you have it ask how many files to combine? I do this return analysis every month of the year, so in the early months I have less than 12 files to combine. If easier, I could have all the files that need to be combined opened before running the macro. Thanks again. s. "Otto Moehrbach" wrote: Do you have a master list of all the product names or do you want the code to generate such a list from all the sheets? If the code generates the list, that list would include only those products that have had at least one return that year. What are the names of the monthly sheets? HTH Otto "srsammsiam" wrote in message ... I'm guessing this is a fairly common problem. I have product return data for each month in the year. Each sheet is similar: the 1st column is the product name & the second column is the # of returns for that product for that month. I would like to combine all of these sheets into a single sheet with the 1st column being the product name & the subsequent columns being the # of returns for each month in the year. The problem I have is that if a product has no returns in any given month, it is not included in that months data. The upshot is that each month's data has a variable # of rows & the 1st column of each month's data has some of the same values as the next month, but not all. Example of the data I have: Jan. Data: Feb. Data: Name Returns Name Returns product-a 2 product-b 3 product-c 5 product-c 4 product-d 4 product-d 2 product-f 2 product-e 6 product-g 1 product-f 1 Example of combined data I'd like to have: Name Jan. returns Feb. returns product-a 2 0 product-b 0 3 product-c 5 4 product-d 4 2 product-e 0 6 product-f 2 1 product-g 1 0 Any help you can provide in pointing me in the right direction for a VBA type macro to help merge these data sets would be greatly appreciated. I'm not even sure of the search terms to use for something like this. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically merging 2 data sets that share some values
You said that you had a number of "sheets" that you wanted to combine into
one sheet. Now you mention having "files". Which do you have? If you want to combine workbooks into one "master" workbook, are all these workbooks in one folder? Having them all in one folder, along with the "master" workbook, and no other workbooks in that folder, would preclude having to know the names of the workbooks. If you have workbooks, what is the layout of those workbooks. That is, how many sheets in each, how many sheets do you want to combine, are the sheet names pertinent, etc. Otto "srsammsiam" wrote in message ... Otto, Thank you for your time & expertise! No, I don't have a master list, so the code would need to generate that list by combining all the 1st columns & eliminating duplicates. As for file names, let's keep it simple for now & assume the files are all named "1.xls", "2.xls", etc. (I know enough programming to modify at a later time). If you're actually writing/adapting code could you have it ask how many files to combine? I do this return analysis every month of the year, so in the early months I have less than 12 files to combine. If easier, I could have all the files that need to be combined opened before running the macro. Thanks again. s. "Otto Moehrbach" wrote: Do you have a master list of all the product names or do you want the code to generate such a list from all the sheets? If the code generates the list, that list would include only those products that have had at least one return that year. What are the names of the monthly sheets? HTH Otto "srsammsiam" wrote in message ... I'm guessing this is a fairly common problem. I have product return data for each month in the year. Each sheet is similar: the 1st column is the product name & the second column is the # of returns for that product for that month. I would like to combine all of these sheets into a single sheet with the 1st column being the product name & the subsequent columns being the # of returns for each month in the year. The problem I have is that if a product has no returns in any given month, it is not included in that months data. The upshot is that each month's data has a variable # of rows & the 1st column of each month's data has some of the same values as the next month, but not all. Example of the data I have: Jan. Data: Feb. Data: Name Returns Name Returns product-a 2 product-b 3 product-c 5 product-c 4 product-d 4 product-d 2 product-f 2 product-e 6 product-g 1 product-f 1 Example of combined data I'd like to have: Name Jan. returns Feb. returns product-a 2 0 product-b 0 3 product-c 5 4 product-d 4 2 product-e 0 6 product-f 2 1 product-g 1 0 Any help you can provide in pointing me in the right direction for a VBA type macro to help merge these data sets would be greatly appreciated. I'm not even sure of the search terms to use for something like this. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically merging 2 data sets that share some values
Otto,
I wasn't being specific because I was planning on adapting to whatever solution was provided (I could easily combine all the sheets into 1 workbook if needed or vice-versa) but I can certainly be more specific. As I have it now, each workbook file contains only 1 worksheet with the 2 columns of data (column A is the model number & B is the # of returns). I can & will put all the workbooks for a given year in there own directory with no other .xls files. Though you said it wouldn't matter, the files will be named Jan-09.xls, Feb-09.xls, etc. If possible, please have the code copy the file name into row 1 of the combined data sheet (above the associated return data). It doesn't matter what order the return data columns are in. I can easily rearrange if the month/file name label is in the 1st row. Yes, you can also assume that the 'master' workbook would be in the same directory. As for the # of sheets, it will vary depending on how far into the year we are. I think I've answered all your questions, but if I hadn't, let me know. Note again, I've done a bit of VBA macro programming in Excel before, so if you were able to post the code for the basic mechanism of opening the files, combining the data, & eliminating duplicate model #'s, I think I could tweak things to match my situation. Thanks again, Steve "Otto Moehrbach" wrote: You said that you had a number of "sheets" that you wanted to combine into one sheet. Now you mention having "files". Which do you have? If you want to combine workbooks into one "master" workbook, are all these workbooks in one folder? Having them all in one folder, along with the "master" workbook, and no other workbooks in that folder, would preclude having to know the names of the workbooks. If you have workbooks, what is the layout of those workbooks. That is, how many sheets in each, how many sheets do you want to combine, are the sheet names pertinent, etc. Otto "srsammsiam" wrote in message ... Otto, Thank you for your time & expertise! No, I don't have a master list, so the code would need to generate that list by combining all the 1st columns & eliminating duplicates. As for file names, let's keep it simple for now & assume the files are all named "1.xls", "2.xls", etc. (I know enough programming to modify at a later time). If you're actually writing/adapting code could you have it ask how many files to combine? I do this return analysis every month of the year, so in the early months I have less than 12 files to combine. If easier, I could have all the files that need to be combined opened before running the macro. Thanks again. s. "Otto Moehrbach" wrote: Do you have a master list of all the product names or do you want the code to generate such a list from all the sheets? If the code generates the list, that list would include only those products that have had at least one return that year. What are the names of the monthly sheets? HTH Otto "srsammsiam" wrote in message ... I'm guessing this is a fairly common problem. I have product return data for each month in the year. Each sheet is similar: the 1st column is the product name & the second column is the # of returns for that product for that month. I would like to combine all of these sheets into a single sheet with the 1st column being the product name & the subsequent columns being the # of returns for each month in the year. The problem I have is that if a product has no returns in any given month, it is not included in that months data. The upshot is that each month's data has a variable # of rows & the 1st column of each month's data has some of the same values as the next month, but not all. Example of the data I have: Jan. Data: Feb. Data: Name Returns Name Returns product-a 2 product-b 3 product-c 5 product-c 4 product-d 4 product-d 2 product-f 2 product-e 6 product-g 1 product-f 1 Example of combined data I'd like to have: Name Jan. returns Feb. returns product-a 2 0 product-b 0 3 product-c 5 4 product-d 4 2 product-e 0 6 product-f 2 1 product-g 1 0 Any help you can provide in pointing me in the right direction for a VBA type macro to help merge these data sets would be greatly appreciated. I'm not even sure of the search terms to use for something like this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merging data sets using dates | Excel Discussion (Misc queries) | |||
Merging to data sets | Excel Worksheet Functions | |||
Aligning Two Sets of Data That Share Only Some Values | Excel Programming | |||
merging two sets of data | Excel Discussion (Misc queries) | |||
Merging 2 sets of data | Excel Worksheet Functions |