Thanks Joel,

The plant number is the string "Plant No: DT35" in column A. The date is
formatted as date, but not necessary in the text file that will be exported.

When i have the data arranged I will try to create a txt file with only the
comma separated lines ready for import. I can probably handle this from my
'library' of code, however, if you have a standard method from this macro I'd
be glad to have it.

I'll test this on the morrow and get back to you with the result.

Cheers and as always many thanks.


--
Jim


"Joel" wrote:

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