Generate a list from single cell input

[alistairg01]

Hello all,

My question is basically how can you generate a list in Excel based on a single cell input?
For example, in the single cell the user types 10 then replaces it with 20 and then 30 etc. Is it possible to generate a list like:
10
20
30
etc

This could be for any number of inputs, and still work if duplicate entries are typed (eg 10 then 10 then 20 etc). If anyone has a solution I would love to hear it.
Thanks for the help!
Alistair

 

[L. Howard]

Try this, in the sheet module.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F1")) Is Nothing Then
  Range("A" & Rows.Count).End(xlUp)(2) = Target
End If
[F1].Activate
End Sub

 

[alistairg01]

Thank you very much, worked perfectly!

Going on from that would you know an easy way to concatenate the list back into a single cell. For example if list is:

12
1
14
20

Concatenate to: 12,1,14,20 in one cell. Sorry I only just thought of doing this, I assume it would be easier to do this from a list, but if you could do it all in one step that would also be perfect. For example from a user inputing into a single cell, those values are concatenated into a different single cell. (10 then 20 then 15 etc to 10,20,15,etc).

Sorry I would be able to do this if I knew how many cells were used each time, but with a variable number it might be easier to use a macro.

Thanks!

 

[L. Howard]

Try this in the sheet module.

Enter your values in F1 and they will be listed in column E, E2 and down as far as...?
Now run Sub Concat_E_to_G_Spc(), from a button, assigned key-stroke or CTRL + f8 > select macro and > RUN.

The finished product is in G1, comma delimited. (You can change the , to suit or eliminate delimiter, using a space)

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F1")) Is Nothing Then
  Range("E" & Rows.Count).End(xlUp)(2) = Target
  [F1].Activate
End If

End Sub


Sub Concat_E_to_G_Spc()
[G1].ClearContents
Dim rngC As Range
Dim myConC As String

For Each rngC In Range("E2:E" & Cells(Rows.Count, "E").End(xlUp).Row)

   If Len(rngC) = 0 Then
      [G1] = [G1] & " "
   Else
      [G1] = [G1] & rngC.Text & ", "
   End If
   
Next

   With Range("G2")
     .Formula = "=LEFT(G1,LEN(G1)-2)": .Value = .Value
     .Cut Range("G1")
   End With
   [E:E].ClearContents
End Sub