Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Large list of numbers to concatenate | Excel Worksheet Functions | |||
Concatenate large numbers of cells | Excel Discussion (Misc queries) | |||
Concatenate | Excel Worksheet Functions | |||
Concatenate | Excel Discussion (Misc queries) | |||
I know how to concatenate ,can one de-concatenate to split date? | New Users to Excel |