Comparing text in loops?

Blind_Man_Dan

New Member
Joined
Sep 3, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,648
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top