Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table to List (XP)

    Does anyone know if there is a feature in Excel that will let me convert the data in a table into a list? If not has anyone seen VBA code to make the conversion? Thanks.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Table to List (XP)

    Could you be more specific? What do you mean by a list. I would define the items in a column in excel as in a list. Are you trying to combine several cells into one cell? You can use the concatenation function or just the ampersand character (&):
    =A1&A2&A3

    Steve

  3. #3
    New Lounger
    Join Date
    Sep 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table to List (XP)

    I was pretty vague, sorry about that. I have a table that has two attributes for the row and one attribute for the column. I want to convert that into a list that shows each attribute and the value. I have attached a spreadsheet as an example.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table to List (XP)

    The macro below should do what you want. It is expects the table to be on a sheet named "List example" starting in cell A3. It will create a new worksheet named "New List" and create the list starting in cell A3.

    <pre>Public Sub ConvertToList()
    Dim oOSht As Worksheet, oNSht As Worksheet
    Dim I As Long, J As Long, K As Long
    Application.ScreenUpdating = False
    Set oOSht = Worksheets("List example")
    On Error Resume Next
    Set oNSht = Worksheets("NewList")
    On Error GoTo 0
    If oNSht Is Nothing Then
    Set oNSht = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    oNSht.Name = "NewList"
    End If
    oNSht.Cells.Clear
    oOSht.Range("A3:B3").Copy
    oNSht.Paste Destination:=oNSht.Range("A3")
    oNSht.Range("C3") = "Month"
    oNSht.Range("D3") = "Value"
    oOSht.Range("A33").Copy
    oNSht.Range("A3").PasteSpecial Paste:=xlPasteFormats
    oNSht.Range("C:C").NumberFormat = "mmm-yy"
    J = 0
    K = 0
    Do While oOSht.Range("C3").Offset(0, J).Value <> ""
    I = 0
    Do While oOSht.Range("A4").Offset(I, 0).Value <> ""
    oOSht.Range("a4:B4").Offset(I, 0).Copy
    oNSht.Paste Destination:=oNSht.Range("A4").Offset(K, 0)
    oOSht.Range("C3").Offset(0, J).Copy
    oNSht.Paste Destination:=oNSht.Range("C4").Offset(K, 0)
    oOSht.Range("C4").Offset(I, J).Copy
    oNSht.Paste Destination:=oNSht.Range("D4").Offset(K, 0)
    I = I + 1
    K = K + 1
    Loop
    J = J + 1
    Loop
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •