delimit multiple rows' lists, collect into a column...

[spacely]

So, I have a column with rows like:

445
333,678,23
21,34
6
345,20

...and I need to delimit all those with the comma, and make a single column somewhere else like:

445
333
678
23
21
34
6
345
20


Rather not turn to VBA...probably an array function where you shift+ctrl+enter ?

Thanks!

 

[spacely]

I can make my macro into event code so that whenever a value in Column Y is changed, the list of single numbers would be automatically repopulated. How well that would work might depend on how the data gets into Column Y. Given you comment earlier about putting an apostrophe in front of comma delimited numbers that can be interpreted as long normal numbers, it almost sounds like you enter the numbers manually. Can you describe briefly how the data gets into Column Y for us?




When there are multiple responders to your question, you should indicate who you are responding to when you reply back to a posted message. Who was the above comment directed to... me or Peter?

Not sure how to get my responses to be right after each of yours. I'll try quoting. That 200th number response was for Peter.

I just type in the Column Y. I could link to the manual typing event in the whole column, so as I add more rows, it calls the function. Let me save that idea for later, and see if Peter's fix is a bit more "automated" after fixing...

 

[Rick Rothstein]

I just type in the Column Y. I could link to the manual typing event in the whole column, so as I add more rows, it calls the function.

I was going to give you the event code so you would have it as a fall back if needed; however, I have a question about your setup. You specifically reference the CONFIGURATIONS worksheet to get to your comma delimited number list, but you do not reference a sheet name for the Column AC output meaning the output would go to the active worksheet. Does that mean you do not output the list to the CONFIGURATIONS sheet?

 

[spacely]

I was going to give you the event code so you would have it as a fall back if needed; however, I have a question about your setup. You specifically reference the CONFIGURATIONS worksheet to get to your comma delimited number list, but you do not reference a sheet name for the Column AC output meaning the output would go to the active worksheet. Does that mean you do not output the list to the CONFIGURATIONS sheet?

The output goes to the current sheet on which I am calling the function... but let's be specific... it's called "menus". So, and edits in column Y on CONFIGURATIONS would cause the function to update a column in menus.

 

[Rick Rothstein]

The output goes to the current sheet on which I am calling the function... but let's be specific... it's called "menus". So, and edits in column Y on CONFIGURATIONS would cause the function to update a column in menus.

Okay, here is event code (note this is installed differently than a macro... see below) that will automatically output the list you want to Column AC on the Menus sheet when you make any changes in Column Y on the CONFIGURATIONS sheet...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Rng As Range, Combined As Variant
  If Not Intersect(Columns("Y"), Target) Is Nothing Then
    Sheets("Menus").Columns("AC").Clear
    If Target.Row > 1 And Application.CountA(Columns("Y")) > 0 Then
      Set Rng = Worksheets("CONFIGURATIONS").Range("Y2", Worksheets("CONFIGURATIONS").Cells(Rows.Count, "Y").End(xlUp))
      Combined = Split(Application.Trim(Replace(Join(Application.Transpose(Rng.Resize(Rng.Rows.Count + 1))), ",", " ")))
      If UBound(Combined) > -1 Then Sheets("Menus").Range("AC2").Resize(1 + UBound(Combined)) = Application.Transpose(Combined)
    End If
  End If
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Peter_SSs]

Would rather have the numerical answers.

That addressed one of the issues, but not the other.

Would have been helpful to clarify some of the issues I raised earlier.

Since that didn't happen, this assumes that
- text results (not numbers) is fine and
- there are no comma-space delimiters in the data.

So, do you have any data where the numbers have a comma and a space between them (eg "1234,4563, 5678") or do all numbers only have a comma between (eg "1234,4563,5678")

 

[spacely]

That addressed one of the issues, but not the other.

So, do you have any data where the numbers have a comma and a space between them (eg "1234,4563, 5678") or do all numbers only have a comma between (eg "1234,4563,5678")

I edited all data to be separated by only commas, after I also prefaced all with an apostrophe.

 

[spacely]

Okay, here is event code (note this is installed differently than a macro... see below) that will automatically output the list you want to Column AC on the Menus sheet when you make any changes in Column Y on the CONFIGURATIONS sheet...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Rng As Range, Combined As Variant
  If Not Intersect(Columns("Y"), Target) Is Nothing Then
    Sheets("Menus").Columns("AC").Clear
    If Target.Row > 1 And Application.CountA(Columns("Y")) > 0 Then
      Set Rng = Worksheets("CONFIGURATIONS").Range("Y2", Worksheets("CONFIGURATIONS").Cells(Rows.Count, "Y").End(xlUp))
      Combined = Split(Application.Trim(Replace(Join(Application.Transpose(Rng.Resize(Rng.Rows.Count + 1))), ",", " ")))
      If UBound(Combined) > -1 Then Sheets("Menus").Range("AC2").Resize(1 + UBound(Combined)) = Application.Transpose(Combined)
    End If
  End If
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

That works. Thanks.

 

[Rick Rothstein]

I edited all data to be separated by only commas, after I also prefaced all with an apostrophe.

As a side note to the new (event) code that I posted in Message #24 ... you would not have to remove spaces around your commas using that code, as a matter of fact, you could use spaces only as your delimiter if you wanted. Doing that would solve one of your problems because if you used spaces only as your delimiter, you would not have to preface any of your values with apostrophes as Excel would not interpret values delimited by spaces only as being a number.

 

[Peter_SSs]

I edited all data to be separated by only commas, after I also prefaced all with an apostrophe.

Thanks for that clarification.

That actually doesn't work on my big long list.

Problem is, I don't know just how big your "big long list is" & how many individual numbers it might contain & how many total digits might be involved, so I'm guessing a bit as to how best to try to allow for it.

This worked for me for more than 1,200 numbers of different length in the range A2:A42. Don't forget to adjust that range in the formula. You could also experiment with reducing/increasing the 10000 values. Less would be better and any significant increase will likely cause formula failure again (if it doesn't already with your data)

=IFERROR(TRIM(MID(SUBSTITUTE(SUBSTITUTE(","&TEXTJOIN(",",TRUE,A$2:A$42)&",",",",REPT(" ",10000),ROWS(B$2:B2)+1),",",REPT(" ",10000),ROWS(B$2:B2)),10000,10000))+0,"")

If your data is even bigger and with more individual numbers, try this. This worked for me with data down to row 200 and over 6,000 individual numbers. However, the actual amount of data it can handle will still depend on the string length when the TEXTJOIN function is invoked as reported earlier.

=IFERROR(REPLACE(LEFT(TEXTJOIN(",",TRUE,A$2:A$200),FIND("^",SUBSTITUTE(TEXTJOIN(",",TRUE,A$2:A$200)&",",",","^",ROWS(B$2:B2)))-1),1,FIND("^",SUBSTITUTE(","&TEXTJOIN(",",TRUE,A$2:A$200)&",",",","^",ROWS(B$2:B2)))-1,"")+0,"")