How do I create a list of items in a column?

[stepaim]

I would like to create a list of the data in a column. For example: the data
in cell A1 is 92, the data in cell A2 is 76, the data in cell A3 is 112, etc.
I would like to create a formula that would list the data from the A column
into the formula cell (B1) like this: 1234, 1235, 1236. I need to have a list
of all the values in the column so that I can copy and paste it into another
program. Any help would be greatly appreciated.

A B
1 92 92, 76, 112, 32, 85
2 76
3 112
4 32
5 85

[systemx]

Hi,

The long way of doing this is -

=A1&","&A2&","&A3

and so on for all values. If there are not too many values and this is a realtively short list....then this would probably be fine.

Alternatively, you could write a macro to do it for you - I'm not sure exactly what it would look like but in (almost) plain english...

Dim MyString As String
Dim NextCell As String

Do

MyString = ActiveCell.Value
NextCell = ActiveCell.Offset(1,0).Value

MyString = MyString &","& NextCell

Until NextCell = ""

Well...someone will get the idea of what I am trying to achieve there....I don't know much vba so my apologies.

I also think there are character limits on excel cells (about 255 from memory). Although you can get more in there with 'memo' type cells...I think you can have problems getting the data back out of Excel.

Good luck

Rob

[Ken Johnson]

Hi stepaim,

you could try this macro...

Public Sub List()
Dim iLastRow As Long
Dim I As Long
Dim strList As String
iLastRow = Range("A" & Range("A:A").Rows.Count).End(xlUp).Row
For I = 1 To iLastRow
If Cells(I, 1).Value <> "" Then
strList = strList & Cells(I, 1).Value & ", "
End If
Next
If Right(strList, 2) = ", " Then
Let Cells(1, 2).Value = Left(strList, Len(strList) - 2)
Else: Let Cells(1, 2).Value = strList
End If
Cells(1, 2).EntireColumn.AutoFit
End Sub

If any cells in column A between A1 and the bottommost value is a blank
then it is not included in the list that is placed in B1.
If you want blanks included then use...

Public Sub List()
Dim iLastRow As Long
Dim I As Long
Dim strList As String
iLastRow = Range("A" & Range("A:A").Rows.Count).End(xlUp).Row
For I = 1 To iLastRow
strList = strList & Cells(I, 1).Value & ", "
Next
Let Cells(1, 2).Value = Left(strList, Len(strList) - 2)
Cells(1, 2).EntireColumn.AutoFit
End Sub


Ken Johnson

[Richard Buttrey]

On Fri, 5 May 2006 21:56:01 -0700, stepaim
<[email protected]> wrote:

>I would like to create a list of the data in a column. For example: the data
>in cell A1 is 92, the data in cell A2 is 76, the data in cell A3 is 112, etc.
>I would like to create a formula that would list the data from the A column
>into the formula cell (B1) like this: 1234, 1235, 1236. I need to have a list
>of all the values in the column so that I can copy and paste it into another
>program. Any help would be greatly appreciated.
>
> A B
>1 92 92, 76, 112, 32, 85
>2 76
>3 112
>4 32
>5 85

One way. Insert a column between A&B

Now enter the following

B1: =A1
B2: =B1&","&A2

Now copy B2 down your list. Then with the last cell in col B, do
Edit-->F9--Enter, to convert it to a string

You are limited by the number of characters you can have in a single
cell which I believe to be 32767, although only 1024 will display.

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________