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!

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 D13 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

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.

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