I have a list of people with house numbers, names and email addresses.



House| Name | email
W2 | Fred | abc@zzz.com
W2 | Adil | xyz@zzz.com
W2 | Mark | cdf@zzz.com
W2 | Anya | 3456@zzz.com
E4 | Sai | tecx@zzz.com
E4 | Guru | gfyj@zzz.com
E4 | Mary | wedr@zzz.com

I need to be able to create an additional column that lists names and email addresses of everyone in the same house. So e.g., for each row in house W2 it will list Fred, Adil, Mark and Anya. etc.

Any suggestions on how to do this. I have about 2000 rows and about 280 different houses.

Thanks

Hi,

You may need a "Macro" for this. If you can show the desired result.
It will help us to better understand your request.

Taking the example data above, I want to be able to generate the following:

House| Name | email | Housemates
W2 | Fred | abc@zzz.com | Adil, Mark, Anya
W2 | Adil | xyz@zzz.com | Fred, Mark, Anya
W2 | Mark | cdf@zzz.com | Fred, Adil, Anya
W2 | Anya | 3456@zzz.com | Fred, Adil, Mark
E4 | Sai | tecx@zzz.com | Guru, Mary
E4 | Guru | gfyj@zzz.com | Sai, Mary
E4 | Mary | wedr@zzz.com | Sai, Guru

If it is easier to list all housemates including the person in the row, that is fine too.
e.g.
W2 | Fred | abc@zzz.com | Fred, Adil, Mark, Anya
W2 | Adil | xyz@zzz.com | Fred, Adil, Mark, Anya
etc.

Ultimately I need to generate a mailmerge to email to let everyone know their housemates.

If it is easier to accomplish this using Access, that's fine with me too.

Thanks

I managed to do what I needed to using a crosstab query in MS Access. Thanks