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

ID	Incident 1	Incident 2	Incident 3	Sheet2
1111	first	second	third
2222	other	other two's
3333	another test
*****	************	***********	**********

<tbody>
</tbody>

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.