Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I create a list of items in a column?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I create a list of items in a column?
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 -- systemx ------------------------------------------------------------------------ systemx's Profile: http://www.excelforum.com/member.php...o&userid=29254 View this thread: http://www.excelforum.com/showthread...hreadid=539485 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I create a list of items in a column?
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I create a list of items in a column?
On Fri, 5 May 2006 21:56:01 -0700, stepaim
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 __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a dependent list, to a current list? | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
Compare 2 columns, and create a list of items that are in both lists | Excel Worksheet Functions | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) |