Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
Hello group,
I have about 300 workbooks with just one sheet each (Sheet1). Each worksheet has about 130 columns and anywhere from 20 to 2000 rows. I need to combine all of them into one data source for analysis. Here's the kicker - About 70 of those columns are "standard" meaning that they have the same column header, etc. The rest are variable. This data was collected over a period of three years and standardizations were not employed very well. So some of those "variable" column headers may overlap or some may not. For instance (using a small subset of total columns as example): Book A has the following column headers (and related info) in columns X,Y,Z respectively: Column X: Status Column Y: /Hotel/Accomodations Column Z: /Credit Card Number Book B has the following column headers (and related info) in columns X,Y,Z respectively: Column X: /Smoking Column Y: Status Column Z: /Badge First Name Is there ANY way to combine all of those spreadsheets into one data source? The logic would probably go something like: If Sheet B were being added to Sheet A, the system would check to see if the column header for Sheet B / Column X existed as a column header in Sheet A. If it did exist, the related row data would then be pasted in that respective column. If it did not, the column header would be created at the top of Sheet A and the related row data would be place under it (but still relative to the row that it is associated with). Does this make sense? I can't for the life of me find any way to do this other than to manually check which columns match and if they don't, to add it as a new column and then move the related row data into that column when combining the sheets. As you can imagine this will take a long time to compile. I can use Access to identify these unmatched columns more effectively, but I have to imagine there is a more elegant solution to this problem. I mean, isn't it a truism that for every problem you encounter there is someone who has encountered and solved the same problem? Are you out there? Thanks again. I will check back periodically as this is a very pressing issue at the moment. Also, I can best be contacted at " thank you, - S |
#2
![]() |
|||
|
|||
![]()
Bob,
The general approach would be to use a macro that loops through the workbooks, reads the column heading and sees if it currently exists in the combined sheet: if it does, it adds the data at the bottom of the existiung column, if it doesn't it adds a new column with a new header and then adds the data into the sames rows as for existing items. The coding shouldn't be too hard - but I have to go right now, so I can't do it for you. But maybe that will give you a clue to get started. HTH, Bernie MS Excel MVP "Bob Dobalina" wrote in message ... Hello group, I have about 300 workbooks with just one sheet each (Sheet1). Each worksheet has about 130 columns and anywhere from 20 to 2000 rows. I need to combine all of them into one data source for analysis. Here's the kicker - About 70 of those columns are "standard" meaning that they have the same column header, etc. The rest are variable. This data was collected over a period of three years and standardizations were not employed very well. So some of those "variable" column headers may overlap or some may not. For instance (using a small subset of total columns as example): Book A has the following column headers (and related info) in columns X,Y,Z respectively: Column X: Status Column Y: /Hotel/Accomodations Column Z: /Credit Card Number Book B has the following column headers (and related info) in columns X,Y,Z respectively: Column X: /Smoking Column Y: Status Column Z: /Badge First Name Is there ANY way to combine all of those spreadsheets into one data source? The logic would probably go something like: If Sheet B were being added to Sheet A, the system would check to see if the column header for Sheet B / Column X existed as a column header in Sheet A. If it did exist, the related row data would then be pasted in that respective column. If it did not, the column header would be created at the top of Sheet A and the related row data would be place under it (but still relative to the row that it is associated with). Does this make sense? I can't for the life of me find any way to do this other than to manually check which columns match and if they don't, to add it as a new column and then move the related row data into that column when combining the sheets. As you can imagine this will take a long time to compile. I can use Access to identify these unmatched columns more effectively, but I have to imagine there is a more elegant solution to this problem. I mean, isn't it a truism that for every problem you encounter there is someone who has encountered and solved the same problem? Are you out there? Thanks again. I will check back periodically as this is a very pressing issue at the moment. Also, I can best be contacted at " thank you, - S |
#3
![]() |
|||
|
|||
![]()
Thanks Bernie,
VB is not my specialty - I have some background in fixing existing code and working with this forum to modify code provided by MVPs... I always appreciate the help and only use it in instances where I am profoundly stumped. If anyone is reading this and has any idea how I would implement this I would be very, very appreciative. This is something I need to do one way or the other over the next week and would prefer not to spend 24 hours a day doing it manually. :-P If anyone can give me some clues on the coding of this idea...... I'll buy you some champagne or something. heheh thanks!!!!! - S "Bernie Deitrick" wrote: Bob, The general approach would be to use a macro that loops through the workbooks, reads the column heading and sees if it currently exists in the combined sheet: if it does, it adds the data at the bottom of the existiung column, if it doesn't it adds a new column with a new header and then adds the data into the sames rows as for existing items. The coding shouldn't be too hard - but I have to go right now, so I can't do it for you. But maybe that will give you a clue to get started. HTH, Bernie MS Excel MVP "Bob Dobalina" wrote in message ... Hello group, I have about 300 workbooks with just one sheet each (Sheet1). Each worksheet has about 130 columns and anywhere from 20 to 2000 rows. I need to combine all of them into one data source for analysis. Here's the kicker - About 70 of those columns are "standard" meaning that they have the same column header, etc. The rest are variable. This data was collected over a period of three years and standardizations were not employed very well. So some of those "variable" column headers may overlap or some may not. For instance (using a small subset of total columns as example): Book A has the following column headers (and related info) in columns X,Y,Z respectively: Column X: Status Column Y: /Hotel/Accomodations Column Z: /Credit Card Number Book B has the following column headers (and related info) in columns X,Y,Z respectively: Column X: /Smoking Column Y: Status Column Z: /Badge First Name Is there ANY way to combine all of those spreadsheets into one data source? The logic would probably go something like: If Sheet B were being added to Sheet A, the system would check to see if the column header for Sheet B / Column X existed as a column header in Sheet A. If it did exist, the related row data would then be pasted in that respective column. If it did not, the column header would be created at the top of Sheet A and the related row data would be place under it (but still relative to the row that it is associated with). Does this make sense? I can't for the life of me find any way to do this other than to manually check which columns match and if they don't, to add it as a new column and then move the related row data into that column when combining the sheets. As you can imagine this will take a long time to compile. I can use Access to identify these unmatched columns more effectively, but I have to imagine there is a more elegant solution to this problem. I mean, isn't it a truism that for every problem you encounter there is someone who has encountered and solved the same problem? Are you out there? Thanks again. I will check back periodically as this is a very pressing issue at the moment. Also, I can best be contacted at " thank you, - S |
#4
![]() |
|||
|
|||
![]()
S,
And here I thought you were "Bob"! Try the macro below. Select the files when prompted, using shift and/or ctrl to select multiple files. The data will be arranged as well as possible, with blanks where there isn't any matching data. The first column is used as a key to show the file name. This assumes you don't have a field named "Source" The assumptions: you have a database on each of the activesheets of each of the workbooks, and the data starts in cell A1 and is contiguous: there are no blanks. (If that isn't the case, we can work around that.) The field headers are spelled the same, or else they are treated as different headers. The final assumptions (and these two would require a bigger re-write) is that you will not have more than 254 fields, nor more than 65,535 records. Try it on a few files first to check whether it actually works on your files. It worked on my 3 small test files. When we get this to work, you can send the champagne Fed-Ex.... ;-) HTH, Bernie MS Excel MVP Sub ConsolidateDatabases() Dim filearray As Variant Dim BaseBook As Workbook Dim BaseSheet As Worksheet Dim myBook As Workbook Dim myCell As Range Dim myColumn As Integer Dim myRow As Long Dim myCount As Integer Dim i As Integer With Application .DisplayAlerts = False .EnableEvents = False .ScreenUpdating = False End With filearray = Application.GetOpenFilename(MultiSelect:=True) If IsArray(filearray) Then Set BaseBook = Workbooks.Open(filearray(LBound(filearray))) Set BaseSheet = BaseBook.ActiveSheet Range("A1").EntireColumn.Insert Intersect(BaseSheet.Range("B:B"), _ BaseSheet.UsedRange).Offset(0, -1).Value = BaseBook.Name Range("A1").Value = "Source" For i = LBound(filearray) + 1 To UBound(filearray) myRow = BaseSheet.UsedRange.Rows.Count + 1 Set myBook = Workbooks.Open(filearray(i)) For Each myCell In Intersect(Range("1:1"), _ ActiveSheet.UsedRange) If myCell.Value < "" Then If IsError(Application.Match(myCell.Value, _ BaseSheet.Range("1:1"), False)) Then With BaseSheet.Range("IV1").End(xlToLeft)(1, 2) .Value = myCell.Value myColumn = .Column End With Else myColumn = Application.Match(myCell.Value, _ BaseSheet.Range("1:1"), False) End If myCount = Range(myCell(2), myCell.End(xlDown)).Cells.Count Range(myCell(2), myCell.End(xlDown)).Copy _ BaseSheet.Cells(myRow, myColumn) End If Next myCell BaseSheet.Cells(myRow, 1).Resize(myCount).Value = myBook.Name myBook.Close False Next i End If BaseBook.SaveAs Application.GetSaveAsFilename("Consolidated file.xls") With Application .DisplayAlerts = True .EnableEvents = True .ScreenUpdating = True End With End Sub "Bob Dobalina" wrote in message ... Thanks Bernie, VB is not my specialty - I have some background in fixing existing code and working with this forum to modify code provided by MVPs... I always appreciate the help and only use it in instances where I am profoundly stumped. If anyone is reading this and has any idea how I would implement this I would be very, very appreciative. This is something I need to do one way or the other over the next week and would prefer not to spend 24 hours a day doing it manually. :-P If anyone can give me some clues on the coding of this idea...... I'll buy you some champagne or something. heheh thanks!!!!! - S "Bernie Deitrick" wrote: Bob, The general approach would be to use a macro that loops through the workbooks, reads the column heading and sees if it currently exists in the combined sheet: if it does, it adds the data at the bottom of the existiung column, if it doesn't it adds a new column with a new header and then adds the data into the sames rows as for existing items. The coding shouldn't be too hard - but I have to go right now, so I can't do it for you. But maybe that will give you a clue to get started. HTH, Bernie MS Excel MVP "Bob Dobalina" wrote in message ... Hello group, I have about 300 workbooks with just one sheet each (Sheet1). Each worksheet has about 130 columns and anywhere from 20 to 2000 rows. I need to combine all of them into one data source for analysis. Here's the kicker - About 70 of those columns are "standard" meaning that they have the same column header, etc. The rest are variable. This data was collected over a period of three years and standardizations were not employed very well. So some of those "variable" column headers may overlap or some may not. For instance (using a small subset of total columns as example): Book A has the following column headers (and related info) in columns X,Y,Z respectively: Column X: Status Column Y: /Hotel/Accomodations Column Z: /Credit Card Number Book B has the following column headers (and related info) in columns X,Y,Z respectively: Column X: /Smoking Column Y: Status Column Z: /Badge First Name Is there ANY way to combine all of those spreadsheets into one data source? The logic would probably go something like: If Sheet B were being added to Sheet A, the system would check to see if the column header for Sheet B / Column X existed as a column header in Sheet A. If it did exist, the related row data would then be pasted in that respective column. If it did not, the column header would be created at the top of Sheet A and the related row data would be place under it (but still relative to the row that it is associated with). Does this make sense? I can't for the life of me find any way to do this other than to manually check which columns match and if they don't, to add it as a new column and then move the related row data into that column when combining the sheets. As you can imagine this will take a long time to compile. I can use Access to identify these unmatched columns more effectively, but I have to imagine there is a more elegant solution to this problem. I mean, isn't it a truism that for every problem you encounter there is someone who has encountered and solved the same problem? Are you out there? Thanks again. I will check back periodically as this is a very pressing issue at the moment. Also, I can best be contacted at " thank you, - S |
#5
![]() |
|||
|
|||
![]()
heheh... Bob is a pseudonym. :-) It was the name from a hip-hop song back
when I was growing up and I always thought it had a funny sound to it. Bernie, this is awesome. I am going to tool around with this over the weekend. If it isn't perfect it is a great starting place and that's really what I needed. So I appreciate it. Of course, so as to avoid actually having to follow through with the champagne promise, I will never admit that it actually worked. But.... Kidding... I'll let you know on Monday how things are going. Once again, thanks! Have a great weekend!! :-) cheers, - S "Bernie Deitrick" wrote: S, And here I thought you were "Bob"! Try the macro below. Select the files when prompted, using shift and/or ctrl to select multiple files. The data will be arranged as well as possible, with blanks where there isn't any matching data. The first column is used as a key to show the file name. This assumes you don't have a field named "Source" The assumptions: you have a database on each of the activesheets of each of the workbooks, and the data starts in cell A1 and is contiguous: there are no blanks. (If that isn't the case, we can work around that.) The field headers are spelled the same, or else they are treated as different headers. The final assumptions (and these two would require a bigger re-write) is that you will not have more than 254 fields, nor more than 65,535 records. Try it on a few files first to check whether it actually works on your files. It worked on my 3 small test files. When we get this to work, you can send the champagne Fed-Ex.... ;-) HTH, Bernie MS Excel MVP Sub ConsolidateDatabases() Dim filearray As Variant Dim BaseBook As Workbook Dim BaseSheet As Worksheet Dim myBook As Workbook Dim myCell As Range Dim myColumn As Integer Dim myRow As Long Dim myCount As Integer Dim i As Integer With Application .DisplayAlerts = False .EnableEvents = False .ScreenUpdating = False End With filearray = Application.GetOpenFilename(MultiSelect:=True) If IsArray(filearray) Then Set BaseBook = Workbooks.Open(filearray(LBound(filearray))) Set BaseSheet = BaseBook.ActiveSheet Range("A1").EntireColumn.Insert Intersect(BaseSheet.Range("B:B"), _ BaseSheet.UsedRange).Offset(0, -1).Value = BaseBook.Name Range("A1").Value = "Source" For i = LBound(filearray) + 1 To UBound(filearray) myRow = BaseSheet.UsedRange.Rows.Count + 1 Set myBook = Workbooks.Open(filearray(i)) For Each myCell In Intersect(Range("1:1"), _ ActiveSheet.UsedRange) If myCell.Value < "" Then If IsError(Application.Match(myCell.Value, _ BaseSheet.Range("1:1"), False)) Then With BaseSheet.Range("IV1").End(xlToLeft)(1, 2) .Value = myCell.Value myColumn = .Column End With Else myColumn = Application.Match(myCell.Value, _ BaseSheet.Range("1:1"), False) End If myCount = Range(myCell(2), myCell.End(xlDown)).Cells.Count Range(myCell(2), myCell.End(xlDown)).Copy _ BaseSheet.Cells(myRow, myColumn) End If Next myCell BaseSheet.Cells(myRow, 1).Resize(myCount).Value = myBook.Name myBook.Close False Next i End If BaseBook.SaveAs Application.GetSaveAsFilename("Consolidated file.xls") With Application .DisplayAlerts = True .EnableEvents = True .ScreenUpdating = True End With End Sub "Bob Dobalina" wrote in message ... Thanks Bernie, VB is not my specialty - I have some background in fixing existing code and working with this forum to modify code provided by MVPs... I always appreciate the help and only use it in instances where I am profoundly stumped. If anyone is reading this and has any idea how I would implement this I would be very, very appreciative. This is something I need to do one way or the other over the next week and would prefer not to spend 24 hours a day doing it manually. :-P If anyone can give me some clues on the coding of this idea...... I'll buy you some champagne or something. heheh thanks!!!!! - S "Bernie Deitrick" wrote: Bob, The general approach would be to use a macro that loops through the workbooks, reads the column heading and sees if it currently exists in the combined sheet: if it does, it adds the data at the bottom of the existiung column, if it doesn't it adds a new column with a new header and then adds the data into the sames rows as for existing items. The coding shouldn't be too hard - but I have to go right now, so I can't do it for you. But maybe that will give you a clue to get started. HTH, Bernie MS Excel MVP "Bob Dobalina" wrote in message ... Hello group, I have about 300 workbooks with just one sheet each (Sheet1). Each worksheet has about 130 columns and anywhere from 20 to 2000 rows. I need to combine all of them into one data source for analysis. Here's the kicker - About 70 of those columns are "standard" meaning that they have the same column header, etc. The rest are variable. This data was collected over a period of three years and standardizations were not employed very well. So some of those "variable" column headers may overlap or some may not. For instance (using a small subset of total columns as example): Book A has the following column headers (and related info) in columns X,Y,Z respectively: Column X: Status Column Y: /Hotel/Accomodations Column Z: /Credit Card Number Book B has the following column headers (and related info) in columns X,Y,Z respectively: Column X: /Smoking Column Y: Status Column Z: /Badge First Name Is there ANY way to combine all of those spreadsheets into one data source? The logic would probably go something like: If Sheet B were being added to Sheet A, the system would check to see if the column header for Sheet B / Column X existed as a column header in Sheet A. If it did exist, the related row data would then be pasted in that respective column. If it did not, the column header would be created at the top of Sheet A and the related row data would be place under it (but still relative to the row that it is associated with). Does this make sense? I can't for the life of me find any way to do this other than to manually check which columns match and if they don't, to add it as a new column and then move the related row data into that column when combining the sheets. As you can imagine this will take a long time to compile. I can use Access to identify these unmatched columns more effectively, but I have to imagine there is a more elegant solution to this problem. I mean, isn't it a truism that for every problem you encounter there is someone who has encountered and solved the same problem? Are you out there? Thanks again. I will check back periodically as this is a very pressing issue at the moment. Also, I can best be contacted at " thank you, - S |
#6
![]() |
|||
|
|||
![]()
=Hey Bernie,
I am trying to implement this today and am running into a "Run-time error 6"... Then it says simply "overflow". When I debug, I get this line as the culprit: "myCount = Range(myCell(2), myCell.End(xlDown)).Cells.Count" I'm just guessing that you've got a good idea about why this is happening (per your comments in posts) so I will defer to your judgement before going off on a goose chase. The champagne offer still stands... ;-) cheers, - S "Bernie Deitrick" wrote: S, And here I thought you were "Bob"! Try the macro below. Select the files when prompted, using shift and/or ctrl to select multiple files. The data will be arranged as well as possible, with blanks where there isn't any matching data. The first column is used as a key to show the file name. This assumes you don't have a field named "Source" The assumptions: you have a database on each of the activesheets of each of the workbooks, and the data starts in cell A1 and is contiguous: there are no blanks. (If that isn't the case, we can work around that.) The field headers are spelled the same, or else they are treated as different headers. The final assumptions (and these two would require a bigger re-write) is that you will not have more than 254 fields, nor more than 65,535 records. Try it on a few files first to check whether it actually works on your files. It worked on my 3 small test files. When we get this to work, you can send the champagne Fed-Ex.... ;-) HTH, Bernie MS Excel MVP Sub ConsolidateDatabases() Dim filearray As Variant Dim BaseBook As Workbook Dim BaseSheet As Worksheet Dim myBook As Workbook Dim myCell As Range Dim myColumn As Integer Dim myRow As Long Dim myCount As Integer Dim i As Integer With Application .DisplayAlerts = False .EnableEvents = False .ScreenUpdating = False End With filearray = Application.GetOpenFilename(MultiSelect:=True) If IsArray(filearray) Then Set BaseBook = Workbooks.Open(filearray(LBound(filearray))) Set BaseSheet = BaseBook.ActiveSheet Range("A1").EntireColumn.Insert Intersect(BaseSheet.Range("B:B"), _ BaseSheet.UsedRange).Offset(0, -1).Value = BaseBook.Name Range("A1").Value = "Source" For i = LBound(filearray) + 1 To UBound(filearray) myRow = BaseSheet.UsedRange.Rows.Count + 1 Set myBook = Workbooks.Open(filearray(i)) For Each myCell In Intersect(Range("1:1"), _ ActiveSheet.UsedRange) If myCell.Value < "" Then If IsError(Application.Match(myCell.Value, _ BaseSheet.Range("1:1"), False)) Then With BaseSheet.Range("IV1").End(xlToLeft)(1, 2) .Value = myCell.Value myColumn = .Column End With Else myColumn = Application.Match(myCell.Value, _ BaseSheet.Range("1:1"), False) End If myCount = Range(myCell(2), myCell.End(xlDown)).Cells.Count Range(myCell(2), myCell.End(xlDown)).Copy _ BaseSheet.Cells(myRow, myColumn) End If Next myCell BaseSheet.Cells(myRow, 1).Resize(myCount).Value = myBook.Name myBook.Close False Next i End If BaseBook.SaveAs Application.GetSaveAsFilename("Consolidated file.xls") With Application .DisplayAlerts = True .EnableEvents = True .ScreenUpdating = True End With End Sub "Bob Dobalina" wrote in message ... Thanks Bernie, VB is not my specialty - I have some background in fixing existing code and working with this forum to modify code provided by MVPs... I always appreciate the help and only use it in instances where I am profoundly stumped. If anyone is reading this and has any idea how I would implement this I would be very, very appreciative. This is something I need to do one way or the other over the next week and would prefer not to spend 24 hours a day doing it manually. :-P If anyone can give me some clues on the coding of this idea...... I'll buy you some champagne or something. heheh thanks!!!!! - S "Bernie Deitrick" wrote: Bob, The general approach would be to use a macro that loops through the workbooks, reads the column heading and sees if it currently exists in the combined sheet: if it does, it adds the data at the bottom of the existiung column, if it doesn't it adds a new column with a new header and then adds the data into the sames rows as for existing items. The coding shouldn't be too hard - but I have to go right now, so I can't do it for you. But maybe that will give you a clue to get started. HTH, Bernie MS Excel MVP "Bob Dobalina" wrote in message ... Hello group, I have about 300 workbooks with just one sheet each (Sheet1). Each worksheet has about 130 columns and anywhere from 20 to 2000 rows. I need to combine all of them into one data source for analysis. Here's the kicker - About 70 of those columns are "standard" meaning that they have the same column header, etc. The rest are variable. This data was collected over a period of three years and standardizations were not employed very well. So some of those "variable" column headers may overlap or some may not. For instance (using a small subset of total columns as example): Book A has the following column headers (and related info) in columns X,Y,Z respectively: Column X: Status Column Y: /Hotel/Accomodations Column Z: /Credit Card Number Book B has the following column headers (and related info) in columns X,Y,Z respectively: Column X: /Smoking Column Y: Status Column Z: /Badge First Name Is there ANY way to combine all of those spreadsheets into one data source? The logic would probably go something like: If Sheet B were being added to Sheet A, the system would check to see if the column header for Sheet B / Column X existed as a column header in Sheet A. If it did exist, the related row data would then be pasted in that respective column. If it did not, the column header would be created at the top of Sheet A and the related row data would be place under it (but still relative to the row that it is associated with). Does this make sense? I can't for the life of me find any way to do this other than to manually check which columns match and if they don't, to add it as a new column and then move the related row data into that column when combining the sheets. As you can imagine this will take a long time to compile. I can use Access to identify these unmatched columns more effectively, but I have to imagine there is a more elegant solution to this problem. I mean, isn't it a truism that for every problem you encounter there is someone who has encountered and solved the same problem? Are you out there? Thanks again. I will check back periodically as this is a very pressing issue at the moment. Also, I can best be contacted at " thank you, - S |
#7
![]() |
|||
|
|||
![]()
Bob,
It could be that the file is much larger than I expected: I dimensioned myCount as an Integer, and if you have a lot of cells, it should be a Long. Try changing the dimension statement from Dim myCount As Integer to Dim myCount As Long If that doesn't work, send me a sample workbook - take the spaces out and change the dot to . and I will see if there is something about the structure of your data files that I didn't take into account. HTH, Bernie MS Excel MVP "Bob Dobalina" wrote in message ... =Hey Bernie, I am trying to implement this today and am running into a "Run-time error 6"... Then it says simply "overflow". When I debug, I get this line as the culprit: "myCount = Range(myCell(2), myCell.End(xlDown)).Cells.Count" I'm just guessing that you've got a good idea about why this is happening (per your comments in posts) so I will defer to your judgement before going off on a goose chase. The champagne offer still stands... ;-) cheers, - S "Bernie Deitrick" wrote: S, And here I thought you were "Bob"! Try the macro below. Select the files when prompted, using shift and/or ctrl to select multiple files. The data will be arranged as well as possible, with blanks where there isn't any matching data. The first column is used as a key to show the file name. This assumes you don't have a field named "Source" The assumptions: you have a database on each of the activesheets of each of the workbooks, and the data starts in cell A1 and is contiguous: there are no blanks. (If that isn't the case, we can work around that.) The field headers are spelled the same, or else they are treated as different headers. The final assumptions (and these two would require a bigger re-write) is that you will not have more than 254 fields, nor more than 65,535 records. Try it on a few files first to check whether it actually works on your files. It worked on my 3 small test files. When we get this to work, you can send the champagne Fed-Ex.... ;-) HTH, Bernie MS Excel MVP Sub ConsolidateDatabases() Dim filearray As Variant Dim BaseBook As Workbook Dim BaseSheet As Worksheet Dim myBook As Workbook Dim myCell As Range Dim myColumn As Integer Dim myRow As Long Dim myCount As Integer Dim i As Integer With Application .DisplayAlerts = False .EnableEvents = False .ScreenUpdating = False End With filearray = Application.GetOpenFilename(MultiSelect:=True) If IsArray(filearray) Then Set BaseBook = Workbooks.Open(filearray(LBound(filearray))) Set BaseSheet = BaseBook.ActiveSheet Range("A1").EntireColumn.Insert Intersect(BaseSheet.Range("B:B"), _ BaseSheet.UsedRange).Offset(0, -1).Value = BaseBook.Name Range("A1").Value = "Source" For i = LBound(filearray) + 1 To UBound(filearray) myRow = BaseSheet.UsedRange.Rows.Count + 1 Set myBook = Workbooks.Open(filearray(i)) For Each myCell In Intersect(Range("1:1"), _ ActiveSheet.UsedRange) If myCell.Value < "" Then If IsError(Application.Match(myCell.Value, _ BaseSheet.Range("1:1"), False)) Then With BaseSheet.Range("IV1").End(xlToLeft)(1, 2) .Value = myCell.Value myColumn = .Column End With Else myColumn = Application.Match(myCell.Value, _ BaseSheet.Range("1:1"), False) End If myCount = Range(myCell(2), myCell.End(xlDown)).Cells.Count Range(myCell(2), myCell.End(xlDown)).Copy _ BaseSheet.Cells(myRow, myColumn) End If Next myCell BaseSheet.Cells(myRow, 1).Resize(myCount).Value = myBook.Name myBook.Close False Next i End If BaseBook.SaveAs Application.GetSaveAsFilename("Consolidated file.xls") With Application .DisplayAlerts = True .EnableEvents = True .ScreenUpdating = True End With End Sub "Bob Dobalina" wrote in message ... Thanks Bernie, VB is not my specialty - I have some background in fixing existing code and working with this forum to modify code provided by MVPs... I always appreciate the help and only use it in instances where I am profoundly stumped. If anyone is reading this and has any idea how I would implement this I would be very, very appreciative. This is something I need to do one way or the other over the next week and would prefer not to spend 24 hours a day doing it manually. :-P If anyone can give me some clues on the coding of this idea...... I'll buy you some champagne or something. heheh thanks!!!!! - S "Bernie Deitrick" wrote: Bob, The general approach would be to use a macro that loops through the workbooks, reads the column heading and sees if it currently exists in the combined sheet: if it does, it adds the data at the bottom of the existiung column, if it doesn't it adds a new column with a new header and then adds the data into the sames rows as for existing items. The coding shouldn't be too hard - but I have to go right now, so I can't do it for you. But maybe that will give you a clue to get started. HTH, Bernie MS Excel MVP "Bob Dobalina" wrote in message ... Hello group, I have about 300 workbooks with just one sheet each (Sheet1). Each worksheet has about 130 columns and anywhere from 20 to 2000 rows. I need to combine all of them into one data source for analysis. Here's the kicker - About 70 of those columns are "standard" meaning that they have the same column header, etc. The rest are variable. This data was collected over a period of three years and standardizations were not employed very well. So some of those "variable" column headers may overlap or some may not. For instance (using a small subset of total columns as example): Book A has the following column headers (and related info) in columns X,Y,Z respectively: Column X: Status Column Y: /Hotel/Accomodations Column Z: /Credit Card Number Book B has the following column headers (and related info) in columns X,Y,Z respectively: Column X: /Smoking Column Y: Status Column Z: /Badge First Name Is there ANY way to combine all of those spreadsheets into one data source? The logic would probably go something like: If Sheet B were being added to Sheet A, the system would check to see if the column header for Sheet B / Column X existed as a column header in Sheet A. If it did exist, the related row data would then be pasted in that respective column. If it did not, the column header would be created at the top of Sheet A and the related row data would be place under it (but still relative to the row that it is associated with). Does this make sense? I can't for the life of me find any way to do this other than to manually check which columns match and if they don't, to add it as a new column and then move the related row data into that column when combining the sheets. As you can imagine this will take a long time to compile. I can use Access to identify these unmatched columns more effectively, but I have to imagine there is a more elegant solution to this problem. I mean, isn't it a truism that for every problem you encounter there is someone who has encountered and solved the same problem? Are you out there? Thanks again. I will check back periodically as this is a very pressing issue at the moment. Also, I can best be contacted at " thank you, - S |
#8
![]() |
|||
|
|||
![]()
Hey Bernie,
Thanks again. I tried that and now I am getting a different error. It is: Run-time error '1004' The information can not be pasted because the copy area and the paste area are not the same size and shape. I'm not sure what you mean about taking the spaces out asnd replacing the dot with a . If you meant for an email address I am at a loss as to where that would be. :-) Toss me an email at " or post yours here and I'll send you that sample. It does have a lot of field data, so perhaps this needs to be accounted for. I can't even tell you how much I appreciate this! thanks, - S "Bernie Deitrick" wrote: Bob, It could be that the file is much larger than I expected: I dimensioned myCount as an Integer, and if you have a lot of cells, it should be a Long. Try changing the dimension statement from Dim myCount As Integer to Dim myCount As Long If that doesn't work, send me a sample workbook - take the spaces out and change the dot to . and I will see if there is something about the structure of your data files that I didn't take into account. HTH, Bernie MS Excel MVP "Bob Dobalina" wrote in message ... =Hey Bernie, I am trying to implement this today and am running into a "Run-time error 6"... Then it says simply "overflow". When I debug, I get this line as the culprit: "myCount = Range(myCell(2), myCell.End(xlDown)).Cells.Count" I'm just guessing that you've got a good idea about why this is happening (per your comments in posts) so I will defer to your judgement before going off on a goose chase. The champagne offer still stands... ;-) cheers, - S "Bernie Deitrick" wrote: S, And here I thought you were "Bob"! Try the macro below. Select the files when prompted, using shift and/or ctrl to select multiple files. The data will be arranged as well as possible, with blanks where there isn't any matching data. The first column is used as a key to show the file name. This assumes you don't have a field named "Source" The assumptions: you have a database on each of the activesheets of each of the workbooks, and the data starts in cell A1 and is contiguous: there are no blanks. (If that isn't the case, we can work around that.) The field headers are spelled the same, or else they are treated as different headers. The final assumptions (and these two would require a bigger re-write) is that you will not have more than 254 fields, nor more than 65,535 records. Try it on a few files first to check whether it actually works on your files. It worked on my 3 small test files. When we get this to work, you can send the champagne Fed-Ex.... ;-) HTH, Bernie MS Excel MVP Sub ConsolidateDatabases() Dim filearray As Variant Dim BaseBook As Workbook Dim BaseSheet As Worksheet Dim myBook As Workbook Dim myCell As Range Dim myColumn As Integer Dim myRow As Long Dim myCount As Integer Dim i As Integer With Application .DisplayAlerts = False .EnableEvents = False .ScreenUpdating = False End With filearray = Application.GetOpenFilename(MultiSelect:=True) If IsArray(filearray) Then Set BaseBook = Workbooks.Open(filearray(LBound(filearray))) Set BaseSheet = BaseBook.ActiveSheet Range("A1").EntireColumn.Insert Intersect(BaseSheet.Range("B:B"), _ BaseSheet.UsedRange).Offset(0, -1).Value = BaseBook.Name Range("A1").Value = "Source" For i = LBound(filearray) + 1 To UBound(filearray) myRow = BaseSheet.UsedRange.Rows.Count + 1 Set myBook = Workbooks.Open(filearray(i)) For Each myCell In Intersect(Range("1:1"), _ ActiveSheet.UsedRange) If myCell.Value < "" Then If IsError(Application.Match(myCell.Value, _ BaseSheet.Range("1:1"), False)) Then With BaseSheet.Range("IV1").End(xlToLeft)(1, 2) .Value = myCell.Value myColumn = .Column End With Else myColumn = Application.Match(myCell.Value, _ BaseSheet.Range("1:1"), False) End If myCount = Range(myCell(2), myCell.End(xlDown)).Cells.Count Range(myCell(2), myCell.End(xlDown)).Copy _ BaseSheet.Cells(myRow, myColumn) End If Next myCell BaseSheet.Cells(myRow, 1).Resize(myCount).Value = myBook.Name myBook.Close False Next i End If BaseBook.SaveAs Application.GetSaveAsFilename("Consolidated file.xls") With Application .DisplayAlerts = True .EnableEvents = True .ScreenUpdating = True End With End Sub "Bob Dobalina" wrote in message ... Thanks Bernie, VB is not my specialty - I have some background in fixing existing code and working with this forum to modify code provided by MVPs... I always appreciate the help and only use it in instances where I am profoundly stumped. If anyone is reading this and has any idea how I would implement this I would be very, very appreciative. This is something I need to do one way or the other over the next week and would prefer not to spend 24 hours a day doing it manually. :-P If anyone can give me some clues on the coding of this idea...... I'll buy you some champagne or something. heheh thanks!!!!! - S "Bernie Deitrick" wrote: Bob, The general approach would be to use a macro that loops through the workbooks, reads the column heading and sees if it currently exists in the combined sheet: if it does, it adds the data at the bottom of the existiung column, if it doesn't it adds a new column with a new header and then adds the data into the sames rows as for existing items. The coding shouldn't be too hard - but I have to go right now, so I can't do it for you. But maybe that will give you a clue to get started. HTH, Bernie MS Excel MVP "Bob Dobalina" wrote in message ... Hello group, I have about 300 workbooks with just one sheet each (Sheet1). Each worksheet has about 130 columns and anywhere from 20 to 2000 rows. I need to combine all of them into one data source for analysis. Here's the kicker - About 70 of those columns are "standard" meaning that they have the same column header, etc. The rest are variable. This data was collected over a period of three years and standardizations were not employed very well. So some of those "variable" column headers may overlap or some may not. For instance (using a small subset of total columns as example): Book A has the following column headers (and related info) in columns X,Y,Z respectively: Column X: Status Column Y: /Hotel/Accomodations Column Z: /Credit Card Number Book B has the following column headers (and related info) in columns X,Y,Z respectively: Column X: /Smoking Column Y: Status Column Z: /Badge First Name Is there ANY way to combine all of those spreadsheets into one data source? The logic would probably go something like: If Sheet B were being added to Sheet A, the system would check to see if the column header for Sheet B / Column X existed as a column header in Sheet A. If it did exist, the related row data would then be pasted in that respective column. If it did not, the column header would be created at the top of Sheet A and the related row data would be place under it (but still relative to the row that it is associated with). Does this make sense? I can't for the life of me find any way to do this other than to manually check which columns match and if they don't, to add it as a new column and then move the related row data into that column when combining the sheets. As you can imagine this will take a long time to compile. I can use Access to identify these unmatched columns more effectively, but I have to imagine there is a more elegant solution to this problem. I mean, isn't it a truism that for every problem you encounter there is someone who has encountered and solved the same problem? Are you out there? Thanks again. I will check back periodically as this is a very pressing issue at the moment. Also, I can best be contacted at " thank you, - S |
#9
![]() |
|||
|
|||
![]()
Bob,
I sent you my address privately. Bernie "Bob Dobalina" wrote in message ... Hey Bernie, Thanks again. I tried that and now I am getting a different error. It is: Run-time error '1004' The information can not be pasted because the copy area and the paste area are not the same size and shape. I'm not sure what you mean about taking the spaces out asnd replacing the dot with a . If you meant for an email address I am at a loss as to where that would be. :-) Toss me an email at " or post yours here and I'll send you that sample. It does have a lot of field data, so perhaps this needs to be accounted for. I can't even tell you how much I appreciate this! thanks, - S "Bernie Deitrick" wrote: Bob, It could be that the file is much larger than I expected: I dimensioned myCount as an Integer, and if you have a lot of cells, it should be a Long. Try changing the dimension statement from Dim myCount As Integer to Dim myCount As Long If that doesn't work, send me a sample workbook - take the spaces out and change the dot to . and I will see if there is something about the structure of your data files that I didn't take into account. HTH, Bernie MS Excel MVP "Bob Dobalina" wrote in message ... =Hey Bernie, I am trying to implement this today and am running into a "Run-time error 6"... Then it says simply "overflow". When I debug, I get this line as the culprit: "myCount = Range(myCell(2), myCell.End(xlDown)).Cells.Count" I'm just guessing that you've got a good idea about why this is happening (per your comments in posts) so I will defer to your judgement before going off on a goose chase. The champagne offer still stands... ;-) cheers, - S "Bernie Deitrick" wrote: S, And here I thought you were "Bob"! Try the macro below. Select the files when prompted, using shift and/or ctrl to select multiple files. The data will be arranged as well as possible, with blanks where there isn't any matching data. The first column is used as a key to show the file name. This assumes you don't have a field named "Source" The assumptions: you have a database on each of the activesheets of each of the workbooks, and the data starts in cell A1 and is contiguous: there are no blanks. (If that isn't the case, we can work around that.) The field headers are spelled the same, or else they are treated as different headers. The final assumptions (and these two would require a bigger re-write) is that you will not have more than 254 fields, nor more than 65,535 records. Try it on a few files first to check whether it actually works on your files. It worked on my 3 small test files. When we get this to work, you can send the champagne Fed-Ex.... ;-) HTH, Bernie MS Excel MVP Sub ConsolidateDatabases() Dim filearray As Variant Dim BaseBook As Workbook Dim BaseSheet As Worksheet Dim myBook As Workbook Dim myCell As Range Dim myColumn As Integer Dim myRow As Long Dim myCount As Integer Dim i As Integer With Application .DisplayAlerts = False .EnableEvents = False .ScreenUpdating = False End With filearray = Application.GetOpenFilename(MultiSelect:=True) If IsArray(filearray) Then Set BaseBook = Workbooks.Open(filearray(LBound(filearray))) Set BaseSheet = BaseBook.ActiveSheet Range("A1").EntireColumn.Insert Intersect(BaseSheet.Range("B:B"), _ BaseSheet.UsedRange).Offset(0, -1).Value = BaseBook.Name Range("A1").Value = "Source" For i = LBound(filearray) + 1 To UBound(filearray) myRow = BaseSheet.UsedRange.Rows.Count + 1 Set myBook = Workbooks.Open(filearray(i)) For Each myCell In Intersect(Range("1:1"), _ ActiveSheet.UsedRange) If myCell.Value < "" Then If IsError(Application.Match(myCell.Value, _ BaseSheet.Range("1:1"), False)) Then With BaseSheet.Range("IV1").End(xlToLeft)(1, 2) .Value = myCell.Value myColumn = .Column End With Else myColumn = Application.Match(myCell.Value, _ BaseSheet.Range("1:1"), False) End If myCount = Range(myCell(2), myCell.End(xlDown)).Cells.Count Range(myCell(2), myCell.End(xlDown)).Copy _ BaseSheet.Cells(myRow, myColumn) End If Next myCell BaseSheet.Cells(myRow, 1).Resize(myCount).Value = myBook.Name myBook.Close False Next i End If BaseBook.SaveAs Application.GetSaveAsFilename("Consolidated file.xls") With Application .DisplayAlerts = True .EnableEvents = True .ScreenUpdating = True End With End Sub "Bob Dobalina" wrote in message ... Thanks Bernie, VB is not my specialty - I have some background in fixing existing code and working with this forum to modify code provided by MVPs... I always appreciate the help and only use it in instances where I am profoundly stumped. If anyone is reading this and has any idea how I would implement this I would be very, very appreciative. This is something I need to do one way or the other over the next week and would prefer not to spend 24 hours a day doing it manually. :-P If anyone can give me some clues on the coding of this idea...... I'll buy you some champagne or something. heheh thanks!!!!! - S "Bernie Deitrick" wrote: Bob, The general approach would be to use a macro that loops through the workbooks, reads the column heading and sees if it currently exists in the combined sheet: if it does, it adds the data at the bottom of the existiung column, if it doesn't it adds a new column with a new header and then adds the data into the sames rows as for existing items. The coding shouldn't be too hard - but I have to go right now, so I can't do it for you. But maybe that will give you a clue to get started. HTH, Bernie MS Excel MVP "Bob Dobalina" wrote in message ... Hello group, I have about 300 workbooks with just one sheet each (Sheet1). Each worksheet has about 130 columns and anywhere from 20 to 2000 rows. I need to combine all of them into one data source for analysis. Here's the kicker - About 70 of those columns are "standard" meaning that they have the same column header, etc. The rest are variable. This data was collected over a period of three years and standardizations were not employed very well. So some of those "variable" column headers may overlap or some may not. For instance (using a small subset of total columns as example): Book A has the following column headers (and related info) in columns X,Y,Z respectively: Column X: Status Column Y: /Hotel/Accomodations Column Z: /Credit Card Number Book B has the following column headers (and related info) in columns X,Y,Z respectively: Column X: /Smoking Column Y: Status Column Z: /Badge First Name Is there ANY way to combine all of those spreadsheets into one data source? The logic would probably go something like: If Sheet B were being added to Sheet A, the system would check to see if the column header for Sheet B / Column X existed as a column header in Sheet A. If it did exist, the related row data would then be pasted in that respective column. If it did not, the column header would be created at the top of Sheet A and the related row data would be place under it (but still relative to the row that it is associated with). Does this make sense? I can't for the life of me find any way to do this other than to manually check which columns match and if they don't, to add it as a new column and then move the related row data into that column when combining the sheets. As you can imagine this will take a long time to compile. I can use Access to identify these unmatched columns more effectively, but I have to imagine there is a more elegant solution to this problem. I mean, isn't it a truism that for every problem you encounter there is someone who has encountered and solved the same problem? Are you out there? Thanks again. I will check back periodically as this is a very pressing issue at the moment. Also, I can best be contacted at " thank you, - S |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Variable Link to Workbooks | Excel Worksheet Functions | |||
Excel 2003 Referencing multiple workbooks via single variable | Excel Worksheet Functions | |||
are variable table-array names in functions possible? | Excel Discussion (Misc queries) | |||
Pivot table field names | Excel Worksheet Functions | |||
Combining Defined Names to New Name For Validation | Excel Worksheet Functions |