B
Bob Dobalina
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
"[email protected]"
thank you,
- S
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
"[email protected]"
thank you,
- S
