Extract Unique Values, Then Extract Again to Remove Suffixes

I have a list of builders where I want to extract all the unique values. 
Using =IF(COUNTIF($A$1:A1,A1)=1,A!,"") works fine to get the first set of 
unique values, but I need to extract it a bit further.  The builder names 
may be Ryan 50, Ryan 60 or just Ryan.  I need to combine those values to 
come up with the final list of unique builders that just says Ryan.  I'm not 
sure if I can strip off anything from the end as some are Ryan - Greenbrier. 
I thought maybe removing everything to the right of a blank space, but those 
entries have 2 blanks.

Any help would be greatly appreciated!  Thanks! 


0
karlspam (74)
6/22/2005 4:15:03 AM
excel 39879 articles. 2 followers. Follow

23 Replies
605 Views

Similar Articles

[PageSpeed] 47

Try using the filter (data > filter) and then in the drop down list, select 
"custom" and enter the custom feature you're looking for eg. contains "ryan".

"Karl Burrows" wrote:

> I have a list of builders where I want to extract all the unique values. 
> Using =IF(COUNTIF($A$1:A1,A1)=1,A!,"") works fine to get the first set of 
> unique values, but I need to extract it a bit further.  The builder names 
> may be Ryan 50, Ryan 60 or just Ryan.  I need to combine those values to 
> come up with the final list of unique builders that just says Ryan.  I'm not 
> sure if I can strip off anything from the end as some are Ryan - Greenbrier. 
> I thought maybe removing everything to the right of a blank space, but those 
> entries have 2 blanks.
> 
> Any help would be greatly appreciated!  Thanks! 
> 
> 
> 
0
mailforme (28)
6/22/2005 5:33:01 AM
It can't use a filter, it has to be derived from a formula.  It drives other 
reporting.  In addition, there are other builders, so they would have to be 
filtered as well.  I almost need something like reverse concatenation.

"James Hamilton" <mailforme@optusnet.com.au> wrote in message 
news:80E1933B-4165-47DA-9318-2DD95EFC4CE6@microsoft.com...
Try using the filter (data > filter) and then in the drop down list, select
"custom" and enter the custom feature you're looking for eg. contains 
"ryan".

"Karl Burrows" wrote:

> I have a list of builders where I want to extract all the unique values.
> Using =IF(COUNTIF($A$1:A1,A1)=1,A!,"") works fine to get the first set of
> unique values, but I need to extract it a bit further.  The builder names
> may be Ryan 50, Ryan 60 or just Ryan.  I need to combine those values to
> come up with the final list of unique builders that just says Ryan.  I'm 
> not
> sure if I can strip off anything from the end as some are Ryan - 
> Greenbrier.
> I thought maybe removing everything to the right of a blank space, but 
> those
> entries have 2 blanks.
>
> Any help would be greatly appreciated!  Thanks!
>
>
> 


0
karlspam (74)
6/22/2005 5:45:44 AM
what do you mean by reverse concatenation?

ps - you should be storing DATA in a DATABASE and not in excel.

-Aaron

0
aaron.kempf (776)
6/22/2005 6:00:36 AM
It is in a database, but am using Excel to pull some formatted reports. 
Here is a better example, for multiple builders:

Ryan
Ryan Townhomes
Ryan 60'
Mulvaney - Greenbrier
Mulvaney - 80
KB Home
KB Home 70'

I need to extract the unique values to give me:

Ryan
Mulvaney
KB Home

What I am doing is about impossible in Access, as it is pulling lot data 
into a formatted report spread over a 6 year rolling period.  I would still 
have the same issue in Access as well if I were to query the data for these 
values.

Thanks!

<aaron.kempf@gmail.com> wrote in message 
news:1119420035.971547.221490@g49g2000cwa.googlegroups.com...
what do you mean by reverse concatenation?

ps - you should be storing DATA in a DATABASE and not in excel.

-Aaron


0
karlspam (74)
6/22/2005 6:08:44 AM
I note that there is a space between the unique information eg. "ryan" and 
the other data on the end eg "townhomes"....so try this:

1. highlight the column with the data in it
2. Go to DATA > TEXT TO COLUMNS, then make sure "delimited" is selected and 
hit NEXT, then make sure that "SPACE" is checked, then hit NEXT, then FINISH.

Can you let me know if this works? 

"Karl Burrows" wrote:

> It is in a database, but am using Excel to pull some formatted reports. 
> Here is a better example, for multiple builders:
> 
> Ryan
> Ryan Townhomes
> Ryan 60'
> Mulvaney - Greenbrier
> Mulvaney - 80
> KB Home
> KB Home 70'
> 
> I need to extract the unique values to give me:
> 
> Ryan
> Mulvaney
> KB Home
> 
> What I am doing is about impossible in Access, as it is pulling lot data 
> into a formatted report spread over a 6 year rolling period.  I would still 
> have the same issue in Access as well if I were to query the data for these 
> values.
> 
> Thanks!
> 
> <aaron.kempf@gmail.com> wrote in message 
> news:1119420035.971547.221490@g49g2000cwa.googlegroups.com...
> what do you mean by reverse concatenation?
> 
> ps - you should be storing DATA in a DATABASE and not in excel.
> 
> -Aaron
> 
> 
> 
0
mailforme (28)
6/22/2005 6:32:03 AM
Karl,

You could use a helper column to get the sans number values

=SUBSTITUTE(A2,MID(A2,MATCH(FALSE,ISERROR(1*MID(A2,ROW(INDIRECT("1:10")),1))
,0),10-SUM(1*ISERROR(1*MID(A2,ROW(INDIRECT("1:10")),1))))*1,"")

and then count uniques here. The formula is an array formula, so commit with
Ctrl-Shift-Enter.

-- 
 HTH

Bob Phillips

"Karl Burrows" <karlspam@spam.yourbeacon.com> wrote in message
news:%23gagWDvdFHA.1456@TK2MSFTNGP15.phx.gbl...
> It is in a database, but am using Excel to pull some formatted reports.
> Here is a better example, for multiple builders:
>
> Ryan
> Ryan Townhomes
> Ryan 60'
> Mulvaney - Greenbrier
> Mulvaney - 80
> KB Home
> KB Home 70'
>
> I need to extract the unique values to give me:
>
> Ryan
> Mulvaney
> KB Home
>
> What I am doing is about impossible in Access, as it is pulling lot data
> into a formatted report spread over a 6 year rolling period.  I would
still
> have the same issue in Access as well if I were to query the data for
these
> values.
>
> Thanks!
>
> <aaron.kempf@gmail.com> wrote in message
> news:1119420035.971547.221490@g49g2000cwa.googlegroups.com...
> what do you mean by reverse concatenation?
>
> ps - you should be storing DATA in a DATABASE and not in excel.
>
> -Aaron
>
>


0
phillips1 (803)
6/22/2005 8:56:44 AM
Assuming that Column A contains your data, enter the following formula 
that needs to be confirmed with CONTROL+SHIFT+ENTER in B1 and copy down:

=INDEX($D$1:$D$3,MATCH(TRUE,ISNUMBER(SEARCH($D$1:$D$3,A1)),0))

....where D1:D3 contains the values to extract, such as Ryan, Mulvaney, 
and KB Home.

Hope this helps!

In article <#gagWDvdFHA.1456@TK2MSFTNGP15.phx.gbl>,
 "Karl Burrows" <karlspam@spam.yourbeacon.com> wrote:

> It is in a database, but am using Excel to pull some formatted reports. 
> Here is a better example, for multiple builders:
> 
> Ryan
> Ryan Townhomes
> Ryan 60'
> Mulvaney - Greenbrier
> Mulvaney - 80
> KB Home
> KB Home 70'
> 
> I need to extract the unique values to give me:
> 
> Ryan
> Mulvaney
> KB Home
> 
> What I am doing is about impossible in Access, as it is pulling lot data 
> into a formatted report spread over a 6 year rolling period.  I would still 
> have the same issue in Access as well if I were to query the data for these 
> values.
> 
> Thanks!
> 
> <aaron.kempf@gmail.com> wrote in message 
> news:1119420035.971547.221490@g49g2000cwa.googlegroups.com...
> what do you mean by reverse concatenation?
> 
> ps - you should be storing DATA in a DATABASE and not in excel.
> 
> -Aaron
0
domenic22 (716)
6/22/2005 12:36:46 PM
Where does this assume the OP's data is, and in what row(s) of the 
helper column is it to be array entered?

Alan Beban

Bob Phillips wrote:
> Karl,
> 
> You could use a helper column to get the sans number values
> 
> =SUBSTITUTE(A2,MID(A2,MATCH(FALSE,ISERROR(1*MID(A2,ROW(INDIRECT("1:10")),1))
> ,0),10-SUM(1*ISERROR(1*MID(A2,ROW(INDIRECT("1:10")),1))))*1,"")
> 
> and then count uniques here. The formula is an array formula, so commit with
> Ctrl-Shift-Enter.
> 
0
unavailable (273)
6/22/2005 5:26:45 PM
I have tried text to columns, but it tries to split out the formula instead 
of the value in the cell (even if I create a new cell and convert it to 
text).  Then, there are some builders that have several spaces in the name 
(KB Home 60).  I tried to use LEFT to pull out to the next blank space, but 
that only pulled KB!

"James Hamilton" <mailforme@optusnet.com.au> wrote in message 
news:DE9B1909-3DC7-4A6F-A26C-742ACB297C7F@microsoft.com...
I note that there is a space between the unique information eg. "ryan" and
the other data on the end eg "townhomes"....so try this:

1. highlight the column with the data in it
2. Go to DATA > TEXT TO COLUMNS, then make sure "delimited" is selected and
hit NEXT, then make sure that "SPACE" is checked, then hit NEXT, then 
FINISH.

Can you let me know if this works?

"Karl Burrows" wrote:

> It is in a database, but am using Excel to pull some formatted reports.
> Here is a better example, for multiple builders:
>
> Ryan
> Ryan Townhomes
> Ryan 60'
> Mulvaney - Greenbrier
> Mulvaney - 80
> KB Home
> KB Home 70'
>
> I need to extract the unique values to give me:
>
> Ryan
> Mulvaney
> KB Home
>
> What I am doing is about impossible in Access, as it is pulling lot data
> into a formatted report spread over a 6 year rolling period.  I would 
> still
> have the same issue in Access as well if I were to query the data for 
> these
> values.
>
> Thanks!
>
> <aaron.kempf@gmail.com> wrote in message
> news:1119420035.971547.221490@g49g2000cwa.googlegroups.com...
> what do you mean by reverse concatenation?
>
> ps - you should be storing DATA in a DATABASE and not in excel.
>
> -Aaron
>
>
> 


0
karlspam (74)
6/22/2005 5:35:42 PM
Well, perhaps I'm not being imaginative enough, but it is difficult for 
me to conceive of a formula that will be able to treat Ryan Townhomes 
like a duplicate of Ryan and not treat KB Home like a duplicate of KB.

Alan Beban

Karl Burrows wrote:
> It is in a database, but am using Excel to pull some formatted reports. 
> Here is a better example, for multiple builders:
> 
> Ryan
> Ryan Townhomes
> Ryan 60'
> Mulvaney - Greenbrier
> Mulvaney - 80
> KB Home
> KB Home 70'
> 
> I need to extract the unique values to give me:
> 
> Ryan
> Mulvaney
> KB Home
> 
> What I am doing is about impossible in Access, as it is pulling lot data 
> into a formatted report spread over a 6 year rolling period.  I would still 
> have the same issue in Access as well if I were to query the data for these 
> values.
> 
> Thanks!
> 
> <aaron.kempf@gmail.com> wrote in message 
> news:1119420035.971547.221490@g49g2000cwa.googlegroups.com...
> what do you mean by reverse concatenation?
> 
> ps - you should be storing DATA in a DATABASE and not in excel.
> 
> -Aaron
> 
> 
0
unavailable (273)
6/22/2005 5:38:10 PM
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!

"Domenic" <domenic22@sympatico.ca> wrote in message 
news:domenic22-D10C01.08364622062005@msnews.microsoft.com...
Assuming that Column A contains your data, enter the following formula
that needs to be confirmed with CONTROL+SHIFT+ENTER in B1 and copy down:

=INDEX($D$1:$D$3,MATCH(TRUE,ISNUMBER(SEARCH($D$1:$D$3,A1)),0))

....where D1:D3 contains the values to extract, such as Ryan, Mulvaney,
and KB Home.

Hope this helps!

In article <#gagWDvdFHA.1456@TK2MSFTNGP15.phx.gbl>,
 "Karl Burrows" <karlspam@spam.yourbeacon.com> wrote:

> It is in a database, but am using Excel to pull some formatted reports.
> Here is a better example, for multiple builders:
>
> Ryan
> Ryan Townhomes
> Ryan 60'
> Mulvaney - Greenbrier
> Mulvaney - 80
> KB Home
> KB Home 70'
>
> I need to extract the unique values to give me:
>
> Ryan
> Mulvaney
> KB Home
>
> What I am doing is about impossible in Access, as it is pulling lot data
> into a formatted report spread over a 6 year rolling period.  I would 
> still
> have the same issue in Access as well if I were to query the data for 
> these
> values.
>
> Thanks!
>
> <aaron.kempf@gmail.com> wrote in message
> news:1119420035.971547.221490@g49g2000cwa.googlegroups.com...
> what do you mean by reverse concatenation?
>
> ps - you should be storing DATA in a DATABASE and not in excel.
>
> -Aaron 


0
karlspam (74)
6/22/2005 5:49:52 PM
That works in some values, but not others.  Pulls KB Home as KB, Lennar 
Homes as Lennar, Saussy Burbank as Saussy, D.R. Horton as D.R.  It does work 
on the ones that are only one name builders like Mulvaney, though.

This is a tough one!

"Bob Phillips" <phillips@tiscali.co.uk> wrote in message 
news:OpLmihwdFHA.688@TK2MSFTNGP14.phx.gbl...
Karl,

You could use a helper column to get the sans number values

=SUBSTITUTE(A2,MID(A2,MATCH(FALSE,ISERROR(1*MID(A2,ROW(INDIRECT("1:10")),1))
,0),10-SUM(1*ISERROR(1*MID(A2,ROW(INDIRECT("1:10")),1))))*1,"")

and then count uniques here. The formula is an array formula, so commit with
Ctrl-Shift-Enter.

-- 
 HTH

Bob Phillips

"Karl Burrows" <karlspam@spam.yourbeacon.com> wrote in message
news:%23gagWDvdFHA.1456@TK2MSFTNGP15.phx.gbl...
> It is in a database, but am using Excel to pull some formatted reports.
> Here is a better example, for multiple builders:
>
> Ryan
> Ryan Townhomes
> Ryan 60'
> Mulvaney - Greenbrier
> Mulvaney - 80
> KB Home
> KB Home 70'
>
> I need to extract the unique values to give me:
>
> Ryan
> Mulvaney
> KB Home
>
> What I am doing is about impossible in Access, as it is pulling lot data
> into a formatted report spread over a 6 year rolling period.  I would
still
> have the same issue in Access as well if I were to query the data for
these
> values.
>
> Thanks!
>
> <aaron.kempf@gmail.com> wrote in message
> news:1119420035.971547.221490@g49g2000cwa.googlegroups.com...
> what do you mean by reverse concatenation?
>
> ps - you should be storing DATA in a DATABASE and not in excel.
>
> -Aaron
>
>



0
karlspam (74)
6/22/2005 5:51:55 PM
It is tough when builder names vary from one word names to initials and name 
and the multiple word names.

"Alan Beban" <unavailable@no.com> wrote in message 
news:uoEhKF1dFHA.1600@tk2msftngp13.phx.gbl...
Well, perhaps I'm not being imaginative enough, but it is difficult for
me to conceive of a formula that will be able to treat Ryan Townhomes
like a duplicate of Ryan and not treat KB Home like a duplicate of KB.

Alan Beban

Karl Burrows wrote:
> It is in a database, but am using Excel to pull some formatted reports.
> Here is a better example, for multiple builders:
>
> Ryan
> Ryan Townhomes
> Ryan 60'
> Mulvaney - Greenbrier
> Mulvaney - 80
> KB Home
> KB Home 70'
>
> I need to extract the unique values to give me:
>
> Ryan
> Mulvaney
> KB Home
>
> What I am doing is about impossible in Access, as it is pulling lot data
> into a formatted report spread over a 6 year rolling period.  I would 
> still
> have the same issue in Access as well if I were to query the data for 
> these
> values.
>
> Thanks!
>
> <aaron.kempf@gmail.com> wrote in message
> news:1119420035.971547.221490@g49g2000cwa.googlegroups.com...
> what do you mean by reverse concatenation?
>
> ps - you should be storing DATA in a DATABASE and not in excel.
>
> -Aaron
>
> 


0
karlspam (74)
6/22/2005 6:01:00 PM
Karl Burrows wrote...
>It is in a database, but am using Excel to pull some formatted reports.
>Here is a better example, for multiple builders:
>
>Ryan
>Ryan Townhomes
>Ryan 60'
>Mulvaney - Greenbrier
>Mulvaney - 80
>KB Home
>KB Home 70'
>
>I need to extract the unique values to give me:
>
>Ryan
>Mulvaney
>KB Home
....

It's not too difficult to get Ryan and KB Home using the following udf,
which returns an array.


Function foo(r As Range) As Variant
  Dim d As Object
  Dim c As Variant, x As Variant, t As String

  Set d = CreateObject("Scripting.Dictionary")

  For Each c In r
    t = c.Text
    If d.Exists(t) Then d.Item(t) = d.Item(t) + 1 _
     Else d.Add Key:=t, Item:=1
  Next c

  For Each c In d.Keys
    For Each x In d.Keys
      If x <> c And x Like c & "*" Then d.Remove Key:=x
    Next x
  Next c

  foo = Application.WorksheetFunction.Transpose(d.Keys)
End Function


But reducing out Mulvaney is much more difficult in general because
left substrings could be common to several distinct records, e.g.,

John Smith Builders
John Smith & Sons Construction

There may be approaches you could take using fuzzy string matching, but
you may find it expedient to use the udf above to filter out most
'duplicates', then remove the remaining ones manually.

0
hrlngrv (1990)
6/22/2005 7:01:57 PM
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 <ejddIL1dFHA.720@TK2MSFTNGP15.phx.gbl>,
 "Karl Burrows" <karlspam@spam.yourbeacon.com> 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!
0
domenic22 (716)
6/22/2005 7:18:58 PM
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" <domenic22@sympatico.ca> wrote in message 
news:domenic22-2832F5.15185822062005@msnews.microsoft.com...
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 <ejddIL1dFHA.720@TK2MSFTNGP15.phx.gbl>,
 "Karl Burrows" <karlspam@spam.yourbeacon.com> 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! 


0
karlspam (74)
6/22/2005 8:35:43 PM
In article <erhkzn2dFHA.3048@TK2MSFTNGP12.phx.gbl>,
 "Karl Burrows" <karlspam@spam.yourbeacon.com> wrote:

> Problem is I don't have a column of named builders like you have in D2:D10. 

I should have said, 'enter a list of builders in D2:D10', as described.  
So with the list of builders you've now provided, enter the following 
list in D2:D13...

Beazer
C.P. Morgan
D.R. Horton
KB
Lennar
McCar
Mulvaney
Pulte
Ryan
Saussy
Unsold
Westminster

....and change the references in the formula accordingly.  Does this help?
0
domenic22 (716)
6/22/2005 9:38:25 PM
I don't know why you picked "Greenbrier" as a problem.  Harlan Grove 
provided code that would eliminate it when operating on the list below. 
The real problem seems to be that your list of "builders names" does not 
include all the names of the builders. In particular, it does not include

C.P. Morgan
Lennar Homes
McCar
Mulvaney

each of which appears from your descriptions to be a builder's name.
(I am assuming that "Unsold Townhome Lots" is equivalent to a builder's 
name.)

Alan Beban

Karl Burrows wrote:
> 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!
0
unavailable (273)
6/23/2005 7:43:31 PM
I think we have gotten away from the actual problem.  It doesn't matter who 
the builder is (I only originally posted a partial list of builders), I want 
to programmatically or through a formula remove all the uncommon information 
from any builder that may be there at some point in time.  Builders may have 
a suffix to further define the lots they own in a neighborhood, so it 
doesn't matter who is in my list now.  I also don't want to create a list of 
the builders to match to the list to create the unique values.  I am doing 
that already and want to eliminate that portion of the worksheet input.

It boils down to taking a column of builder names and stripping off the data 
that is not common to all values for that builder, whether that means 
removing 60', - 60, - Greenbrier, or any other designation they may come up 
with.  I think that is where the hang up is.  Unless you can compare all the 
builder values and then say well these are pretty much alike other than the 
"60" at the end or "townhome" or other, so let's remove that portion.  I 
haven't had a chance to try to coding and maybe that is what it does.

Does all this make sense?  Thanks for everyone's patience.

"Alan Beban" <unavailable@no.com> wrote in message 
news:ugPU5vCeFHA.1136@TK2MSFTNGP12.phx.gbl...
I don't know why you picked "Greenbrier" as a problem.  Harlan Grove
provided code that would eliminate it when operating on the list below.
The real problem seems to be that your list of "builders names" does not
include all the names of the builders. In particular, it does not include

C.P. Morgan
Lennar Homes
McCar
Mulvaney

each of which appears from your descriptions to be a builder's name.
(I am assuming that "Unsold Townhome Lots" is equivalent to a builder's
name.)

Alan Beban

Karl Burrows wrote:
> 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! 


0
karlspam (74)
6/24/2005 5:38:18 AM
Karl Burrows wrote...
....
>It boils down to taking a column of builder names and stripping off the data
>that is not common to all values for that builder, whether that means
>removing 60', - 60, - Greenbrier, or any other designation they may come up
>with.  I think that is where the hang up is.  Unless you can compare all the
>builder values and then say well these are pretty much alike other than the
>"60" at the end or "townhome" or other, so let's remove that portion.  I
>haven't had a chance to try to coding and maybe that is what it does.
....

The problem is that some of the qualifiers added to some of the builder
names could be legitimate parts of a person's or company's name. I'm
not saying that's in fact the case, just that it could be. For example,
Home and House can be surnames.

If the only added qualifiers you have to deal with involve anything
beginning with a decimal numeral or a hyphen, you could use regular
expressions to remove them. But you also have normal words appended
with no more than a space separating them from the builder name. Unless
*YOU* could compile an exhaustive list of such words that would always
be deleted and never erroneously truncate any builder's name, then you
could use a list of these words as tokens to remove from your records.
Then feed what's left through a dictionary object to eliminate
duplicates.

0
hrlngrv (1990)
6/24/2005 4:25:44 PM
Thus, the problem.  Because each builder can have a unique name with some of 
those qualifiers in there, it is about impossible to identify their true 
name.  I think maybe developing a naming convention to add the hyphen or 
something else that we can tell it to strip everything right of that 
character is going to be the only way to go.  As it is now, it is truly 
"fuzzy logic!"

Thanks for all the help!

"Harlan Grove" <hrlngrv@aol.com> wrote in message 
news:1119630344.855951.183310@g14g2000cwa.googlegroups.com...
Karl Burrows wrote...
....
>It boils down to taking a column of builder names and stripping off the 
>data
>that is not common to all values for that builder, whether that means
>removing 60', - 60, - Greenbrier, or any other designation they may come up
>with.  I think that is where the hang up is.  Unless you can compare all 
>the
>builder values and then say well these are pretty much alike other than the
>"60" at the end or "townhome" or other, so let's remove that portion.  I
>haven't had a chance to try to coding and maybe that is what it does.
....

The problem is that some of the qualifiers added to some of the builder
names could be legitimate parts of a person's or company's name. I'm
not saying that's in fact the case, just that it could be. For example,
Home and House can be surnames.

If the only added qualifiers you have to deal with involve anything
beginning with a decimal numeral or a hyphen, you could use regular
expressions to remove them. But you also have normal words appended
with no more than a space separating them from the builder name. Unless
*YOU* could compile an exhaustive list of such words that would always
be deleted and never erroneously truncate any builder's name, then you
could use a list of these words as tokens to remove from your records.
Then feed what's left through a dictionary object to eliminate
duplicates.


0
karlspam (74)
6/24/2005 6:19:37 PM
Harlan Grove wrote:
> Karl Burrows wrote...
> ...
> 
>>It boils down to taking a column of builder names and stripping off the data
>>that is not common to all values for that builder, whether that means
>>removing 60', - 60, - Greenbrier, or any other designation they may come up
>>with.  I think that is where the hang up is.  Unless you can compare all the
>>builder values and then say well these are pretty much alike other than the
>>"60" at the end or "townhome" or other, so let's remove that portion.  I
>>haven't had a chance to try to coding and maybe that is what it does.
> 
> ...
> 
> The problem is that some of the qualifiers added to some of the builder
> names could be legitimate parts of a person's or company's name. I'm
> not saying that's in fact the case, just that it could be. . . .

Unless I misunderstand, we already know from the OP's 3rd posting in 
this thread that it is the case. He made it clear that in Ryan 
Townhomes, the builder's name is Ryan and Townhomes is a suffix; and 
that in KB Home the builder's name is KB Home and Home is not a suffix.

Alan Beban
0
unavailable (273)
6/24/2005 10:05:07 PM
"Alan Beban" <unavailable@no.com> wrote...
....
>Unless I misunderstand, we already know from the OP's 3rd posting
>in this thread that it is the case. He made it clear that in Ryan 
>Townhomes, the builder's name is Ryan and Townhomes is a suffix;
>and that in KB Home the builder's name is KB Home and Home is not
>a suffix.

Then it's the same situation as parsing surnames from a list of peoples from 
many original nationalities but with inappropriate English capitalization 
rules applied. E.g.,

Charles Der
Ruud Van Der Aalter
Nguyen Van Tieu

You come up with a rule to handle all these correctly, and you may have a 
prayer handling general company names which follow even fewer rules. [This 
is rhetorical. It's theoretically possible if there's a sufficiently 
complete rules base, but it'd be expedient to use an approach that works 
80-90% of the time and correct the rest manually.] 


0
hrlngrv (1990)
6/25/2005 9:37:44 PM
Reply:

Similar Artilces:

Page break based on value of sort field
I have an existing report that sorts on OR suite. OR1, OR2, OR3.. etc. We have added room for procedures, PROC1, PROC2, etc. I was hoping to modify the existing report (rather than have 2) that would send a page break when the room name changed from "O" something to "P"something (O*, P*). Possible? Thanks, T Ya T.Yaya wrote: >I have an existing report that sorts on OR suite. OR1, OR2, OR3.. etc. >We have added room for procedures, PROC1, PROC2, etc. I was hoping to >modify the existing report (rather than have 2) that would send a page break >when th...

Can not remove delegates
I have a scenario where an Outlook 2003 user is trying to remove delegates that were set up in Outlook 2000. The delegates are removed from the options window, the user clicks OK (and even restarts Outlook) yet when she returns to the delegates tab of the Options window, they reappear. When checking the properties of the folders that had delegates assigned, there are no permissions listed (we thought perhaps there was a conflict b/w the two.) Any suggestions? ...

how to determine the color with a value of column
I have the following problem. I have a percent column chart. I have only one series. In this case all columns will have the same color on the chart. I want these column colored regarding to the value of a column. I mean: - value: 80% color: yellow - value: 100% color: greeen - value: 40% color: red How to condition the color of the column with a percentage shown on the chart? I mean something like the conditional formatting, but on charts? Thanks in advance Marcin Jon Peltier has instructions for conditional charts: http://www.peltiertech.com/Excel/Charts/format.html#CondChart Kamyk ...

instantaneous cell value
This is probably incredibly simple but I've been going in circles with it for some time. I'm looking for a way to grab and hold the instantaneous value of a cell whose contents are constantly being updated. Any thoughts? You can "fix" the values of a cell by <copy>, then select another cell (maybe in another sheet) and Edit | Paste Special | Values | OK. This would leave the formula in the original cell to continue to update itself. Hope this helps. Pete thanks for the reply. this works as a "manual" solution but how can I assign only the contents o...

Can a value axis autoscale to something other than 0 for the min?
I know how to manually change the scale on a value axis, but is there a way to have the autoscale function automatically maximize the data in the chart? This is for a dynamic chart that can change when certain selection items are chosen from drop-down menus. For example, one set of data ranges from 6 to 9, so I would want the scale to be 5 to 10. Then, the selections change, and the data displayed in the chart now ranges from 31 to 39. I want the scale to be 30 to 40, not 0 to 40. Can this be done? Unfortunately, Excel's axis autoscale algorithms do not suit your (or my) prefer...

Displaying timestamps corresponding to min / max values
I have a table with temperature readings of different locations over a period of time. Thus multiple records per location. I have a query to show me the min and max temperatures for each location. I needed to add to that query the date when the min and the max temperatures were recorded. How could I do that? Many thanks in anticipation. This query returns two records (one for the minimum at the location and one for the maximum) SELECT YourTable.Location, Temperature IIF(YourTable.Temperature = YourCurrentQuery.MinTemperature, "Minimum","Maximum") as Which FROM You...

Calculating total values of duplicate entries
Hello all, This is going to be quite difficult to explain so I'll try my best and hopefully I'll get it sorted out :) OK, I have a spreadsheet with four columns in total. First column (A) contains four-letter combinations (eg. AAAA, AABB, ABAA etc). Second column (B) contains a value for each of the letter combinations in column one (eg. 110, 1000, 7000 etc). The third column (C) I have entered the code *=LEFT(A1,2)* to only show the first two letters of the text in column one. This applies to the whole column, not just row 2 as in my example. This would give, for example, results of ...

range of value
I want to have a range of values for each data point - example: Item A I want the top of the bar to be at 115,000 and the bottom at 85,000, Item B I want the top of the bar to be 130,000 and the bottom to be 90,000 - etc. How do I do that? Deb - You want a floating column chart. Set up your data like this: A B Bottom 85000 90000 Top 30000 40000 The row 'Top' is really the difference between your top and bottom values. Make a stacked column chart (bar charts are horizontal in Excel lingo), and the bottom columns will support the top columns. Double ...

Copy Cell.Value
Hi, I have 2 workbooks: # 1 Workbook.("TBD.xls").worksheet("Sheet1") #2 is a workbook that I open with this VBA macro: fName = Application.GetOpenFilename Workbooks.Open filename:=fName I also have this VBA Macro blastrow = Cells(Rows.Count, "B").End(xlUp).Row isum = Application.WorksheetFunction.Sum(Range("b1:b10000")) Cells(blastrow + 2, "B").Value = isum My Question is: How do I Copy the value of "Isum" in the Workbook.("TBD.xls").worksheet("Sheet1") in cell "F34" Thanks, I'm kind of confuse...

Why won't pasted values from a formula appear in a pivot table
I am trying to make a pivot table from an array of information that was the result of formulas on another worksheet page. I used a macro to copy these formulas on the other worksheet page, then paste their values in the array that I need a pivot table from. The pivot table will not sum any of the values in the array and it does not combine cells in one column that are alike if their corresponding cells in the adjacent column are different. Everything works if I retype the data instead of copying and pasting. This is not an option becuase there are several thousand lines of data. Th...

How do I remove the "Page 1" watermark from my working copy
My working copy has a large gray "Page 1" in the back ground How do I remove this from my working copy view>normal -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "George" <George@discussions.microsoft.com> wrote in message news:28259E62-5CD3-4795-A8B9-32B98FDF24EE@microsoft.com... > My working copy has a large gray "Page 1" in the back ground > How do I remove this from my working copy ...

Look up multi values
I’m struggling figuring out how you would do the following? Work sheet data. A1 =25 B1 = 40 C D 1 25 30 2 25 40 3 27 41 4 28 42 What I want to do is lookup the values 25 & 40 (A1 & B1) from table C1 D4 Matching the value 25 in Column C & 40 in Column D & return the location in the table to Cell A2? If there is no matching value, were decimals come into play, i.e. 25.8, then return the next largest from column C with its parent value from D, Returned value = 2 or 3 if 25.5. Sounds simple but I get lost very quick! Any Help greatly appreciated. one way. This is an array...

Change node attribute value
How would I change the value of an attribute in an XML node using C#? I have tried "findnode.Attributes["ID"].InnerText = newValue;" and "Attribute attri = (XmlAttribute)findnode.Attributes["ID"].Value;" but Im stuck! When I try casting, it say that it cannot convert a string to an XMLAttribute or when I try setting it to a new value it says "Object reference not set to an instance of an object." -------XML-------- <attr-inst id="20" state="known"> <val>100</val> </attr-inst> Thanks for any he...

Chart X Axis value label on both left and right sides
Can I add a second X axis value on both left and right sides of a chart? At present scrolling the chart to the right there are no axis label values to easily read the chart. Beemer Beemer, You have to have at least two Data Series to do that. Right click on the second of them, choose Format Data Series, then on the Axis tab, choose secondary axis. Note that Excel may change the relative values between the first and second axes, so you should set the Max and Min for both manually. HTH, Bernie MS Excel MVP "Beemer" <Beemer@nowhere.com> wrote in message news:MPG.1b2a2779...

Filtering Lookup values
I want to filter the lookup values when you click on "source campaign" on an opportunity to only include active campaigns. I'm not familiar with JScript and creating OnLoad statements. Can someone help me with the code for this? Thanks! Lisa ...

removing white space around an image
I am inserting a logo onto a publication. How do I get rid of the white space around the logo so that it blends with the back ground instead of looking like a white square just stuck on my project. Select your image, On the picture toolbar, select the "set transparent color" tool, and click on the color you want transparent. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "tdeaville" <tdeaville@discussions.microsoft.com> wrote in message news:E7A86C82-6845-498C-98F4-B4E663E48ACB@microsoft.com... >I am ...

Set Default Value
Is there any way to set default value through Command Button for example if the field of a table have value Apple so by pressing command button it will consider default value for ongoing records. Yes, you can do that. You can set the default for a text box, for example by: Me.YourTextBoxNameHere.Default = "Apple" and it will stay that way until you change it or until you close the form. It will revert to it's value that was set in design view when you reopen the form, but it will stay as long as you have the form open and don't close it. -- Bob Larson Access World F...

New Address type picklist value not appearing in picklist on forms
Hi, I've customized my Customer Address form to add a new AddressType value of "Registered Office"; value is 6 and I can see it when I go into the form customization editor. However, when I try to add a new address to, say, a Contact, this new address type value isn't shown. Ditto if I attempt to edit an existing address. What am I doing wrong? Thanks. -- Michael Mayne Did you publish the forms and then issue an IISRESET? Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Wed, 1 Dec 2004 10:21:04 -0800, ...

Removing blank rows #2
I have a spreadsheet that pulls data from an outside source and performs certain changes to the data through a macro I run. Once the macro is finished, there are anywhere from 3,000 to 20,000 rows of data and the rest of the rows (up to 65,000 plus) are blank. Normally, when you save a spreadsheet it stops at the last row of data but for some reason (I'm assuming the functions in my macro are to blame) the spreadsheet is saved with all 65,000 plus rows. Question, is there a line of code that I can include in the macro that will delete all the blank rows at the end of the spreads...

Remove a payment from GL
(We are running GP version 10). Our folks in Accounting posted a payment twice and need to remove it from the GL. They are saying that they cannot void it on their end. Is there a way to remove one (or both) of these payments from the GL? If it can't be done through the application, can I do it with a script? Thanks much for any response -- Norman First, can you describe why they can't void the payments? Any error messages? Are they just plain lazy? What are the reasons for not being able to void? Anything can be accomplished on the backend, but let that be your last option....

Converting calculated value to text
I am a limited Excel user. My project is to take an all caps comma-delimited file and turn it into an organization's printed directory. I've used PROPER to get the case changed, CONCATENATE to combine the street number and street name, and had to parse out the phone number (which came to me as 8882221234 format) using LEFT, MID, RIGHT so I can get dashes between the segments (888-222-1234). All is well so far. I will then go to Word format the fields and have a lovely booklet. My big problem is that now I have too many columns/fields. I want to convert these new fields to a text ...

How to make a formula produce a result (and not the "#Value" Error)
Hello - I am trying to build a formula that shows (A1*B1) + (C1*D1). The problem is that when A1 is empty, the result produces as #Value Error, even when C1 and D1 have values. Is there a best way to address this problem? I need the results for C1 * D1 to show up, even when A1 is blank. For clarity, the formula I am using is: =AG9*Rates!$B$2+'All current'! AB9*Rates!$B$3 Thanks for any suggestions You could use this approach: =3DIF(ISERROR(AG9*Rates!$B$2),IF(ISERROR('All current'!AB9*Rates!$B $3),"",'All current'!AB9*Rates!$B$3),AG9*Rates!$B$2+'All c...

Removal of Office 2007 OneNote
Hi, I installed Office 2007 on my computer recently. One program in the suite, Office 2007 OneNote, I do not use. Would it be safe to uninstall Office OneNote with Revo Uninstaller? Thank you. "moon" <moon@discussions.microsoft.com> wrote in message news:44D6F579-0539-48F9-BFC6-B533DBF6D71F@microsoft.com... > Hi, > > I installed Office 2007 on my computer recently. One program in the suite, > Office 2007 OneNote, I do not use. Would it be safe to uninstall Office > OneNote with Revo Uninstaller? > > Thank you. Why use that? Why no...

default field value based on a Max expression
How do you create a default value based on the current max value of that same field in the table? What i'm trying to do is similar to having an autonumber but I want it to be editable. Any ideas? Many thanks, SMiller In the DefaultValue property of a textbox use the expression: = DMax("FieldName", "TableName") + 1 That will add 1 to the highest value in the table. Be aware that in a multi-user environment, it is relatively easy for 2 users to grab the same number so you should save the record asap. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http:/...

Excel2007: copying filtered table values, how can I copy ONLY the filtered values
Hi I am new to excel 2007. In previous versions of Excel if I : - added filters to a table, - filtered it (eg all names starting with ABC), - highlighted the filtered table, - copied it, - pasted values to another sheet.... it only pasted the filtered values. Which is exactly what I want. However when I do exactly the above steps in 2007, the paste seems to paste the *entire* table, not just the filtered rows. Can someone explain? Is there a way to do this using the above process? Eg is there a setting or something that I need to turn on? Thanks in advance Andy...