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

When using the formula you reccomend below on the fifty alarm columns for a
lot that only has a single alarm in ALM1 column, I get a return value of 50.
So sounds like it is counting these empty cells the same as the populated
cells???

Thanks,
rc


"Dave Peterson" wrote:

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

Nope. It's only counting cells that are filled.

The bad news is that you're looking at cells that look empty, but aren't.

Try this:
Pick out one of those cells (say x9)
put this in an empty cell:
=len(x9)
what do you see?
If it returns a number larger than 0, then you may have spaces in those
cells--or something that looks like a space.

If it returns 0, then those cells could have held formulas that evaluated to ""
(looked empty).

Depending on the problem (and the data), you may have a couple of choices.

#1. If they're space characters and no other space characters should be in any
of the other cells (that's important!).

Select the range
edit|replace
what: (space character)
with: (leave blank)
replace all

Then try the =counta() to see if it's fixed.

#2. If it's the result of a formula converted to values:
Select the range
edit|Replace
what: (leave blank)
with: $$$$$
replace all

followed by:
edit|Replace
what: $$$$$
with: (leave blank)
replace all

This should clean up that detritus. But check it with =counta().

Then run the macro.

#3. You copied and pasted from a web page. And those funny non-breaking html
characters are in your cells.

David McRitchie has a macro that cleans up this kind of stuff:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")



rc wrote:

When using the formula you reccomend below on the fifty alarm columns for a
lot that only has a single alarm in ALM1 column, I get a return value of 50.
So sounds like it is counting these empty cells the same as the populated
cells???

Thanks,
rc

Yee Haw! That did the trick. There was a single space in each of the
"empty" cells.

I used option#1 below on the whole worksheet, but used "match whole cell"
option to not remove the spaces in the other cells with text.

Thanks for your help Dave...it is greatly appreciated.

rc


"Dave Peterson" wrote:

Nope. It's only counting cells that are filled.

The bad news is that you're looking at cells that look empty, but aren't.

Try this:
Pick out one of those cells (say x9)
put this in an empty cell:
=len(x9)
what do you see?
If it returns a number larger than 0, then you may have spaces in those
cells--or something that looks like a space.

If it returns 0, then those cells could have held formulas that evaluated to ""
(looked empty).

Depending on the problem (and the data), you may have a couple of choices.

#1. If they're space characters and no other space characters should be in any
of the other cells (that's important!).

Select the range
edit|replace
what: (space character)
with: (leave blank)
replace all

Then try the =counta() to see if it's fixed.

#2. If it's the result of a formula converted to values:
Select the range
edit|Replace
what: (leave blank)
with: $$$$$
replace all

followed by:
edit|Replace
what: $$$$$
with: (leave blank)
replace all

This should clean up that detritus. But check it with =counta().

Then run the macro.

#3. You copied and pasted from a web page. And those funny non-breaking html
characters are in your cells.

David McRitchie has a macro that cleans up this kind of stuff:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")



rc wrote:

When using the formula you reccomend below on the fifty alarm columns for a
lot that only has a single alarm in ALM1 column, I get a return value of 50.
So sounds like it is counting these empty cells the same as the populated
cells???

Thanks,
rc

Glad you found a solution.

Watch out for cells with multiple space characters.

You may want to run a macro like:

Option Explicit
Sub testme()
Dim sCtr As Long
With Worksheets("Sheet999")
For sCtr = 1 To 20
.Cells.Replace what:=Space(sCtr), _
replacement:="", _
lookat:=xlWhole, _
searchorder:=xlByRows, _
MatchCase:=False
Next sCtr
End With
End Sub

Choose a number that's larger than the worst case you can imagine.



rc wrote:

Yee Haw! That did the trick. There was a single space in each of the
"empty" cells.

I used option#1 below on the whole worksheet, but used "match whole cell"
option to not remove the spaces in the other cells with text.

Thanks for your help Dave...it is greatly appreciated.

rc

"Dave Peterson" wrote:

Nope. It's only counting cells that are filled.

The bad news is that you're looking at cells that look empty, but aren't.

Try this:
Pick out one of those cells (say x9)
put this in an empty cell:
=len(x9)
what do you see?
If it returns a number larger than 0, then you may have spaces in those
cells--or something that looks like a space.

If it returns 0, then those cells could have held formulas that evaluated to ""
(looked empty).

Depending on the problem (and the data), you may have a couple of choices.

#1. If they're space characters and no other space characters should be in any
of the other cells (that's important!).

Select the range
edit|replace
what: (space character)
with: (leave blank)
replace all

Then try the =counta() to see if it's fixed.

#2. If it's the result of a formula converted to values:
Select the range
edit|Replace
what: (leave blank)
with: $$$$$
replace all

followed by:
edit|Replace
what: $$$$$
with: (leave blank)
replace all

This should clean up that detritus. But check it with =counta().

Then run the macro.

#3. You copied and pasted from a web page. And those funny non-breaking html
characters are in your cells.

David McRitchie has a macro that cleans up this kind of stuff:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")



rc wrote:

When using the formula you reccomend below on the fifty alarm columns for a
lot that only has a single alarm in ALM1 column, I get a return value of 50.
So sounds like it is counting these empty cells the same as the populated
cells???

Thanks,
rc


--

Dave Peterson