Lookup with multiple results to be displayed

Hi

I have two spreadsheets one with a list of tenants names and the house they live in, EG:

Tenant Name Address
Mr White 1 Smith Street
Mr Black 1 Smith Street
Mr Orange 1 Smith Street
Mr Yellow 2 Colour Lane
Mr Brown 2 Colour Lane
Mr Pink 2 Colour Lane
Mr Blue 2 Colour Lane
Mr Green 1 Chapel Green
Mr Violet 1 Chapel Green

and my second spreadsheet has a list of all the jobs that need doing across the various houses, EG:

A B
1 Smith Street Repair Blind
1 Smith Street Replace Freezer
1 Smith Street Clean Microwave
2 Colour Lane Replace Picture
2 Colour Lane Repair Hoover
2 Colour Lane Repaint Desk
2 Colour Lane Replace Toilet Brush
1 Chapel Green Fix Roof
1 Chapel Green Clean Microwave

I need to do a look up or something, this is where I need your help, to populate a third column with a list of all the tenants that that job relates to, so my new spreadsheet would look something like this:


A B C
1 Smith Street Repair Blind Mr White, Mr Black, Mr Orange
1 Smith Street Replace Freezer Mr White, Mr Black, Mr Orange
1 Smith Street Clean Microwave Mr White, Mr Black, Mr Orange
2 Colour Lane Replace Picture Mr Yellow, Mr Brown, Mr Pink, Mr Blue
2 Colour Lane Repair Hoover Mr Yellow, Mr Brown, Mr Pink, Mr Blue
2 Colour Lane Repaint Desk Mr Yellow, Mr Brown, Mr Pink, Mr Blue
2 Colour Lane Replace Toilet Brush Mr Yellow, Mr Brown, Mr Pink, Mr Blue
1 Chapel Green Fix Roof Mr Green, Mr Violet
1 Chapel Green Clean Microwave Mr Green, Mr Violet

Any help you can give me I would greatly appreciate!


Thanks

Just to ensure I'm working to the same data format that you have, could you attach a workbook with the data laid out in the proper columns that you use.

Pete