Perfect! Thanks so much Bob! You are a master!!


"Bob Phillips" wrote in message
...
Hi Steph,

Sorry to take so long, work got in the way.

Here's my (first?) shot at it

Sub FormatData()
Dim iLastRow As Long
Dim iLastCol As Long
Dim iCol As Long
Dim cCols As Long
Dim i As Long, j As Long
Dim fAll As Boolean
Dim sTemp

For i = 5 To 1000
If Not IsEmpty(Worksheets("Sheet2").Cells(i, "A")) Then
sTemp = sTemp & "|" & Worksheets("Sheet2").Cells(i, "A").Value
End If
Next i

iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To iLastRow
fAll = Cells(i, "M").Value = "ALL"
iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column
If fAll Then
For j = 2 To 12
If Cells(i, j).Value < "" Then
Cells(i, 1).Value = Cells(i, 1).Value & "|" & Cells(i,
j).Value
End If
Next j
Cells(i, 1).Value = Cells(i, 1).Value & "|" & sTemp
Else
For j = 2 To iLastCol
If Cells(i, j).Value < "" Then
Cells(i, 1).Value = Cells(i, 1).Value & "|" & Cells(i,
j).Value
End If
Next j
End If
cCols = IIf(iLastCol 1, iLastCol - 1, 1)
Cells(i, 2).Resize(, cCols).ClearContents
Next i

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steph" wrote in message
...
Hi Bob,

Exactly. I apologize...sometimes I'm not very clear in describing this
stuff!

M-BJ are the cells that the user uses a pick list to select. In Sheet2
A5:A1000 are the "picks" that they can pick from (The data validation
list
source). So if the user wanted to select every pick from the list, I
wanted
to allow them to simply select ALL in M10, rather than slelcting each
item
one at a time in columns M:BJ.

Thanks Bob!

PS - If this was posted more than once, I apologize. My other response
did
not show up in my subject list, so I wasn't sure if it was posted or not.
Sorry!


"Bob Phillips" wrote in message
...
Let me see that I understand correctly.

If M10 say is "ALL", then that line will be concatenated to the
contents
of
cells A-L, and then the contents of A5:A1000, the rest of M-BJ is
ignored.

If M10 is not "ALL", we just concatgenate A-BJ as before.

Is that correct?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steph" wrote in message
...
Thanks to Bob Philips, I have the bit of code below. It takes 50
columns
of
data, and essentially concatenates each populated cell within a row,
with
a
| in between each one. My quesion is this - If the contents of the
first
column of data (M) is the word "ALL", I would like the code to
concatenate
all populated cells from a table on sheet1 (A5:A1000).

So basically, columns M through BJ are pick lists. Rather than
having
the
user pick 50 items 1 at a time, if the code sees ALL in the forst
column,
it
will take all data from the data validation list and concatenate
them.
Is
this possible? Thanks!

iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To iLastRow
iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column
For j = 2 To iLastCol
If Cells(i, j).Value < "" Then
Cells(i, 1).Value = Cells(i, 1).Value & "|" &
Cells(i,
j).Value
Cells(i, j).Value = ""
End If
Next j
Next i