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

A

B

C

D

E

F

G

H

I

J

K

L

M

Client 1

Client 2

Client 3

Client 4

Date

Client

Date1

Date2

Date3

Date4

Person 3

Person 4

Person18

Person 40

05/01

Person 1

Person 4

Person7

Person 2

Person 30

05/08

Person 2

Person 8

Person 5

Person19

Person9

05/15

Person 3

Person 12

...

...

...

05/22

Person 4

<tbody>
</tbody>

 

[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)),"")

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Client 1

Client 2

Client 3

Client 4

Date

Client

Date1

Date2

Date3

Date4

2

Person 3

Person 4

Person18

Person 40

01/05/2018

Person 1

08/05/2018

22/05/2018

3

Person 1

Person 2

Person 3

Person 4

08/05/2018

Person 2

08/05/2018

22/05/2018

4

Person 8

Person 5

Person19

Person9

15/05/2018

Person 3

01/05/2018

08/05/2018

22/05/2018

5

Person 12

Person 1

Person 2

Person 3

22/05/2018

Person 4

01/05/2018

08/05/2018

6

***

**

**

*********

*********

*********

*********

***********

**

*********

***********

***********

***********

***********

**

<tbody>
</tbody>

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