Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re Large Concatenate - (RP)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re Large Concatenate - (RP)
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re Large Concatenate - (RP)
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 |
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) | |||
I know how to concatenate ,can one de-concatenate to split date? | New Users to Excel | |||
Large Concatenate - (RP) | Excel Programming | |||
Large Concatenate | Excel Programming |