I have a dataset of unlimited rows and columns as such:

Tool Lot ALM1 ALM2 ALM3 ... ALM50
A1 ER123456 Err-2341 Warn-4511 Err-998 ... Err-234
A2 ER987654 Err-423 Err-421 Warn-982
A3 ED90800 ...
....

Not all tool/lot cominations have the same number of alarms, some will have
only 1 and others will have 50. Blanks in the ALMx columns can be ignored as
this means there were no more alarms.

I would like to maintain the Tool and Lot values while stacking the ALM
codes for each tool/lot comination as such:

Tool Lot ALM
A1 ER123456 Err-2341 (which is ALM1)
A1 ER123456 Warn-4511(which is ALM2)
A1 ER123456 Err-998 (which is ALM3)
....
A1 ER123456 Err-234 (which is ALM50)
A2 ER987654 Err-423 (whcih is ALM1)
A2 ER987654 Err-421 (which is ALM2)
....

I have found some macros to stack rows into columns, but I think it needs
additional steps to increment down row until hits a blank cell and also
copy/paste the tool and lot IDs in first two columns for each subsequent ALM
code.

Thanks for any help anyone can provide.

First, I wouldn't include any blank rows in the output. It really messes up
other stuff--filters, charts, pivottables...

And personally, I would want to see the alarm header on each row: Alm45 on the
row that contained the 45 alarm text. And I would also keep the empty cells.
I'd use data|filter|autofilter if I didn't want to see them anymore.

But I included code at the bottom to delete the rows with empty alarms and
delete the header column if you don't want it. Just uncomment that portion and
see if it works:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Dim HdrRange As Range
Dim AlarmCount As Long

Dim DestCell As Range

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add
With NewWks
.Range("a1").Resize(1, 4).Value _
= Array("ID", "Lot", "Alarm Number", "Alarm Text")
Set DestCell = .Range("a2")
End With

With CurWks
FirstRow = 2 'headers must be in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'headers are in C1:x1
Set HdrRange = .Range("C1", .Cells(1, .Columns.Count).End(xlToLeft))
AlarmCount = HdrRange.Cells.Count

For iRow = FirstRow To LastRow
'make it look double spaced
DestCell.EntireRow.RowHeight = DestCell.EntireRow.RowHeight * 2
'Id column
DestCell.Resize(AlarmCount, 1).Value = .Cells(iRow, "A").Value
'Lot column
DestCell.Offset(0, 1).Resize(AlarmCount, 1).Value _
= .Cells(iRow, "B").Value
'Copy of all the alarm headers
DestCell.Offset(0, 2).Resize(AlarmCount, 1).Value _
= Application.Transpose(HdrRange)
'copy of the alarm text
DestCell.Offset(0, 3).Resize(AlarmCount, 1).Value _
= Application.Transpose(.Cells(iRow, "C").Resize(1, AlarmCount))
'get ready for next row
Set DestCell = DestCell.Offset(AlarmCount, 0)
Next iRow
End With

'clean up the output (I wouldn't do this!)
' With NewWks
' On Error Resume Next
' .Range("d1").EntireColumn.Cells.SpecialCells(xlCel lTypeBlanks) _
' .EntireRow.Delete
' On Error GoTo 0
' .Range("C1").EntireColumn.Delete
' End With

End Sub

rc wrote:

I have a dataset of unlimited rows and columns as such:

Tool Lot ALM1 ALM2 ALM3 ... ALM50
A1 ER123456 Err-2341 Warn-4511 Err-998 ... Err-234
A2 ER987654 Err-423 Err-421 Warn-982
A3 ED90800 ...
...

Not all tool/lot cominations have the same number of alarms, some will have
only 1 and others will have 50. Blanks in the ALMx columns can be ignored as
this means there were no more alarms.

I would like to maintain the Tool and Lot values while stacking the ALM
codes for each tool/lot comination as such:

Tool Lot ALM
A1 ER123456 Err-2341 (which is ALM1)
A1 ER123456 Warn-4511(which is ALM2)
A1 ER123456 Err-998 (which is ALM3)
...
A1 ER123456 Err-234 (which is ALM50)
A2 ER987654 Err-423 (whcih is ALM1)
A2 ER987654 Err-421 (which is ALM2)
...

I have found some macros to stack rows into columns, but I think it needs
additional steps to increment down row until hits a blank cell and also
copy/paste the tool and lot IDs in first two columns for each subsequent ALM
code.

Thanks for any help anyone can provide.

--

Dave Peterson

Thanks a bunch Dave. I have used your code as a template to make some
modifications to remove the alarm header in each row and to add the alarm
file text to each row.

I think the only issue I have to resolve is not including rows with no
alarms. Since I will likely be utilizing more than all 65k lines, I would
like to only copy the cells with alarm text. Rather than cleaning up at the
end, I prefer not to copy them at all. I suspect an IF statement is needed
somewhere, just not sure where to put it.

Thanks again for any help you can offer. I'm new to macros and visual basic
and appreciate any learning I can get.

Thanks,
rc

"Dave Peterson" wrote:

First, I wouldn't include any blank rows in the output. It really messes up
other stuff--filters, charts, pivottables...

And personally, I would want to see the alarm header on each row: Alm45 on the
row that contained the 45 alarm text. And I would also keep the empty cells.
I'd use data|filter|autofilter if I didn't want to see them anymore.

But I included code at the bottom to delete the rows with empty alarms and
delete the header column if you don't want it. Just uncomment that portion and
see if it works:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Dim HdrRange As Range
Dim AlarmCount As Long

Dim DestCell As Range

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add
With NewWks
.Range("a1").Resize(1, 4).Value _
= Array("ID", "Lot", "Alarm Number", "Alarm Text")
Set DestCell = .Range("a2")
End With

With CurWks
FirstRow = 2 'headers must be in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'headers are in C1:x1
Set HdrRange = .Range("C1", .Cells(1, .Columns.Count).End(xlToLeft))
AlarmCount = HdrRange.Cells.Count

For iRow = FirstRow To LastRow
'make it look double spaced
DestCell.EntireRow.RowHeight = DestCell.EntireRow.RowHeight * 2
'Id column
DestCell.Resize(AlarmCount, 1).Value = .Cells(iRow, "A").Value
'Lot column
DestCell.Offset(0, 1).Resize(AlarmCount, 1).Value _
= .Cells(iRow, "B").Value
'Copy of all the alarm headers
DestCell.Offset(0, 2).Resize(AlarmCount, 1).Value _
= Application.Transpose(HdrRange)
'copy of the alarm text
DestCell.Offset(0, 3).Resize(AlarmCount, 1).Value _
= Application.Transpose(.Cells(iRow, "C").Resize(1, AlarmCount))
'get ready for next row
Set DestCell = DestCell.Offset(AlarmCount, 0)
Next iRow
End With

'clean up the output (I wouldn't do this!)
' With NewWks
' On Error Resume Next
' .Range("d1").EntireColumn.Cells.SpecialCells(xlCel lTypeBlanks) _
' .EntireRow.Delete
' On Error GoTo 0
' .Range("C1").EntireColumn.Delete
' End With

End Sub

rc wrote:

I have a dataset of unlimited rows and columns as such:

Tool Lot ALM1 ALM2 ALM3 ... ALM50
A1 ER123456 Err-2341 Warn-4511 Err-998 ... Err-234
A2 ER987654 Err-423 Err-421 Warn-982
A3 ED90800 ...
...

Not all tool/lot cominations have the same number of alarms, some will have
only 1 and others will have 50. Blanks in the ALMx columns can be ignored as
this means there were no more alarms.

I would like to maintain the Tool and Lot values while stacking the ALM
codes for each tool/lot comination as such:

Tool Lot ALM
A1 ER123456 Err-2341 (which is ALM1)
A1 ER123456 Warn-4511(which is ALM2)
A1 ER123456 Err-998 (which is ALM3)
...
A1 ER123456 Err-234 (which is ALM50)
A2 ER987654 Err-423 (whcih is ALM1)
A2 ER987654 Err-421 (which is ALM2)
...

I have found some macros to stack rows into columns, but I think it needs
additional steps to increment down row until hits a blank cell and also
copy/paste the tool and lot IDs in first two columns for each subsequent ALM
code.

Thanks for any help anyone can provide.

--

Dave Peterson

You could do something like:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Dim HdrRange As Range
Dim AlarmCount As Long

Dim DestCell As Range

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add
With NewWks
.Range("a1").Resize(1, 4).Value _
= Array("ID", "Lot", "Alarm Number", "Alarm Text")
Set DestCell = .Range("a2")
End With

With CurWks
FirstRow = 2 'headers must be in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'headers are in C1:x1
Set HdrRange = .Range("C1", .Cells(1, .Columns.Count).End(xlToLeft))
AlarmCount = HdrRange.Cells.Count

For iRow = FirstRow To LastRow
If Application.CountA _
(.Cells(iRow, "C").Resize(1, AlarmCount)) = 0 Then
'skip it
Else
'make it look double spaced
DestCell.EntireRow.RowHeight = DestCell.EntireRow.RowHeight * 2
'Id column
DestCell.Resize(AlarmCount, 1).Value = .Cells(iRow, "A").Value
'Lot column
DestCell.Offset(0, 1).Resize(AlarmCount, 1).Value _
= .Cells(iRow, "B").Value
'Copy of all the alarm headers
DestCell.Offset(0, 2).Resize(AlarmCount, 1).Value _
= Application.Transpose(HdrRange)
'copy of the alarm text
DestCell.Offset(0, 3).Resize(AlarmCount, 1).Value _
= Application.Transpose(.Cells(iRow, "C") _
.Resize(1, AlarmCount))
'get ready for next row

With NewWks
On Error Resume Next
.Range("D1").EntireColumn.Cells _
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error Resume Next

Set DestCell _
= .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
End If
Next iRow
End With

'clean up the output
'NewWks.Range("C1").EntireColumn.Delete

End Sub


rc wrote:

Thanks a bunch Dave. I have used your code as a template to make some
modifications to remove the alarm header in each row and to add the alarm
file text to each row.

I think the only issue I have to resolve is not including rows with no
alarms. Since I will likely be utilizing more than all 65k lines, I would
like to only copy the cells with alarm text. Rather than cleaning up at the
end, I prefer not to copy them at all. I suspect an IF statement is needed
somewhere, just not sure where to put it.

Thanks again for any help you can offer. I'm new to macros and visual basic
and appreciate any learning I can get.

Thanks,
rc

"Dave Peterson" wrote:

First, I wouldn't include any blank rows in the output. It really messes up
other stuff--filters, charts, pivottables...

And personally, I would want to see the alarm header on each row: Alm45 on the
row that contained the 45 alarm text. And I would also keep the empty cells.
I'd use data|filter|autofilter if I didn't want to see them anymore.

But I included code at the bottom to delete the rows with empty alarms and
delete the header column if you don't want it. Just uncomment that portion and
see if it works:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Dim HdrRange As Range
Dim AlarmCount As Long

Dim DestCell As Range

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add
With NewWks
.Range("a1").Resize(1, 4).Value _
= Array("ID", "Lot", "Alarm Number", "Alarm Text")
Set DestCell = .Range("a2")
End With

With CurWks
FirstRow = 2 'headers must be in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'headers are in C1:x1
Set HdrRange = .Range("C1", .Cells(1, .Columns.Count).End(xlToLeft))
AlarmCount = HdrRange.Cells.Count

For iRow = FirstRow To LastRow
'make it look double spaced
DestCell.EntireRow.RowHeight = DestCell.EntireRow.RowHeight * 2
'Id column
DestCell.Resize(AlarmCount, 1).Value = .Cells(iRow, "A").Value
'Lot column
DestCell.Offset(0, 1).Resize(AlarmCount, 1).Value _
= .Cells(iRow, "B").Value
'Copy of all the alarm headers
DestCell.Offset(0, 2).Resize(AlarmCount, 1).Value _
= Application.Transpose(HdrRange)
'copy of the alarm text
DestCell.Offset(0, 3).Resize(AlarmCount, 1).Value _
= Application.Transpose(.Cells(iRow, "C").Resize(1, AlarmCount))
'get ready for next row
Set DestCell = DestCell.Offset(AlarmCount, 0)
Next iRow
End With

'clean up the output (I wouldn't do this!)
' With NewWks
' On Error Resume Next
' .Range("d1").EntireColumn.Cells.SpecialCells(xlCel lTypeBlanks) _
' .EntireRow.Delete
' On Error GoTo 0
' .Range("C1").EntireColumn.Delete
' End With

End Sub

rc wrote:

I have a dataset of unlimited rows and columns as such:

Tool Lot ALM1 ALM2 ALM3 ... ALM50
A1 ER123456 Err-2341 Warn-4511 Err-998 ... Err-234
A2 ER987654 Err-423 Err-421 Warn-982
A3 ED90800 ...
...

Not all tool/lot cominations have the same number of alarms, some will have
only 1 and others will have 50. Blanks in the ALMx columns can be ignored as
this means there were no more alarms.

I would like to maintain the Tool and Lot values while stacking the ALM
codes for each tool/lot comination as such:

Tool Lot ALM
A1 ER123456 Err-2341 (which is ALM1)
A1 ER123456 Warn-4511(which is ALM2)
A1 ER123456 Err-998 (which is ALM3)
...
A1 ER123456 Err-234 (which is ALM50)
A2 ER987654 Err-423 (whcih is ALM1)
A2 ER987654 Err-421 (which is ALM2)
...

I have found some macros to stack rows into columns, but I think it needs
additional steps to increment down row until hits a blank cell and also
copy/paste the tool and lot IDs in first two columns for each subsequent ALM
code.

Thanks for any help anyone can provide.

--

Dave Peterson


--

Dave Peterson

I'm still getting the same output with tool and lot entries for the remainder
of the 50 alarm header that do not contain alarms. I think it's because we
are expecting 50 alarms for every lot. Perhaps I'm not describing my data
set very well...

For each tool there will be multiple lots, and for each lot there could be
anywhere from 1 to 50 alarms. There are 50 alarms columns (headers) in the
data set, but very often there are many cells left blank. For example, if
only 15 alarms for a lot, then alarm columns 16-50 will be blank.

It looks like this code loops to 50 (AlarmCount is based on alarm headers)
every time copying cell contents to new rows. Could we instead copy each
alarm cell in iRow until we hit a blank cell, then Next iRow to move down to
the next row (lot) in the data set and start copying again? Does this make
sense?

Sorry to keep bugging you with this...

Thanks again,
rc


"Dave Peterson" wrote:

You could do something like:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Dim HdrRange As Range
Dim AlarmCount As Long

Dim DestCell As Range

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add
With NewWks
.Range("a1").Resize(1, 4).Value _
= Array("ID", "Lot", "Alarm Number", "Alarm Text")
Set DestCell = .Range("a2")
End With

With CurWks
FirstRow = 2 'headers must be in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'headers are in C1:x1
Set HdrRange = .Range("C1", .Cells(1, .Columns.Count).End(xlToLeft))
AlarmCount = HdrRange.Cells.Count

For iRow = FirstRow To LastRow
If Application.CountA _
(.Cells(iRow, "C").Resize(1, AlarmCount)) = 0 Then
'skip it
Else
'make it look double spaced
DestCell.EntireRow.RowHeight = DestCell.EntireRow.RowHeight * 2
'Id column
DestCell.Resize(AlarmCount, 1).Value = .Cells(iRow, "A").Value
'Lot column
DestCell.Offset(0, 1).Resize(AlarmCount, 1).Value _
= .Cells(iRow, "B").Value
'Copy of all the alarm headers
DestCell.Offset(0, 2).Resize(AlarmCount, 1).Value _
= Application.Transpose(HdrRange)
'copy of the alarm text
DestCell.Offset(0, 3).Resize(AlarmCount, 1).Value _
= Application.Transpose(.Cells(iRow, "C") _
.Resize(1, AlarmCount))
'get ready for next row

With NewWks
On Error Resume Next
.Range("D1").EntireColumn.Cells _
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error Resume Next

Set DestCell _
= .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
End If
Next iRow
End With

'clean up the output
'NewWks.Range("C1").EntireColumn.Delete

End Sub


rc wrote:

Thanks a bunch Dave. I have used your code as a template to make some
modifications to remove the alarm header in each row and to add the alarm
file text to each row.

I think the only issue I have to resolve is not including rows with no
alarms. Since I will likely be utilizing more than all 65k lines, I would
like to only copy the cells with alarm text. Rather than cleaning up at the
end, I prefer not to copy them at all. I suspect an IF statement is needed
somewhere, just not sure where to put it.

Thanks again for any help you can offer. I'm new to macros and visual basic
and appreciate any learning I can get.

Thanks,
rc

"Dave Peterson" wrote:

First, I wouldn't include any blank rows in the output. It really messes up
other stuff--filters, charts, pivottables...

And personally, I would want to see the alarm header on each row: Alm45 on the
row that contained the 45 alarm text. And I would also keep the empty cells.
I'd use data|filter|autofilter if I didn't want to see them anymore.

But I included code at the bottom to delete the rows with empty alarms and
delete the header column if you don't want it. Just uncomment that portion and
see if it works:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Dim HdrRange As Range
Dim AlarmCount As Long

Dim DestCell As Range

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add
With NewWks
.Range("a1").Resize(1, 4).Value _
= Array("ID", "Lot", "Alarm Number", "Alarm Text")
Set DestCell = .Range("a2")
End With

With CurWks
FirstRow = 2 'headers must be in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'headers are in C1:x1
Set HdrRange = .Range("C1", .Cells(1, .Columns.Count).End(xlToLeft))
AlarmCount = HdrRange.Cells.Count

For iRow = FirstRow To LastRow
'make it look double spaced
DestCell.EntireRow.RowHeight = DestCell.EntireRow.RowHeight * 2
'Id column
DestCell.Resize(AlarmCount, 1).Value = .Cells(iRow, "A").Value
'Lot column
DestCell.Offset(0, 1).Resize(AlarmCount, 1).Value _
= .Cells(iRow, "B").Value
'Copy of all the alarm headers
DestCell.Offset(0, 2).Resize(AlarmCount, 1).Value _
= Application.Transpose(HdrRange)
'copy of the alarm text
DestCell.Offset(0, 3).Resize(AlarmCount, 1).Value _
= Application.Transpose(.Cells(iRow, "C").Resize(1, AlarmCount))
'get ready for next row
Set DestCell = DestCell.Offset(AlarmCount, 0)
Next iRow
End With

'clean up the output (I wouldn't do this!)
' With NewWks
' On Error Resume Next
' .Range("d1").EntireColumn.Cells.SpecialCells(xlCel lTypeBlanks) _
' .EntireRow.Delete
' On Error GoTo 0
' .Range("C1").EntireColumn.Delete
' End With

End Sub

rc wrote:

I have a dataset of unlimited rows and columns as such:

Tool Lot ALM1 ALM2 ALM3 ... ALM50
A1 ER123456 Err-2341 Warn-4511 Err-998 ... Err-234
A2 ER987654 Err-423 Err-421 Warn-982
A3 ED90800 ...
...

Not all tool/lot cominations have the same number of alarms, some will have
only 1 and others will have 50. Blanks in the ALMx columns can be ignored as
this means there were no more alarms.

I would like to maintain the Tool and Lot values while stacking the ALM
codes for each tool/lot comination as such:

Tool Lot ALM
A1 ER123456 Err-2341 (which is ALM1)
A1 ER123456 Warn-4511(which is ALM2)
A1 ER123456 Err-998 (which is ALM3)
...
A1 ER123456 Err-234 (which is ALM50)
A2 ER987654 Err-423 (whcih is ALM1)
A2 ER987654 Err-421 (which is ALM2)
...

I have found some macros to stack rows into columns, but I think it needs
additional steps to increment down row until hits a blank cell and also
copy/paste the tool and lot IDs in first two columns for each subsequent ALM
code.

Thanks for any help anyone can provide.

--

Dave Peterson


--

Dave Peterson

The code deletes cells that are empty.

Are you sure that those cells that look empty are really empty?

Pick out a row that has cells that aren't being deleted.

Then use a formula to count the number of cells that have something in it, like:

=counta(c3:iv3)

Do you get what you expect or is it a lot larger than you expect?



rc wrote:

I'm still getting the same output with tool and lot entries for the remainder
of the 50 alarm header that do not contain alarms. I think it's because we
are expecting 50 alarms for every lot. Perhaps I'm not describing my data
set very well...

For each tool there will be multiple lots, and for each lot there could be
anywhere from 1 to 50 alarms. There are 50 alarms columns (headers) in the
data set, but very often there are many cells left blank. For example, if
only 15 alarms for a lot, then alarm columns 16-50 will be blank.

It looks like this code loops to 50 (AlarmCount is based on alarm headers)
every time copying cell contents to new rows. Could we instead copy each
alarm cell in iRow until we hit a blank cell, then Next iRow to move down to
the next row (lot) in the data set and start copying again? Does this make
sense?

Sorry to keep bugging you with this...

Thanks again,
rc

"Dave Peterson" wrote:

You could do something like:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Dim HdrRange As Range
Dim AlarmCount As Long

Dim DestCell As Range

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add
With NewWks
.Range("a1").Resize(1, 4).Value _
= Array("ID", "Lot", "Alarm Number", "Alarm Text")
Set DestCell = .Range("a2")
End With

With CurWks
FirstRow = 2 'headers must be in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'headers are in C1:x1
Set HdrRange = .Range("C1", .Cells(1, .Columns.Count).End(xlToLeft))
AlarmCount = HdrRange.Cells.Count

For iRow = FirstRow To LastRow
If Application.CountA _
(.Cells(iRow, "C").Resize(1, AlarmCount)) = 0 Then
'skip it
Else
'make it look double spaced
DestCell.EntireRow.RowHeight = DestCell.EntireRow.RowHeight * 2
'Id column
DestCell.Resize(AlarmCount, 1).Value = .Cells(iRow, "A").Value
'Lot column
DestCell.Offset(0, 1).Resize(AlarmCount, 1).Value _
= .Cells(iRow, "B").Value
'Copy of all the alarm headers
DestCell.Offset(0, 2).Resize(AlarmCount, 1).Value _
= Application.Transpose(HdrRange)
'copy of the alarm text
DestCell.Offset(0, 3).Resize(AlarmCount, 1).Value _
= Application.Transpose(.Cells(iRow, "C") _
.Resize(1, AlarmCount))
'get ready for next row

With NewWks
On Error Resume Next
.Range("D1").EntireColumn.Cells _
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error Resume Next

Set DestCell _
= .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
End If
Next iRow
End With

'clean up the output
'NewWks.Range("C1").EntireColumn.Delete

End Sub


rc wrote:

Thanks a bunch Dave. I have used your code as a template to make some
modifications to remove the alarm header in each row and to add the alarm
file text to each row.

I think the only issue I have to resolve is not including rows with no
alarms. Since I will likely be utilizing more than all 65k lines, I would
like to only copy the cells with alarm text. Rather than cleaning up at the
end, I prefer not to copy them at all. I suspect an IF statement is needed
somewhere, just not sure where to put it.

Thanks again for any help you can offer. I'm new to macros and visual basic
and appreciate any learning I can get.

Thanks,
rc

"Dave Peterson" wrote:

First, I wouldn't include any blank rows in the output. It really messes up
other stuff--filters, charts, pivottables...

And personally, I would want to see the alarm header on each row: Alm45 on the
row that contained the 45 alarm text. And I would also keep the empty cells.
I'd use data|filter|autofilter if I didn't want to see them anymore.

But I included code at the bottom to delete the rows with empty alarms and
delete the header column if you don't want it. Just uncomment that portion and
see if it works:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Dim HdrRange As Range
Dim AlarmCount As Long

Dim DestCell As Range

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add
With NewWks
.Range("a1").Resize(1, 4).Value _
= Array("ID", "Lot", "Alarm Number", "Alarm Text")
Set DestCell = .Range("a2")
End With

With CurWks
FirstRow = 2 'headers must be in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'headers are in C1:x1
Set HdrRange = .Range("C1", .Cells(1, .Columns.Count).End(xlToLeft))
AlarmCount = HdrRange.Cells.Count

For iRow = FirstRow To LastRow
'make it look double spaced
DestCell.EntireRow.RowHeight = DestCell.EntireRow.RowHeight * 2
'Id column
DestCell.Resize(AlarmCount, 1).Value = .Cells(iRow, "A").Value
'Lot column
DestCell.Offset(0, 1).Resize(AlarmCount, 1).Value _
= .Cells(iRow, "B").Value
'Copy of all the alarm headers
DestCell.Offset(0, 2).Resize(AlarmCount, 1).Value _
= Application.Transpose(HdrRange)
'copy of the alarm text
DestCell.Offset(0, 3).Resize(AlarmCount, 1).Value _
= Application.Transpose(.Cells(iRow, "C").Resize(1, AlarmCount))
'get ready for next row
Set DestCell = DestCell.Offset(AlarmCount, 0)
Next iRow
End With

'clean up the output (I wouldn't do this!)
' With NewWks
' On Error Resume Next
' .Range("d1").EntireColumn.Cells.SpecialCells(xlCel lTypeBlanks) _
' .EntireRow.Delete
' On Error GoTo 0
' .Range("C1").EntireColumn.Delete
' End With

End Sub

rc wrote:

I have a dataset of unlimited rows and columns as such:

Tool Lot ALM1 ALM2 ALM3 ... ALM50
A1 ER123456 Err-2341 Warn-4511 Err-998 ... Err-234
A2 ER987654 Err-423 Err-421 Warn-982
A3 ED90800 ...
...

Not all tool/lot cominations have the same number of alarms, some will have
only 1 and others will have 50. Blanks in the ALMx columns can be ignored as
this means there were no more alarms.

I would like to maintain the Tool and Lot values while stacking the ALM
codes for each tool/lot comination as such:

Tool Lot ALM
A1 ER123456 Err-2341 (which is ALM1)
A1 ER123456 Warn-4511(which is ALM2)
A1 ER123456 Err-998 (which is ALM3)
...
A1 ER123456 Err-234 (which is ALM50)
A2 ER987654 Err-423 (whcih is ALM1)
A2 ER987654 Err-421 (which is ALM2)
...

I have found some macros to stack rows into columns, but I think it needs
additional steps to increment down row until hits a blank cell and also
copy/paste the tool and lot IDs in first two columns for each subsequent ALM
code.

Thanks for any help anyone can provide.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson