The code below works but may need some adjustments. From your data I can't
tell if the plant No is in column A or B. I also don't know if the date is
in date format or just a string. Change the sheet names if necessary and try
the code. Tell me the results and I will fix as necessary.

Sub movedata()

With Sheets("Sheet1")
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Sh1RowCount = 1
Sh2RowCount = 1
Do While Sh1RowCount <= LastRow
ColAData = .Range("A" & Sh1RowCount)
If InStr(ColAData, "Plant No:") 0 Then
PlantNo = .Range("B" & Sh1RowCount)
SMUEND = .Range("D" & (Sh1RowCount + 2))
NewDate = .Range("A" & (Sh1RowCount + 2))
StringData = PlantNo & ",," & SMUEND & _
"," & NewDate
With Sheets("Sheet2")
.Range("A" & Sh2RowCount) = StringData
Sh2RowCount = Sh2RowCount + 1
End With
End If
Sh1RowCount = Sh1RowCount + 1
Loop
End With
End Sub


"Jim G" wrote:

I have a data set that I download to excel and I need to select specifica
data to import into another programme. The raw data looks like this.


Row Col A Col B Col C Col D Col E
Col F
9 Date Shift Type SMU Start SMU End SMU Total Error Gap
10
11
12 Type: 45D (DUMPER)
13
14
15 Plant No: DT35
16
17 19/11/2007 DAY 4,904.00 4,977.00 73.00 0.00

This repeats for each plant number with the same row spacing.

I would like to use a macro to create a unique list for each plant item
("DT35") followed by two commas (,,) the SMU End (Col D unformatted) comma
and the date (DD/MM/YYYY). EG "DT35,,4977,19/11/2007 all in column A of a
new sheet wthout headings.

The lists will vary in length with each site.
--
Jim