Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search Multiple columns for criterion asterisk (*) and Return Numeric Label
Hi All,
I would like to search multiple columns for criterion asterisk (*) and Return across a single Row Multiple Numeric Labels that MATCH the criterion on their respective Row. 1. The criterion is an asterisk * (multiplication sign) housed in cell A2 2. The data to be searched for criterion is housed in columns U2:Y60 3. The Numeric Labels to be returned are housed in column T2:T60 4. To search for criterion "~**" (Tilde ** in quotation marks) Sample Data Layout: Col"T" Col"U" Col"V" Col"W" Col"X" Col"Y" 1 2 3 X 4 ** 5 6 ** 7 * ** 8 X 9 ** 10 * 11 * * 12 * 13 14 ** 15 X Expected Results: Numeric Label has criterion asterisk on its row. Return Numeric Labels across a row : 4 6 7 9 10 11 12 14 Thanks Sam -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search Multiple columns for criterion asterisk (*) and Return Numeric Label
Hi!
Try this: (based on your sample size and data) Array entered: =INDEX($T$2:$T$16,SMALL(IF(MMULT(--($U$2:$Y$16=$A$2),TRANSPOSE(COLUMN($U$2:$Y$16)^0)) 0,ROW(T$2:T$16)-ROW(T$2)+1),COLUMNS($A:A))) If you want an error trap: =IF(COLUMNS($A:A)<=SUM(--(MMULT(--($U$2:$Y$16=$A$2),TRANSPOSE(COLUMN($U$2:$Y$16)^0)) 0)),INDEX($T$2:$T$16,SMALL(IF(MMULT(--($U$2:$Y$16=$A$2),TRANSPOSE(COLUMN($U$2:$Y$16)^0)) 0,ROW(T$2:T$16)-ROW(T$2)+1),COLUMNS($A:A))),"") Since it may be possible for every row in the range to contain the * you have to copy across the equivalent number of cells. Biff "Sam via OfficeKB.com" <u4102@uwe wrote in message news:6334a3dc91a4e@uwe... Hi All, I would like to search multiple columns for criterion asterisk (*) and Return across a single Row Multiple Numeric Labels that MATCH the criterion on their respective Row. 1. The criterion is an asterisk * (multiplication sign) housed in cell A2 2. The data to be searched for criterion is housed in columns U2:Y60 3. The Numeric Labels to be returned are housed in column T2:T60 4. To search for criterion "~**" (Tilde ** in quotation marks) Sample Data Layout: Col"T" Col"U" Col"V" Col"W" Col"X" Col"Y" 1 2 3 X 4 ** 5 6 ** 7 * ** 8 X 9 ** 10 * 11 * * 12 * 13 14 ** 15 X Expected Results: Numeric Label has criterion asterisk on its row. Return Numeric Labels across a row : 4 6 7 9 10 11 12 14 Thanks Sam -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search Multiple columns for criterion asterisk (*) and Return Numeric Label
Hi Biff,
Thank you for reply. I'm not sure why I do not get the Expected Results from your Formulae. I copied it direct from your post and entered with Ctrl+Shift+Enter . Did you get the Expected Results from the Sample Data. Expected Results: Numeric Label has criterion asterisk on its row. Return Numeric Labels across a row : 4 6 7 9 10 11 12 14 Further assistance much appreciated. Cheers, Sam Biff wrote: Hi! Try this: (based on your sample size and data) Array entered: =INDEX($T$2:$T$16,SMALL(IF(MMULT(--($U$2:$Y$16=$A$2),TRANSPOSE(COLUMN($U$2:$Y$16)^0)) 0,ROW(T$2:T$16)-ROW(T$2)+1),COLUMNS($A:A))) If you want an error trap: =IF(COLUMNS($A:A)<=SUM(--(MMULT(--($U$2:$Y$16=$A$2),TRANSPOSE(COLUMN($U$2:$Y$16)^0)) 0)),INDEX($T$2:$T$16,SMALL(IF(MMULT(--($U$2:$Y$16=$A$2),TRANSPOSE(COLUMN($U$2:$Y$16)^0)) 0,ROW(T$2:T$16)-ROW(T$2)+1),COLUMNS($A:A))),"") Since it may be possible for every row in the range to contain the * you have to copy across the equivalent number of cells. Biff -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200607/1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search Multiple columns for criterion asterisk (*) and Return Numeric Label
It looks like some cells contain one asterisk and other cells contain
two asterisks. Is this correct? If so, does this mean that while your criterion is one asterisk that you'd like to return the numeric labels for any row whose cells contain one or two asterisks? In article <6334a3dc91a4e@uwe, "Sam via OfficeKB.com" <u4102@uwe wrote: Hi All, I would like to search multiple columns for criterion asterisk (*) and Return across a single Row Multiple Numeric Labels that MATCH the criterion on their respective Row. 1. The criterion is an asterisk * (multiplication sign) housed in cell A2 2. The data to be searched for criterion is housed in columns U2:Y60 3. The Numeric Labels to be returned are housed in column T2:T60 4. To search for criterion "~**" (Tilde ** in quotation marks) Sample Data Layout: Col"T" Col"U" Col"V" Col"W" Col"X" Col"Y" 1 2 3 X 4 ** 5 6 ** 7 * ** 8 X 9 ** 10 * 11 * * 12 * 13 14 ** 15 X Expected Results: Numeric Label has criterion asterisk on its row. Return Numeric Labels across a row : 4 6 7 9 10 11 12 14 Thanks Sam |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search Multiple columns for criterion asterisk (*) and Return Numeric Label
Hi Domenic,
Domenic wrote: It looks like some cells contain one asterisk and other cells contain two asterisks. Is this correct? Yes If so, does this mean that while your criterion is one asterisk that you'd like to return the numeric labels for any row whose cells contain one or two asterisks? Yes Sorry, I did not make that clear. I thought using this: 4. To search for criterion "~**" (Tilde ** in quotation marks) as my criterion would capture multiple asteriks in a cell. Cheers, Sam Hi All, [quoted text clipped - 33 lines] Thanks Sam -- Message posted via http://www.officekb.com |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search Multiple columns for criterion asterisk (*) and Return Numeric Label
Hi Domenic,
Correction: Criterion is multiple asteriks 4. To search for criterion "~**" (Tilde ** in quotation marks) If so, does this mean that while your criterion is one asterisk that you'd like to return the numeric labels for any row whose cells contain one or two asterisks? Yes Cheers, Sam Sam wrote: Hi Domenic, It looks like some cells contain one asterisk and other cells contain two asterisks. Is this correct? Yes If so, does this mean that while your criterion is one asterisk that you'd like to return the numeric labels for any row whose cells contain one or two asterisks? Yes Sorry, I did not make that clear. I thought using this: 4. To search for criterion "~**" (Tilde ** in quotation marks) as my criterion would capture multiple asteriks in a cell. Cheers, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200607/1 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search Multiple columns for criterion asterisk (*) and Return Numeric Label
Biff's formula can be modified as follows...
B2, copied across: =IF(COLUMNS($B2:B2)<=SUM(--(MMULT(ISNUMBER(FIND($A2,$U$2:$Y$16))+0,TRANSP OSE(COLUMN($U$2:$Y$16)^0))0)),INDEX($T$2:$T$16,SM ALL(IF(MMULT(ISNUMBER(F IND($A2,$U$2:$Y$16))+0,TRANSPOSE(COLUMN($U$2:$Y$16 )^0)),ROW($T$2:$T$16)-R OW($T$2)+1),COLUMNS($B2:B2))),"") A few notes: 1) It assumes that A2 contains the criterion. 2) Any cell within U2:Y16 that contains the value in A2 within its text string will meet the criterion. 3) The function FIND is case-sensitive. 4) You may want to use a defined name for the MMULT part of the formula. Post back if you need help... Hope this helps! In article <633c0fc142fff@uwe, "Sam via OfficeKB.com" <u4102@uwe wrote: Hi Domenic, Correction: Criterion is multiple asteriks 4. To search for criterion "~**" (Tilde ** in quotation marks) If so, does this mean that while your criterion is one asterisk that you'd like to return the numeric labels for any row whose cells contain one or two asterisks? Yes Cheers, Sam |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search Multiple columns for criterion asterisk (*) and Return Numeric Label
I wrote the formula assuming that there was only one * per cell.
After I had posted I thought that this: 4. To search for criterion "~**" (Tilde ** in quotation marks) Might mean there may be multiple *'s per cell. If that was indeed the case a simple tweak could fix things and I see Domenic has taken care of that. Biff "Sam via OfficeKB.com" <u4102@uwe wrote in message news:633b2f48f3329@uwe... Hi Biff, Thank you for reply. I'm not sure why I do not get the Expected Results from your Formulae. I copied it direct from your post and entered with Ctrl+Shift+Enter . Did you get the Expected Results from the Sample Data. Expected Results: Numeric Label has criterion asterisk on its row. Return Numeric Labels across a row : 4 6 7 9 10 11 12 14 Further assistance much appreciated. Cheers, Sam Biff wrote: Hi! Try this: (based on your sample size and data) Array entered: =INDEX($T$2:$T$16,SMALL(IF(MMULT(--($U$2:$Y$16=$A$2),TRANSPOSE(COLUMN($U$2:$Y$16)^0)) 0,ROW(T$2:T$16)-ROW(T$2)+1),COLUMNS($A:A))) If you want an error trap: =IF(COLUMNS($A:A)<=SUM(--(MMULT(--($U$2:$Y$16=$A$2),TRANSPOSE(COLUMN($U$2:$Y$16)^0)) 0)),INDEX($T$2:$T$16,SMALL(IF(MMULT(--($U$2:$Y$16=$A$2),TRANSPOSE(COLUMN($U$2:$Y$16)^0)) 0,ROW(T$2:T$16)-ROW(T$2)+1),COLUMNS($A:A))),"") Since it may be possible for every row in the range to contain the * you have to copy across the equivalent number of cells. Biff -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200607/1 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search Multiple columns for criterion asterisk (*) and Return Numeric Label
Hi Domenic,
Thank you very much. That's Great! Biff, thank you also for your contribution. Domenic wrote: Biff's formula can be modified as follows... B2, copied across: =IF(COLUMNS($B2:B2)<=SUM(--(MMULT(ISNUMBER(FIND($A2,$U$2:$Y$16))+0,TRANSP OSE(COLUMN($U$2:$Y$16)^0))0)),INDEX($T$2:$T$16,S MALL(IF(MMULT(ISNUMBER(F IND($A2,$U$2:$Y$16))+0,TRANSPOSE(COLUMN($U$2:$Y$1 6)^0)),ROW($T$2:$T$16)-R OW($T$2)+1),COLUMNS($B2:B2))),"") A few notes: 1) It assumes that A2 contains the criterion. 2) Any cell within U2:Y16 that contains the value in A2 within its text string will meet the criterion. 3) The function FIND is case-sensitive. 4) You may want to use a defined name for the MMULT part of the formula. Post back if you need help... Hope this helps! Hi Domenic, [quoted text clipped - 10 lines] Cheers, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200607/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|