[SOLVED] Align information in multiple sheets

[L Martin]

I have 3 workbooks open and each has dates in column A with other
information in the other columns. The problem is each date is used several
times. I would like to be able to combine the 3 files and have the first
use of each date line up across. Right now, I have all 3 open and arranged
vertically. I am aligning the 2nd with the 3rd file first, then the first
with the 2nd. For instance, A918 in the 2nd is 2004-08-19, I then look over
in the 3rd and see that 2004-08-09 there is in A924, then I go back to the
2nd, highlight the row with A918 and insert 5 rows (one less than the
difference) - which makes the first instance of 2004-08-19 line up between
the 2nd and 3rd files. I'm sure this can be handled with a Macro, but I
don't know how to set it up. There are over 7000 rowss, so it would REALLY
help! Can easily put the 3 files together as different worksheets in one
book if that would help. (Note: Posted this in Excel.programming last week,
but got no resolution.) Would appreciate any help!

LM

[mrice]

Can you attach a sample workbook to help explain the problem?

[L Martin]

Attached is a small 25 line "snippet", which I believe illustrates the
situation. This information was exported from an Oracle data base for me
and the "dates" are actually just numbers (i.e. 20060728, which I custom
formated and placed the "-"s -- they represent dates but were changed to
general format in the transfer). What Id like to do is to align the first
instance of each "date" in each sheet where they will line up across in the
same row. After they are all lined up, I'll cut & paste them all into a new
sheet. Then I plan to sum the total number of each category for each
"date". Thanks for any help!
"mrice" <[email protected]> wrote in
message news:[email protected]...
>
> Can you attach a sample workbook to help explain the problem?
>
>
> --
> mrice
>
> Research Scientist with many years of spreadsheet development experience
> ------------------------------------------------------------------------
> mrice's Profile:
> http://www.excelforum.com/member.php...o&userid=10931
> View this thread: http://www.excelforum.com/showthread...hreadid=565834
>

[mrice]

Sorry - can't see an attachment.

Martin

[L Martin]

I'll try to show what I'm looking for here. (Maximize window)
1st Sheet: EQUIPMENT 2nd: SUPERVISORS 3rd: LABOR
Date Descr. Date Name Date
Category Nbr Hrs
2004-02-20 Truck 2004-02-20 Smith 2004-02-20 TrkDvr
2 8
2004-02-20 BkHoe 2004-02-20
Laborer 4 8
2004-02-20 Dozer
2004-02-20 Loader
2004-02-21 Truck 2004-02-21 Smith 2004-02-21 TrkDvr
3 8
2004-02-21 Bkhoe 2004-02-21
Laborer 6 8
2004-02-21 Dozer
2004-02-22 Truck 2004-02-22 Smith 2004-02-21 Trk Dvr
4 8
2004-02-22 BkHoe 2004-02-21
Laborer 5 8
2004-02-22 Dozer 2004-02-21
Operator 2 8
2004-02-22 TrkHoe
2004-02-22 JkHmr

This is the way I'd like them to display - the first instance of each date
to line up across. The 3 files or sheets have different numbers of entries
for each date and when displayed side by side do not line up. Thanks for
any help.

"mrice" <[email protected]> wrote in
message news:[email protected]...
>
> Sorry - can't see an attachment.
>
> Martin
>
>
> --
> mrice
>
> Research Scientist with many years of spreadsheet development experience
> ------------------------------------------------------------------------
> mrice's Profile:
> http://www.excelforum.com/member.php...o&userid=10931
> View this thread: http://www.excelforum.com/showthread...hreadid=565834
>

[mrice]

Sorry for the delay in replying.

Assuming that you have three sheets in the same workbook and there is a header row, the following macro should work.



Sub Align()
'Generate an array of dates with number of lines for each sheet
Dim DataArray()
ReDim DataArray(10, 0)
For Each Sheet In Sheets
For N = 2 To Sheet.Cells(65536, 1).End(xlUp).Row
If Sheet.Cells(N, 1) <> "" Then
NewDate = True
For M = 1 To UBound(DataArray, 2)
If DataArray(1, M) = Sheet.Cells(N, 1) Then
NewDate = False
Exit For
End If
Next M
If NewDate = True Then
ReDim Preserve DataArray(10, UBound(DataArray, 2) + 1)
TargetArrayRow = UBound(DataArray, 2)
Else
TargetArrayRow = M
End If
If DataArray(1, TargetArrayRow) = "" Then DataArray(1, TargetArrayRow) = Sheet.Cells(N, 1)
Select Case Sheet.Name
Case Is = "EQUIPMENT"
DataArray(2, TargetArrayRow) = DataArray(2, TargetArrayRow) + 1
If DataArray(5, TargetArrayRow) = "" Then DataArray(5, TargetArrayRow) = N
Case Is = "SUPERVISORS"
DataArray(3, TargetArrayRow) = DataArray(3, TargetArrayRow) + 1
If DataArray(6, TargetArrayRow) = "" Then DataArray(6, TargetArrayRow) = N
Case Is = "LABOR"
DataArray(4, TargetArrayRow) = DataArray(4, TargetArrayRow) + 1
If DataArray(7, TargetArrayRow) = "" Then DataArray(7, TargetArrayRow) = N
End Select
End If
Next N
Next Sheet

For N = 1 To UBound(DataArray, 2)
DataArray(8, N) = Application.Max(DataArray(5, N), DataArray(6, N), DataArray(7, N))
DataArray(9, N) = Application.Max(DataArray(2, N), DataArray(3, N), DataArray(4, N))
If N > 1 Then
DataArray(10, N) = DataArray(10, N - 1) + DataArray(9, N)
Else
DataArray(10, N) = DataArray(9, N) + 1
End If
Next N

For N = UBound(DataArray, 2) To 1 Step -1
For Each Sheet In Sheets
Select Case Sheet.Name
Case Is = "EQUIPMENT"
Sheet.Rows(DataArray(5, N) & ":" & DataArray(5, N) + DataArray(2, N) - 1).Cut Destination:=Sheet.Rows(DataArray(10, N) + 1 & ":" & DataArray(10, N) + DataArray(2, N))
Case Is = "SUPERVISORS"
Sheet.Rows(DataArray(6, N) & ":" & DataArray(6, N) + DataArray(3, N) - 1).Cut Destination:=Sheet.Rows(DataArray(10, N) + 1 & ":" & DataArray(10, N) + DataArray(3, N))
Case Is = "LABOR"
Sheet.Rows(DataArray(7, N) & ":" & DataArray(7, N) + DataArray(4, N) - 1).Cut Destination:=Sheet.Rows(DataArray(10, N) + 1 & ":" & DataArray(10, N) + DataArray(4, N))
End Select
Next Sheet
Next N
End Sub

[L Martin]

I appreciate your efforts to help. I put the macro in my spreadsheet and
ran it. After fixing some punctuation errors caused by Notepad when I
copied and pasted the data and adding End Case and Select Case Sheet.Name
before each sheet's name to get rid of "compile errors", it looked like it
was going to work. However, after about 6 minutes, another error message
popped up: Run-time error '13': Type Mismatch -- when I went into Debug,
this line was highlighted: Sheet.Rows (DataArray (5,N) & ":" & DataArray
(5,N) + DataArray (2,N) -1).Cut Destination:=Sheet.Rows (DataArray(10,N) + 1
& ":" & DataArray (10,N) + Data Array (2,N)). I forgot to mention that I'm
using Excel 2000, if that makes any difference. Thanks again for your help.
"mrice" <[email protected]> wrote in message
news:[email protected]...
>
> Sorry for the delay in replying.
>
> Assuming that you have three sheets in the same workbook and there is a
> header row, the following macro should work.
>
>
>
> Sub Align()
> 'Generate an array of dates with number of lines for each sheet
> Dim DataArray()
> ReDim DataArray(10, 0)
> For Each Sheet In Sheets
> For N = 2 To Sheet.Cells(65536, 1).End(xlUp).Row
> If Sheet.Cells(N, 1) <> "" Then
> NewDate = True
> For M = 1 To UBound(DataArray, 2)
> If DataArray(1, M) = Sheet.Cells(N, 1) Then
> NewDate = False
> Exit For
> End If
> Next M
> If NewDate = True Then
> ReDim Preserve DataArray(10, UBound(DataArray, 2) + 1)
> TargetArrayRow = UBound(DataArray, 2)
> Else
> TargetArrayRow = M
> End If
> If DataArray(1, TargetArrayRow) = "" Then DataArray(1,
> TargetArrayRow) = Sheet.Cells(N, 1)
> Select Case Sheet.Name
> Case Is = "EQUIPMENT"
> DataArray(2, TargetArrayRow) = DataArray(2,
> TargetArrayRow) + 1
> If DataArray(5, TargetArrayRow) = "" Then
> DataArray(5, TargetArrayRow) = N
> Case Is = "SUPERVISORS"
> DataArray(3, TargetArrayRow) = DataArray(3,
> TargetArrayRow) + 1
> If DataArray(6, TargetArrayRow) = "" Then
> DataArray(6, TargetArrayRow) = N
> Case Is = "LABOR"
> DataArray(4, TargetArrayRow) = DataArray(4,
> TargetArrayRow) + 1
> If DataArray(7, TargetArrayRow) = "" Then
> DataArray(7, TargetArrayRow) = N
> End Select
> End If
> Next N
> Next Sheet
>
> For N = 1 To UBound(DataArray, 2)
> DataArray(8, N) = Application.Max(DataArray(5, N), DataArray(6, N),
> DataArray(7, N))
> DataArray(9, N) = Application.Max(DataArray(2, N), DataArray(3, N),
> DataArray(4, N))
> If N > 1 Then
> DataArray(10, N) = DataArray(10, N - 1) + DataArray(9, N)
> Else
> DataArray(10, N) = DataArray(9, N) + 1
> End If
> Next N
>
> For N = UBound(DataArray, 2) To 1 Step -1
> For Each Sheet In Sheets
> Select Case Sheet.Name
> Case Is = "EQUIPMENT"
> Sheet.Rows(DataArray(5, N) & ":" & DataArray(5, N) +
> DataArray(2, N) - 1).Cut Destination:=Sheet.Rows(DataArray(10, N) + 1 &
> ":" & DataArray(10, N) + DataArray(2, N))
> Case Is = "SUPERVISORS"
> Sheet.Rows(DataArray(6, N) & ":" & DataArray(6, N) +
> DataArray(3, N) - 1).Cut Destination:=Sheet.Rows(DataArray(10, N) + 1 &
> ":" & DataArray(10, N) + DataArray(3, N))
> Case Is = "LABOR"
> Sheet.Rows(DataArray(7, N) & ":" & DataArray(7, N) +
> DataArray(4, N) - 1).Cut Destination:=Sheet.Rows(DataArray(10, N) + 1 &
> ":" & DataArray(10, N) + DataArray(4, N))
> End Select
> Next Sheet
> Next N
> End Sub
>
>
> --
> mrice
>
> Research Scientist with many years of spreadsheet development experience
> ------------------------------------------------------------------------
> mrice's Profile:
> http://www.excelforum.com/member.php...o&userid=10931
> View this thread: http://www.excelforum.com/showthread...hreadid=565834
>

[mrice]

Hmmmm....

It looks like the error is due to something in the data and its not really possible to diagnose exactly what the problem is without seeing it.

If you could attach the workbook or drop a copy to the e-mail on my homepage, I might be able to help.