HOW TO: Get data out of a range

[abupu]

Hey Guys,

I'm having a problem, getting the data out of a table.

This is the data table:
<table border="0" cellpadding="0" cellspacing="0" width="320"><col style="width:48pt" span="5" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64">ID</td> <td class="xl63" style="width:48pt" width="64">Title 1</td> <td class="xl63" style="width:48pt" width="64">Title 2</td> <td class="xl63" style="width:48pt" width="64">Title 3</td> <td class="xl63" style="width:48pt" width="64">Title 4</td> </tr> <tr style="height:60.0pt" height="80"> <td class="xl63" style="height:60.0pt" height="80">1</td> <td class="xl63">AA1</td> <td class="xl63">BB1</td> <td class="xl63">CC1</td> <td class="xl63">DD1</td> </tr> <tr style="height:30.0pt" height="40"> <td class="xl63" style="height:30.0pt" height="40">2</td> <td class="xl63">AA2</td> <td class="xl63">BB2</td> <td class="xl63">CC2</td> <td class="xl63">DD2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">3</td> <td class="xl63">AA3</td> <td class="xl63">BB3</td> <td class="xl63">CC3</td> <td class="xl63">DD3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" height="20">4</td> <td class="xl63">AA4</td> <td class="xl63">BB4</td> <td class="xl63">CC4</td> <td class="xl63">DD4</td> </tr> </tbody></table>
This is what i need to access and output:

<table border="0" cellpadding="0" cellspacing="0" width="295"><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:8448;width:173pt" width="231"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:48pt" height="20" width="64">IDs</td> <td class="xl65" style="width:173pt" width="231">Combined Data
</td> </tr> <tr style="height:60.0pt" height="80"> <td class="xl65" style="height:60.0pt" height="80">1;2;3;4</td> <td class="xl66" style="width:173pt" width="231">AA1 BB1 CC1 DD1
AA2 BB2 CC2 DD2
AA3 BB3 CC3 DD3
AA4 BB4 CC4 DD4</td> </tr> <tr style="height:30.0pt" height="40"> <td class="xl65" style="height:30.0pt" height="40">1;2</td> <td class="xl66" style="width:173pt" width="231">AA1 BB1 CC1 DD1
AA2 BB2 CC2 DD2</td> </tr> </tbody></table>
The "Combined Data" column is what the formula should output and the lookup reference "IDs" are the ID numbers that need to be looked up. So for instance if i place a number 1 in the IDs column, the Combined Data column should output AA1 BB1 CC1 DD1 OR if i place 1;3 it should output AA1 BB1 CC1 DD1 and AA3 BB3 CC3 DD3.

I'm thinking using named ranges and an index lookup is the way to go?

Thanks!

 

[Andrew Poulsom]

Try this UDF:

Function GetData(IDList As String, DataRange As Range)
    Dim IDs As Variant
    Dim i As Long
    Dim r As Long
    Dim c As Long
    IDs = Split(IDList, ";")
    With DataRange
        For i = LBound(IDs) To UBound(IDs)
            If IsNumeric(IDs(i)) Then
                r = WorksheetFunction.Match(CDbl(IDs(i)), .Columns(1), False)
            Else
                r = WorksheetFunction.Match(IDs(i), .Columns(1), False)
            End If
            For c = 2 To .Columns.Count
                If c = 2 Then
                    GetData = GetData & .Cells(r, c).Value
                Else
                    GetData = GetData & " " & .Cells(r, c).Value
                End If
            Next c
            GetData = GetData & Chr(10)
        Next i
    End With
End Function

With your table in A1:E5 and the required IDs in G1 the formula would be:

=GetData(G1,A1:E5)

 

[abupu]

Thanks! Worked great!