Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Bob Dobalina
 
Posts: n/a
Default Combining workbooks with some variable field names

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   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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   Report Post  
Bob Dobalina
 
Posts: n/a
Default

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   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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   Report Post  
Bob Dobalina
 
Posts: n/a
Default

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   Report Post  
Bob Dobalina
 
Posts: n/a
Default

=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   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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   Report Post  
Bob Dobalina
 
Posts: n/a
Default

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   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Variable Link to Workbooks dfrancefort Excel Worksheet Functions 1 April 28th 05 01:15 AM
Excel 2003 Referencing multiple workbooks via single variable BBohannon Excel Worksheet Functions 0 April 20th 05 08:32 PM
are variable table-array names in functions possible? JimH Excel Discussion (Misc queries) 2 April 7th 05 09:51 PM
Pivot table field names somewhat confused Excel Worksheet Functions 1 December 14th 04 04:15 AM
Combining Defined Names to New Name For Validation TheSpankster22 Excel Worksheet Functions 0 November 4th 04 02:28 AM


All times are GMT +1. The time now is 07:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"