Retrieve multiple values associated with single ID in excel

Question

I'm curious if there is a simpler solution out there with respect to retrieving values associated with a single ID in excel.

I have explored the INDEX solution to look up multiple values in a list but that is not really dynamic and gives you the result in a vertical order rather than the horizontal order that I required. (see Results desired below)

The sample function i used was this

"=IF(ISERROR(SMALL(IF(IF(ISERROR(SEARCH($A$9,$A$1:$A$7)),FALSE,TRUE),ROW($A$1:$A$7)),ROW($C$1:$C$7))),"",INDEX($A$1:$C$7,SMALL(IF(IF(ISERROR(SEARCH($A$9,$A$1:$A$7)),FALSE,TRUE),ROW($A$1:$A$7)),ROW($C$1:$C$7)),3))"

*Ignore the references for this example.

I have two sheets that I'm working on and basically need to retrieve the values associated with a single ID from "Numbers Sheet" and store them on "Master Sheet" See images below for clearer explanation. The formula needs to find the subsequent number associated with the ID and put it on the subsequent column as shown below.

*note: any user ID can request for any number of tickets so it can range from 1-100 (just showing 3 as an example)

Appreciate any guidance from the excel masters here. The only other solution I can think of is to use a vba code to retrieve each value and store it in an array and then retrieve the value from the array. Let me know your thoughts!

Thanks in advance!

Master Sheet:

Numbers Sheet:

Results desired:

Answer 1

Put the following formula in cell C2[1] of your Master Sheet

{=IFERROR(INDEX(Numbers!$A:$C,SMALL(IF(Numbers!$A$1:$A$1000=$A2,ROW(Numbers!$A$1:$A$1000)),INT((COLUMN(A:A)-1)/2)+1),MOD(COLUMN(A:A)-1,2)+2),"")}

[1] I'm assuming it is row 2 since you have unfortunately not shown the row numbers.

The formula is an array formula. Input it into the cell without the curly brackets and confirm it with [Ctrl] + [Shift] + [Enter]. The curly brackets then will appear automatically.

Then fill the formula to right and downwards as needed.

Answer 2

you can try this code

Sub main()        
    Dim IdRng As Range, cell As Range, filtCell As Range
    Dim i As Long

    With Worksheets("Master Sheet") 
        Set IdRng = .Range("A1", .Cells(.Rows.Count, 1).End(xlUp)).SpecialCells(XlCellType.xlCellTypeConstants)
    End With

    With Worksheets("Numbers")
        With .Cells(1, 1).CurrentRegion
            For Each cell In IdRng  
                .AutoFilter field:=1, Criteria1:=cell.value '<--| filter it on current department value
                If Application.WorksheetFunction.Subtotal(103, .Cells.Resize(, 1)) > 1 Then
                    For Each filtCell In .Offset(1, 1).Resize(.Rows.Count - 1, 1).SpecialCells(XlCellType.xlCellTypeVisible)
                        cell.End(xlToRight).Offset(, 1).Resize(, 2).value = filtCell.Resize(, 2).value
                    Next filtCell
                End If
            Next cell
        End With
        .AutoFilterMode = False
    End With

    With Worksheets("Master Sheet").Cells(1, 1).CurrentRegion.Rows(1) 
        .Insert
        With .Offset(-1)
            .Font.Bold = True
            .Resize(, 2) = Array("ID", "Name")
            For i = 1 To .Columns.Count - 2 Step 2
                .Offset(, 1 + i).Resize(, 2) = Array("Description " & (i + 1) / 2, "Number " & (i + 1) / 2)
            Next i
        End With
    End With

End Sub

Answer 3

VBA is probably a better route for this and using .Find and .FindNext is the way I would go.

Attached is a generic FindAll function, so you could look for all the cells containing the ID in question then process the cells one at a time.

Function FindAll(What, _
    Optional SearchWhat As Variant, _
    Optional LookIn, _
    Optional LookAt, _
    Optional SearchOrder, _
    Optional SearchDirection As XlSearchDirection = xlNext, _
    Optional MatchCase As Boolean = False, _
    Optional MatchByte, _
    Optional SearchFormat) As Range

    'LookIn can be xlValues or xlFormulas, _
     LookAt can be xlWhole or xlPart, _
     SearchOrder can be xlByRows or xlByColumns, _
     SearchDirection can be xlNext, xlPrevious, _
     MatchCase, MatchByte, and SearchFormat can be True or False. _
     Before using SearchFormat = True, specify the appropriate settings for the Application.FindFormat _
     object; e.g. Application.FindFormat.NumberFormat = "General;-General;""-"""

    Dim SrcRange As Range
    If IsMissing(SearchWhat) Then
        Set SrcRange = ActiveSheet.UsedRange
    ElseIf TypeOf SearchWhat Is Range Then
        Set SrcRange = IIf(SearchWhat.Cells.Count = 1, SearchWhat.Parent.UsedRange, SearchWhat)
    ElseIf TypeOf SearchWhat Is Worksheet Then
        Set SrcRange = SearchWhat.UsedRange
    Else: Set SrcRange = ActiveSheet.UsedRange
    End If
    If SrcRange Is Nothing Then Exit Function

    'get the first matching cell in the range first
    With SrcRange.Areas(SrcRange.Areas.Count)
        Dim FirstCell As Range: Set FirstCell = .Cells(.Cells.Count)
    End With

    Dim CurrRange As Range: Set CurrRange = SrcRange.Find(What:=What, After:=FirstCell, LookIn:=LookIn, LookAt:=LookAt, _
        SearchDirection:=SearchDirection, MatchCase:=MatchCase, MatchByte:=MatchByte, SearchFormat:=SearchFormat)

    If Not CurrRange Is Nothing Then
        Set FindAll = CurrRange
        Do
            Set CurrRange = SrcRange.Find(What:=What, After:=CurrRange, LookIn:=LookIn, LookAt:=LookAt, _
            SearchDirection:=SearchDirection, MatchCase:=MatchCase, MatchByte:=MatchByte, SearchFormat:=SearchFormat)
            If CurrRange Is Nothing Then Exit Do
            If Application.Intersect(FindAll, CurrRange) Is Nothing Then
                Set FindAll = Application.Union(FindAll, CurrRange)
            Else: Exit Do
            End If
        Loop
    End If
End Function