Filter unique distinct values where adjacent cells contain search string in excel
Question: How do I create a unique distinct list where adjacent cell values contains a search string?
AA102 CA
AA103 BC
AA104 CA
AD103 SD
AA201 CC
Search string: 1
Unique distinct list
CA
BC
SD
Answer:
Filter unique distinct values where adjacent cells contain search string
Array formula in C12:
=INDEX(Column_txt, MATCH(0, COUNTIF($C$11:C11, Column_txt)*(SEARCH($C$8, Search_column)), 0)) + CTRL + SHIFT + ENTER copied down as far as needed.
Named ranges
Column_txt (C2:C6)
Search_column (B2:B6)
What is named ranges?
How to implement array formula to your workbook
Change the named ranges. If your list starts at, for example, F3. Change $C$11:C11 to F2:$F$2, in the above formulas.
Download excel file for this tutorial.
Filter unique distinct values where adjacent cells contain a search string.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition
MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
SEARCH() Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)
Related posts:
- Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
- Filter unique distinct text values in a range using “contain” condition in excel
- Filter unique distinct text values using “begins with” criterion in a range using array formula in excel
- Filter unique text values in a range using “contain” condition in excel
- Filter unique distinct values using “contain” condition of a column in excel
- Filter unique values from a range using array formula in excel
- Sum adjacent values using multiple lookup text values in a column in excel
- Create a unique distinct text list from a range containing both numerical and text values in excel
- Extract a unique distinct list by matching items that meet a criterion in excel
- Filter unique text values from a range containing both numerical and text values in excel
July 26th, 2010 at 6:31 pm
can you do this with multiple search strings?
July 27th, 2010 at 4:33 pm
Arielle,
Yes!
Formula in cell C12:
=INDEX(Column_txt, MATCH(0, COUNTIF($C$11:C11, Column_txt)*(ISNUMBER(SEARCH($C$8, Search_column))+ISNUMBER(SEARCH($C$9, Search_column))), 0)) + CTRL + SHIFT + ENTER
Copy cell C12 and paste it down as far as needed.
The second search string is in cell C9.
July 27th, 2010 at 5:13 pm
Okay I need to do this with text that searches cells with text separated by commas.
ex. search string: fin
search string: pro
search string: crm
cell A1: fin, pro, crm
cell A2: pro, crm
will the formula work for that so that it displays the cell that contains all three strings?
July 28th, 2010 at 9:25 am
Arielle,
No, try this formula in cell C12:
=INDEX(Column_txt, MATCH(0, COUNTIF($C$11:C11, Column_txt)*(SEARCH($C$8, Search_column)*SEARCH($C$9, Search_column)*SEARCH($C$10, Search_column)), 0)) + CTRL + SHIFT + ENTER
Copy cell C12 and paste it down as far as needed.
The search strings are in cell C8,C9 and C10.
The array formula creates a unique distinct list of all cells containing the value in cell C8,C9 and C10.
Thank you for commenting!
July 28th, 2010 at 3:07 pm
Great it worked!! I changed the search function to find because the program I am using doesn't support the excel search function and it works great!!
Thanks again!!!!!
July 28th, 2010 at 3:08 pm
Arielle,
I am happy it worked!
July 28th, 2010 at 4:18 pm
is there a way to do this without using an array?
July 28th, 2010 at 8:54 pm
well without using the ctrl + shift + enter part. even if I had to break up the formula into 2 or 3 different cells then multiply/add or whatever them together in another cell.
July 28th, 2010 at 9:52 pm
Arielle,
In cell E2:
=(SEARCH($C$8, B2)*SEARCH($C$9, B2))*SEARCH($C$10, B2)) + ENTER. Copy cell E2 and paste it down as far as needed.
In cell F2:
=IF(ISNUMBER(E2)*(MATCH(C2;$C$2:$C$6;0)=ROW()-1);C2;"") + ENTER.
Copy cell F2 and paste it down as far as needed.
The list you get in column F contains a lot of blank rows.
July 28th, 2010 at 10:00 pm
when i evaluate the formula without the crtl shift enter, it fails when it gets to the match function, so maybe there is a way around that without the ctrl shift enter. I included all of the supported functions in xcelsius, which makes it difficult to create a formula to by pass all these issues. I can even use a formula like http://www.get-digital-help.com/2009/08/24/search-and-display-all-cells-that-contain-all-search-strings-in-excel/ to find its location or its cell number and then i can lookup the other value that i really need but without all of the unsupported functions. i really hope you can help!!!
ive used your functions for other projects but i was able to bypass the unsupported functions and the array issue by using a macro that copied the values and pasted them on another section of the worksheet and then referenced to the pasted cells. i cant do that with this project because of this search that you gave me the previous formula for. (so basically the formula you gave me works but i just need to avoid the ctrl shift enter so that everything will display in xcelsius)
ABS ACOS ACOSH ADDRESS AND ASIN
ASINH ASSIGN ATAN ATAN2 ATANH AVEDEV
AVERAGE AVERAGEA AVERAGEIF BETADIST CEILING CHAR
CHOOSE CODE COLUMN COLUMNS COMBIN CONCATENATE
CORREL COS COSH COUNT COUNTA COUNTBLANK
COUNTIF COVAR DATE DATEVALUE DAVERAGE DAY
DAYS360 DB DCOUNT DCOUNTA DDB DEGREES
DEVSQ DGET DIVIDE DMAX DMIN DOLLAR
DPRODUCT DSTDEV DSSTDEVP DSUM DVAR DVARP
EDATE EFFECT EOMONTH EVEN EXACT EXP
EXPONDIST FACT FACTDOUBLE FALSE FIND FISHER
FISHERINV FIXED FLOOR FORECAST FV GE
GEOMEAN GT HARMEAN HLOOKUP HOUR IF
INDEX INDIRECT INT INTERCEPT IPMT
IRR ISBLANK ISEVEN ISLOGICAL
ISNA ISNONTEXT ISNUMBER ISODD ISTEXT KURT
LARGE LE LEFT LEN LN LOG
LOG10 LOOKUP LOWER MATCH MAX MAXA
MEDIAN MID MIN MINA MINUS MINUTE
MIRR MOD MODE MONTH N NE
NETWORKDAYS NORMDIST NORMINV NORMSINV NOT NOW
NPER NPV OFFSET OR PEARSON PERCENTILE
PERCENTRANK PERMUT PI PMT POWER PPMT
PRODUCT PV QUARTILE QUOTIENT RADIANS RAND
RANDBETWEEN RANGE_COLON RANK RATE REPLACE REPT
RIGHT ROUND ROUNDDOWN ROUNDUP
RSQ SECOND SIGN SIN SINH SLN
SLOPE SMALL SQRT STANDARDIZE STDEV STDEVA
STDEVP SUBTOTAL SUM SUMIF SUMPRODUCT SUMSQ
SUMX2MY2 SUMX2PY2 SUMXMY2 SYD TAN TANH
TEXT TIME TIMEVALUE TODAY TRUE TRUNC
TYPE VALUE VAR VARA VARP VARPA
VDB VLOOKUP WEEKDAY WEEKNUM WORKDAY YEAR
YEARFRAC
July 29th, 2010 at 10:52 am
Ariel,
Do you get an error or is the cell blank in your spreadsheet?
Are the above functions unsupported?
July 29th, 2010 at 2:33 pm
Hi!
sorry about the confusion. I sent you the 2nd message and I didn't realize that you had sent me another formula to try. when i sent you the second message i was talking about the formula you gave me =INDEX(Column_txt, MATCH(0, COUNTIF($C$11:C11, Column_txt)*(SEARCH($C$8, Search_column)*SEARCH($C$9, Search_column)*SEARCH($C$10, Search_column)), 0)) and i did it without the ctrl shift enter and when i evaluated the formula it failed at the match function. when i did the formula you just sent to me In cell E2:
=(SEARCH($C$8, B2)*SEARCH($C$9, B2))*SEARCH($C$10, B2)) + ENTER. Copy cell E2 and paste it down as far as needed.
In cell F2:
=IF(ISNUMBER(E2)*(MATCH(C2;$C$2:$C$6;0)=ROW()-1);C2;"") + ENTER.
Copy cell F2 and paste it down as far as needed.
it gives me a value error in cell E2 and blanks in F2.
all the formulas that i listed above are the supported functions so let me know if there is any way possible to achieve what i want by using those functions.
thanks again for all of your help and im sorry about how confusing i am!
July 30th, 2010 at 10:11 pm
Arielle,
The formula in cell E2 returns an error if any or all of the search strings in C8, C9 and C10 are not found. If all of the search strings are found the formula returns a number.
The formula in cell F2 returns a unique distinct list depending on the outcome of the formula in cell E2.
Don´t forget to copy the formulas down as far as needed.
August 11th, 2010 at 8:06 pm
Hi Oscar! Here is what I have to do and you can tell me if it is possible. I have to search for a cell in a table and then display the column title.
search value in cell e1: AA
table in cells A1:C6
x y z
BB CC DD
AA GG AA
CC EE
FF HH
II
then the values to be displayed from the search would be:
x in one cell and z in the next cell. the display of the search values can either be in a row-(g1: x h1: z) or a column-(g1: x g2: z)
Let me know if this type of search is possible, thanks!
August 11th, 2010 at 8:11 pm
apologies, my table did not display properly-
A1:x B1:y C1:z
A2:BB B2:CC C2:DD
A3:AA B3:GG C3:AA
A4:CC B4:BLANK C4:EE
A5:FF B5:BLANK C5:HH
A6:BLANK B6:BLANK C6:II
August 16th, 2010 at 7:10 pm
Arielle,
see this post: http://www.get-digital-help.com/2010/08/16/search-for-a-cell-in-a-table-and-then-display-the-column-title-in-excel/