Comparing text in loops?

[Blind_Man_Dan]

Hi Folks

I have a list of people from the Electoral Roll, and they're sorted into their addresses. I now need to compare the people in each address to see if their surnames are all the same so I can determine what to have as the addressee on an envelope, eg if all people have the same surname, I'm going to address the envelope as "The Smith Family". With households ranging from 1 to maybe 10 people, I'm struggling to work out the best way to establish this. Obviously if there's only 1 or two people in the household, it's fairly straight forward, however if there's more, I'm getting lost with how to achieve this. I've thought about defining the range of the surnames, then using a for each cell in range, but I'm struggling to work out how that will give me the result I need, which is to know if all the surnames are the same.

StrComp would be useful if I could compare more than 2, and I thought about an IF,Then, type thing, but as the number of things I need to compare are variable, I just can't work it out!

Any suggestions gladly appreciated, otherwise I've got about 3000 addresses to go through and do it manually!

Cheers

Dan

 

[onlyadrafter]

Hello,

Have assumed names are in Col A and addresses are in Col B, and data starts in row 2.

This should give you a start:

=IF(COUNTIF($B$2:B3001,B2)=COUNTIF($A$2:A3001,A2),A2&" Family","")

and copy down as far as required.

Post back with any further requirements.