Convert table to list

**kvn** · 02-10-2012, 06:27 PM

Hi I am having difficulty converting a table to a list. However the table is a database of distances between cities and the list I want to create needs to omit repetitive data (ex since City 1 to City 2 will be listed first City 2 to to City 1 can be omitted). I also want to omit City 1 to City 1 data as it is redundant. I have attached a spreadsheet demonstrating my data and what I am looking for. Is this possible? Any help with be greatly appreciated.

Kevin

**protonLeah** · 02-11-2012, 04:04 PM

Range	Name
B2:F6	DistanceMatrix
B1:F1	Destination
A2:A6	Origin

C13:

=INDEX(DistanceMatrix,MATCH(A13,Origin,0),MATCH(B13,Destination,0))

and copied down

**kvn** · 02-11-2012, 06:53 PM

Thanks Ben

This works great. However is there a way to automatically omit the 'city to city' scenarios which have already been included to create the list (ie if City 1 to City 2 is listed than I don't want to list City 2 to City 1)? Your solution is dependent on this part of the list being created already. The reason I ask this is because the table i am using is 140 x 140 and automating this step would be a huge help. Thanks for your help.

Regards,
Kevin

**protonLeah** · 02-11-2012, 09:51 PM

Here's a macro version:

Option Explicit

Sub TableToList()
    Application.ScreenUpdating = False
    Dim Origin      As Range, _
        Destination As Range, _
        OCell       As Range, _
        DCell       As Range, _
        Table       As Worksheet, _
        ListSheet   As Worksheet, _
        DestRow     As Long
    
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "ListSheet"
    
    Set ListSheet = Sheets("ListSheet")
    Set Table = Sheets("Sheet1")
    
    With Table
        Set Origin = .Range("A2", .Cells(Rows.Count, "A").End(xlUp))
        Set Destination = .Range("B1", .Cells(1, Columns.Count).End(xlToLeft))
    End With

    ' only copy values from the upper triangle of the table
    For Each OCell In Origin
        For Each DCell In Destination
            If OCell.Row < DCell.Column Then
                DestRow = DestRow + 1
                ListSheet.Cells(DestRow, "A").Value = OCell.Value
                ListSheet.Cells(DestRow, "B").Value = DCell.Value
                ListSheet.Cells(DestRow, "C").Value = Table.Cells(OCell.Row, DCell.Column).Value
            End If
        Next DCell
    Next OCell
    Application.ScreenUpdating = True
End Sub