Automatically merging 2 data sets that share some values

[srsammsiam]

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.

 

[Otto Moehrbach]

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]

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]

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]

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]

Steve

Put all the following code in a regular module of the master
file. I wrote the code in 3 macros because putting it all in one would have
been harder to follow. For you and me both.

I set up a dummy folder with 3 dummy files with some data in
each and it appears to work like you want. The code removes the .xls from
the file names before it places them as column headers.

Try this code in a copy of your file and make sure it does what
you want. When you paste this code into the module, watch out for line
wrapping. Posting code in a newsgroup tends to produce line wrapping and
this will result in errors when you run the code. If you wish, send me an
email and I'll send you the small files I used for this. My email address
is (e-mail address removed). Mention the name "srsammsiam" in your
email so I won't get you mixed up with someone else. Otto



Option Explicit
Dim MasterWB As Workbook
Dim rMasterColA As Range
Dim rwbColA As Range
Dim i As Range
Dim DestCol As Long
Dim DestRow As Long
Dim wb As Workbook

Sub CombineData()
Dim TheFile As String
Dim ThePath As String
Application.ScreenUpdating = False
Set MasterWB = ThisWorkbook
ThePath = ThisWorkbook.Path
ChDir ThePath
TheFile = Dir("*.xls") 'The name of the first .xls file
Do While TheFile <> ""
If TheFile <> MasterWB.Name Then
DestCol = Cells(1, Columns.Count).End(xlToLeft).Offset(,
1).Column
Cells(1, DestCol).NumberFormat = "@"
Cells(1, DestCol) = Left(TheFile, 6)
Call SetMasterWBColA
Set wb = Workbooks.Open(ThePath & "\" & TheFile)
'Note that TheFile is now the active workbook
Set rwbColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Call CopyData
wb.Close
End If
TheFile = Dir 'The name of the next file in the folder
'Note that TheFile will be blank ("") when all files have been done.
Loop
Application.ScreenUpdating = True
End Sub



Private Sub SetMasterWBColA()
If Not IsEmpty(Range("A2").Value) Then
Set rMasterColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Else
Set rMasterColA = Range("A2")
End If
End Sub



Private Sub CopyData()
'Workbook TheFile is the active file
For Each i In rwbColA
If Not rMasterColA.Find(What:=i, LookAt:=xlWhole) Is Nothing Then
DestRow = rMasterColA.Find(What:=i, LookAt:=xlWhole).Row
Else
With MasterWB.ActiveSheet
DestRow = .Range("A" & Rows.Count).End(xlUp).Offset(1).Row
.Cells(DestRow, 1) = i.Value
End With
End If
MasterWB.ActiveSheet.Cells(DestRow, DestCol) = i.Offset(, 1).Value
Next i
End Sub