Finding matching cells within a page

Hi,

I have a list of prospective home sellers that I want to contact but I want to ensure I don't contact the same owner twice.

I have 4 owner name columns, generally a company name then a couple of owners. Is there a way I can mark the various properties owned by the same people? Obviously I can order each ownership column alphabetically to eliminate quite a few, but a lot of the owners have ended up in different columns or own multiple companies.

This potentially isn't the best example, but it's an example of the format I am using across a couple of different books.

Please let me know if you think this has breached any confidential info rules, I have removed postal addresses, plus it is all info I have found online.

Thanks
Ryan

When you say, "owned by the same people", does that mean all columns need to match? If the owners names match in the 1st 2 columns, but not the 3rd (or 4th), would they be considered the same owners?

Are you looking for all the unique names within all 4 columns? Is this a one time thing (meaning you could do it with a quick "process" rather than a formula) or do you need something automatic where you refresh the list and want to see the unique owners immediately?

Originally Posted by harrisonland

There's no easy way around this - your data are a mess and need tidying up.

However, see the attached... in paste this in A60 as ARRAY FORMULA, and copy and down for a list of distinct owners from columns D, E, F & G

Formula:

Please Login or Register  to view this content.

Alongside that there's a dropdown in C61 populated from the distinct owners list and which the formulas in columns D-G refer to. D61 has the following ARRAY FORMULA (other cells copied down and across)

Formula:

Please Login or Register  to view this content.

Bottom line is: when you select an owner in the dropdown, the columns D-G list the addresses for that owner that are listed in the respective columns. It's not ideal because it doesn't take variant spellings of owners into account, but it may be a start.

HTH
Tim

Genius.

I'll try it as soon as I can and report back