Large Concatenate
 

Hi all. I have a database style worksheet that has in column A a persons
name. In columns B through AZ are items that person is authorized to work
on (pulldown list). This data will be uploaded to an ERP sytem, and need to
be in the format:
Joe|Item1|Item2|Item3|Item9|...
I can use a simple concatenate, but it will be huge. Is there a way to
programatically do this without actually using concatenate? It is also very
possible that columns could be blank. As above, Joe has data in the 1st 3
columns, then skips a few, then again in 9. Ideally, before Upload I'd like
to look at each row, and move all data to the left to eliminate blanks
columns. Thanks.

Large Concatenate
 

iLastRow = Cells(Rows.Count,"A").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

--

HTH

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


"Steph" wrote in message
...
Hi all. I have a database style worksheet that has in column A a persons
name. In columns B through AZ are items that person is authorized to work
on (pulldown list). This data will be uploaded to an ERP sytem, and need
to
be in the format:
Joe|Item1|Item2|Item3|Item9|...
I can use a simple concatenate, but it will be huge. Is there a way to
programatically do this without actually using concatenate? It is also
very
possible that columns could be blank. As above, Joe has data in the 1st 3
columns, then skips a few, then again in 9. Ideally, before Upload I'd
like
to look at each row, and move all data to the left to eliminate blanks
columns. Thanks.

Large Concatenate
 

Thanks Bob. Can I ask one more small favor? Turns out I need one more
column "concatenated". Is your code easy to modify to concatenate ColA,
ColB and then the 50 or so columns? Thank you!!!!

"Bob Phillips" wrote in message
...

iLastRow = Cells(Rows.Count,"A").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

--

HTH

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


"Steph" wrote in message
...
Hi all. I have a database style worksheet that has in column A a persons
name. In columns B through AZ are items that person is authorized to
work
on (pulldown list). This data will be uploaded to an ERP sytem, and need
to
be in the format:
Joe|Item1|Item2|Item3|Item9|...
I can use a simple concatenate, but it will be huge. Is there a way to
programatically do this without actually using concatenate? It is also
very
possible that columns could be blank. As above, Joe has data in the 1st
3
columns, then skips a few, then again in 9. Ideally, before Upload I'd
like
to look at each row, and move all data to the left to eliminate blanks
columns. Thanks.

Large Concatenate
 

Steph,

It should already concatenate however many columns there are

Bob


"Steph" wrote in message
...
Thanks Bob. Can I ask one more small favor? Turns out I need one more
column "concatenated". Is your code easy to modify to concatenate ColA,
ColB and then the 50 or so columns? Thank you!!!!

"Bob Phillips" wrote in message
...

iLastRow = Cells(Rows.Count,"A").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

--

HTH

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


"Steph" wrote in message
...
Hi all. I have a database style worksheet that has in column A a
persons
name. In columns B through AZ are items that person is authorized to
work
on (pulldown list). This data will be uploaded to an ERP sytem, and
need
to
be in the format:
Joe|Item1|Item2|Item3|Item9|...
I can use a simple concatenate, but it will be huge. Is there a way to
programatically do this without actually using concatenate? It is also
very
possible that columns could be blank. As above, Joe has data in the
1st
3
columns, then skips a few, then again in 9. Ideally, before Upload I'd
like
to look at each row, and move all data to the left to eliminate blanks
columns. Thanks.

Large Concatenate
 

Understood. Thanks Bob!

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

It should already concatenate however many columns there are

Bob


"Steph" wrote in message
...
Thanks Bob. Can I ask one more small favor? Turns out I need one more
column "concatenated". Is your code easy to modify to concatenate ColA,
ColB and then the 50 or so columns? Thank you!!!!

"Bob Phillips" wrote in message
...

iLastRow = Cells(Rows.Count,"A").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

--

HTH

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


"Steph" wrote in message
...
Hi all. I have a database style worksheet that has in column A a
persons
name. In columns B through AZ are items that person is authorized to
work
on (pulldown list). This data will be uploaded to an ERP sytem, and
need
to
be in the format:
Joe|Item1|Item2|Item3|Item9|...
I can use a simple concatenate, but it will be huge. Is there a way
to
programatically do this without actually using concatenate? It is
also
very
possible that columns could be blank. As above, Joe has data in the
1st
3
columns, then skips a few, then again in 9. Ideally, before Upload
I'd
like
to look at each row, and move all data to the left to eliminate blanks
columns. Thanks.