Andrew,

I think that many people may be misunderstanding the layout of you
worksheet due to the limitations of trying to post your columns an
rows here in the forum.

Of course, maybe I misunderstand also!

Okay, for the sake of avoiding all misunderstanding, let me ask if thi
is correct....

Your COLUMN B, looks like this, am I right:

Olive
Data1
Data2
Data3
Green
Data4
Data5

So, B1 = "Olive", B2 = "Data1", B5 = "Green", B6 = "Data4"

Is that correct??

If so, then here is the macro that I propose:


Code
-------------------

Sub Andrews_Data()

For x = 1 To 500

If StrComp(Left(Trim(Cells(x, 2)), 3), "dat", vbTextCompare) < 0 Then

ColorName = Trim(CStr(Cells(x, 2)))
Cells(x, 2).Value = ""

Else

If Trim(Cells(x, 2).Value) < "" Then
Cells(x, 2).Value = Trim(CStr(Cells(x, 2).Value)) & " " & ColorName
End If

End If

Next

End Sub

-------------------


Please note that the macro above does not contain any "error handling,
or any instructions for what the macro should do if any of the data i
column B doesn't fit your pattern at all.

Also, I have simply told it to go from B1 down to B500, which i
completely arbitrary.

My "laziness" about errors and choosing how many rows is due to th
following:

I hope you can first confirm for me whether this macro produces
result which is at all similar to what you need. There's no point i
me making a macro that handles all types of eventualities if I a
totally off-base about your situation.

I am attaching a file with my own "dummy" data so you can see what
was thinking

Attachment filename: andrews_data.xls.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=62022
--
Message posted from http://www.ExcelForum.com