Excel 2007 - Reverse Index Match Item within a series of items 1

[BxWill]

I "inherited" a MS Excel 2007 workbook with two worksheets.

The first worksheet contains 5,500 records with two columns
Name-------Reports Received
Mary
Cathy
Brenda
Bill
Bob
.
.
.

Second worksheet contains 1,400 records with two colums;

Reports--------------Distribution
Report#1-------------Cathy;Bill;Mary
Report#2-------------Brenda;Mary
Report#3-------------Bob;Brenda

Is it possible to populate the second column on the first worksheet, column titled "Reports Received", with all of the
Reports that each individual receives?

For example, the second column for Mary should be "Report#1;Report#2"

The second column for Brenda should be "Report#2;Report#3

Thanks in advance for any insight...

 

[SkipVought]

Hi,

This workbook design is absolutely horrible!

My suggestion would be to reconstruct the sheet 2 table into a normalized table using the Data > Text to colums feature and this transform technique:

NORMALIZE Your Table using the PivotTable Wizard  faq68-5287


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Agree with Skip but his function should do what you need

Function Get_Reports(strWho As String) As String

Dim tmpList As String

tmpList = ""

For Each c In Sheets("Sheet2").Range("B2:B1400").Cells

If InStr(1, c.Text, strWho) > 0 Then

    tmpList = tmpList & c.Offset(0, -1).Value & ";"
    
End If

Next c

If tmpList <> "" Then

    Get_Reports = Left(tmpList, Len(tmpList) - 1)

Else

    Get_Reports = tmpList
    
End If

End Function

assumes 2nd sheet called Sheet2 - function should be entered as =get_reports(A2)
where the person's name is in A2 and the list of reports is on sheet2 A2:B1400

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question