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!

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!

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" wrote in message
...
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!

what do you mean by reverse concatenation?

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

-Aaron

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!

wrote in message
oups.com...
what do you mean by reverse concatenation?

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

-Aaron

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!

wrote in message
oups.com...
what do you mean by reverse concatenation?

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

-Aaron

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" wrote in message
...
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!

wrote in message
oups.com...
what do you mean by reverse concatenation?

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

-Aaron

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 ,
"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!

wrote in message
oups.com...
what do you mean by reverse concatenation?

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

-Aaron

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!

wrote in message
oups.com...
what do you mean by reverse concatenation?

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

-Aaron

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.