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