Problem is I don't have a column of named builders like you have in D2:D10.
Maybe this would be easier. Here is the exact list of builders they have.
This can also change as new subdivisions are added, but the list is
populated from an Access database. Builders may have more than one type of
lot in one subdivision or may have lots in multiple subdivisions, which is
what creates the need to identify builders this way for reporting. I don't
think this is going to be possible since Excel would have no way of knowing
that "Greenbrier" was not part of the builder name.

Beazer Homes
Beazer Homes 50'
Beazer Homes 60'
C.P. Morgan 40'
C.P. Morgan 50'
D.R. Horton
D.R. Horton
D.R. Horton 40
D.R. Horton 50
KB Home
KB Home 40
KB Home 50
Lennar Homes 40
Lennar Homes 50
Lennar Homes 55
Lennar Homes 60
McCar Homes
McCar Townhomes
Mulvaney 60
Mulvaney Homes
Mulvaney Townhomes
Pulte
Pulte
Ryan
Ryan - Aldridge
Ryan - Greenbrier
Ryan 60
Ryan 66
Ryan Townhomes
Ryan Townhomes
Saussy Burbank
Unsold Townhome Lots
Westminster
Westminster Townhomes


Thanks!

"Domenic" wrote in message
...
If A2:A14 contains...

Beazer Homes 50' & 60'
D.R. Horton 40 & 50
Ryan - Greenbrier
Ryan - Aldridge
Ryan Townhomes
Westminster Townhomes
KB Home 40 & 50
C.P. Morgan 40' & 50'
Lennar Homes 40, 50, 55 & 60
McCar Homes & McCar Townhomes
Mulvaney 60
Mulvaney Homes
Mulvaney Townhomes

....and D2:D10 contains...

Beazer
C.P. Morgan
D.R. Horton
KB Home
Lennar
McCar
Mulvaney
Ryan
Westminster

....enter the following formula in B2, and copy down...

=INDEX($D$2:$D$10,MATCH(TRUE,ISNUMBER(SEARCH($D$2: $D$10,A2)),0))

....confirmed with CONTROL+SHIFT+ENTER. You should get the following
results...

Beazer
D.R. Horton
Ryan
Ryan
Ryan
Westminster
KB Home
C.P. Morgan
Lennar
McCar
Mulvaney
Mulvaney
Mulvaney

Then, if you want a unique list -- it only just dawned on me that this
is probably what you want :) -- you can either use 'Advanced Filter'
and check 'Unique records only' or use the following formula...

C2, copied down until you get #N/A:

=INDEX(B2:$B$14,MATCH(0,COUNTIF($C$1:C1,B2:$B$14), 0))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"Karl Burrows" wrote:

That is really close! It works on about 50% of the builder names.
Examples:

Works on:
Beazer Homes 50' & 60'
D.R. Horton 40 & 50
Ryan - Greenbrier
Ryan - Aldridge
Ryan Townhomes
Westminster Townhomes
KB Home 40 & 50

Does not work on:
C.P. Morgan 40' & 50'
Lennar Homes 40, 50, 55 & 60
McCar Homes & McCar Townhomes
Mulvaney 60
Mulvaney Homes
Mulvaney Townhomes

Should convert to:
C.P. Morgan
Lennar Homes
McCar Homes
Mulvaney

Strange how it works on some similar and not others. This is really good!
Thanks!