Need Help with Lists

[doh316]

hi,

I was wondering how one would collapse a list of items in separate cells within a column into one cell? Is there a basic function within excel that would allow this? Please help asap. Thanks!

 

[Barrie Davidson]

You mean something like (puts spaces between values also):

=A1&" "&B1&" "&C1

Does this help?

 

[doh316]

I dont think thats what I need.

i have a list of items in separate cells in a column, for example

tree
dog
apples

I was wondering if there was a function in Excel that will allow me to collapse the list into a single cell separated by commas

so the result would be in cell A1:

"tree, dog, apples" in cell A1

Not sure if i need a macro for this, whatever the case may be, how would i be able to do this?

Thanks

 

[Barrie Davidson]

How many cells in a column could you be looking at? Alternatively, a VBA solution could be something like:

Sub CombineValues()
'written by Barrie Davidson
Dim CombineRange As Range, c As Range
Dim NewVal As String
Dim NewValRange As Range

Set CombineRange = Application.InputBox("Select range to combine", , , , , , , 8)
For Each c In CombineRange
    NewVal = NewVal & ", " & c
Next c
NewVal = Right(NewVal, Len(NewVal) - 2)
Set NewValRange = Application.InputBox("Select range to paste combined value", , , , , , , 8)
NewValRange.Cells(1, 1) = NewVal
End Sub

Regards,