Search for multiple instances of name in multiple columns and return multiple dates in a row

[dtwyford]

Good day.

I am seeking and very much appreciate some help.
I have a table (see below). The table contains:

Colum C-F - names of 50 people, randomly place
Colum G - a date

Column I - the names of the 50 people

Column J-M - where I want to place the results of the search.

The aim of search:
Using the name in column I, search columns C-F and find all instances. For each instance, return the corresponding date (column G) and place it in J or K or L or M.

There will be no more than 4 instances of a name in columns C-F

[TABLE="width: 600"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Client 1[/TD]
[TD]Client 2[/TD]
[TD]Client 3 [/TD]
[TD]Client 4[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD]Client[/TD]
[TD]Date1[/TD]
[TD]Date2[/TD]
[TD]Date3[/TD]
[TD]Date4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Person 3[/TD]
[TD]Person 4[/TD]
[TD]Person18[/TD]
[TD]Person 40[/TD]
[TD]05/01[/TD]
[TD][/TD]
[TD]Person 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Person 4[/TD]
[TD]Person7[/TD]
[TD]Person 2[/TD]
[TD]Person 30[/TD]
[TD]05/08[/TD]
[TD][/TD]
[TD]Person 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Person 8[/TD]
[TD]Person 5[/TD]
[TD]Person19[/TD]
[TD]Person9[/TD]
[TD]05/15[/TD]
[TD][/TD]
[TD]Person 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Person 12[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]05/22[/TD]
[TD][/TD]
[TD]Person 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

Try:

Sub CopyDate()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim lColumn As Long
    lColumn = Cells(1, Columns.Count).End(xlToLeft).Column
    Dim person As Range
    Dim foundPerson As Range
    Dim sAddr As String
    For Each person In Range("I2:I" & LastRow)
        Set foundPerson = Range("C2:F" & LastRow).Find(person, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundPerson Is Nothing Then
            sAddr = foundPerson.Address
            Do
                lColumn = Cells(person.Row, Columns.Count).End(xlToLeft).Column + 1
                Cells(person.Row, lColumn) = Cells(foundPerson.Row, 7)
                Set foundPerson = Range("C2:F" & LastRow).FindNext(foundPerson)
            Loop While foundPerson.Address <> sAddr
            sAddr = ""
        End If
    Next person
    Application.ScreenUpdating = True
End Sub

 

[dtwyford]

Mumps.
Thank you! This will definitely be a keeper. Worked like a charm. Thank you again.

 

[mumps]

You are very welcome.

 

[markmzz]

Hi!

Another way (with formula):

In J2 and copy down and to the right

Array Formula - Use Ctrl+Shift+Enter to enter the formula

=IFERROR(SMALL(IF($C$2:$F$5=$I2,$G$2:$G$5),COLUMNS($J2:J2)),"")


[TABLE="class: grid, width: 882"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Client 1[/TD]
[TD]Client 2[/TD]
[TD]Client 3[/TD]
[TD]Client 4[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD]Client[/TD]
[TD]Date1[/TD]
[TD]Date2[/TD]
[TD]Date3[/TD]
[TD]Date4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Person 3[/TD]
[TD]Person 4[/TD]
[TD]Person18[/TD]
[TD]Person 40[/TD]
[TD="align: right"]01/05/2018[/TD]
[TD][/TD]
[TD]Person 1[/TD]
[TD="align: right"]08/05/2018[/TD]
[TD="align: right"]22/05/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Person 1[/TD]
[TD]Person 2[/TD]
[TD]Person 3[/TD]
[TD]Person 4[/TD]
[TD="align: right"]08/05/2018[/TD]
[TD][/TD]
[TD]Person 2[/TD]
[TD="align: right"]08/05/2018[/TD]
[TD="align: right"]22/05/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Person 8[/TD]
[TD]Person 5[/TD]
[TD]Person19[/TD]
[TD]Person9[/TD]
[TD="align: right"]15/05/2018[/TD]
[TD][/TD]
[TD]Person 3[/TD]
[TD="align: right"]01/05/2018[/TD]
[TD="align: right"]08/05/2018[/TD]
[TD="align: right"]22/05/2018[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Person 12[/TD]
[TD]Person 1[/TD]
[TD]Person 2[/TD]
[TD]Person 3[/TD]
[TD="align: right"]22/05/2018[/TD]
[TD][/TD]
[TD]Person 4[/TD]
[TD="align: right"]01/05/2018[/TD]
[TD="align: right"]08/05/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]*********[/TD]
[TD]*********[/TD]
[TD]*********[/TD]
[TD]*********[/TD]
[TD]***********[/TD]
[TD]**[/TD]
[TD]*********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]***********[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[dtwyford]

Hi!
Another way (with formula):
In J2 and copy down and to the right
Array Formula - Use Ctrl+Shift+Enter to enter the formula
=IFERROR(SMALL(IF($C$2:$F$5=$I2,$G$2:$G$5),COLUMNS($J2:J2)),"")

Markmzz,
Thank you for this. I do have to become more familiar with the array formula.
Darren

 

[markmzz]

Markmzz,
Thank you for this. I do have to become more familiar with the array formula.
Darren

You are welcome.

Markmzz