Lookup multiple values

[portucale]

Hi,

I have a list of ID's but in the same list there are duplicates, then I have my consolidation sheet without any duplicates, my issue is that I need to have the contents of a different column for each of the ID's.

Data sheet example

Column A (ID) | Column D (Result)

1111 first
2222 other
1111 second
3333 another test
2222 other two's
1111 third

Consolidation sheet

Column A (ID) | Incident 1 | Incident 2 | Incident 3

1111 first second third
2222 other other two's
3333 another test

Is there any formula/vba which could perform something similar?

Many Thanks for your time,

 

[storm8]

here is your function

Public Function AllVals(rng As Range, SearchFor As Variant, Optional retCol As Byte = 2) As Variant


    Dim v As Variant
    Dim a As Variant
    a = rng.Value
    Dim r As Long
    For r = 1 To UBound(a)
        If a(r, 1) = SearchFor Then
            v = v & a(r, retCol) & " | "
        End If
    Next r


    If Len(v) > 0 Then v = Left(v, Len(v) - 3)
    
    AllVals = v


End Function

insert into module, use as any other built in excel function
the params are:
rng = range with all data, first col of range must be the lookup column (A) in your case
searchFor the search value: 1111 in your case
retCol: values from what col of rng do you want to return: 4 in your case

 

[markmzz]

Maybe this:

Layout

[TABLE="width: 212"]
<tbody>[TR]
[TD="class: xl63, width: 32, bgcolor: transparent"]ID
[/TD]
[TD="class: xl63, width: 67, bgcolor: transparent"]Incident 1
[/TD]
[TD="class: xl63, width: 62, bgcolor: transparent"]Incident 2
[/TD]
[TD="class: xl63, width: 57, bgcolor: transparent"]Incident 3
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Sheet2
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]1111
[/TD]
[TD="class: xl64, bgcolor: yellow"]first
[/TD]
[TD="class: xl64, bgcolor: yellow"]second
[/TD]
[TD="class: xl64, bgcolor: yellow"]third
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]2222
[/TD]
[TD="class: xl64, bgcolor: yellow"]other
[/TD]
[TD="class: xl64, bgcolor: yellow"]other two's
[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]3333
[/TD]
[TD="class: xl64, bgcolor: yellow"]another test
[/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="class: xl64, bgcolor: yellow"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]*****
[/TD]
[TD="class: xl63, bgcolor: transparent"]************
[/TD]
[TD="class: xl63, bgcolor: transparent"]***********
[/TD]
[TD="class: xl63, bgcolor: transparent"]**********
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


Array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

B2-> =IFERROR(INDEX(Sheet1!$D$2:$D$7,SMALL(IF(Sheet1!$A$2:$A$7=$A2,ROW(Sheet1!$A$2:$A$7)-ROW(Sheet1!$A$2)+1),COLUMNS($B2:B2))),"")

Markmzz

 

[portucale]

Many thanks to both Storm8 and markmzz, both methods work and I do appreciate your time to help. If I may be a bit more "picky" I would say although "storm8" user defined formula is great the result appear in the same row which obliges to perform a few more steps to have it separated, like in markmzz example.

Do you happen to know if markmzz solution could be possible to put into a procedure/function?

Again many thanks to both.