If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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! |
#4
|
|||
|
|||
what do you mean by reverse concatenation?
ps - you should be storing DATA in a DATABASE and not in excel. -Aaron |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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. |
#10
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to extract unique records based on two columns | MSA | Running & Setting Up Queries | 1 | June 22nd, 2005 01:12 PM |
AutoFilter lists unique values | R.J.H. | General Discussion | 3 | April 19th, 2005 08:53 PM |
Display unique values from two columns | Laura C | Worksheet Functions | 2 | August 19th, 2004 02:02 PM |
Trying to list unique values in an arryay | Dan S | General Discussion | 4 | June 22nd, 2004 03:29 AM |
Counting Unique Values Using "Starts with" Criteria | Mark T | Worksheet Functions | 17 | November 3rd, 2003 10:20 PM |