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

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

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

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

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

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

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,SMALL(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

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

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,SMALL(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!
>
>> Hi Domenic,
>>
>[quoted text clipped - 10 lines]
>> Cheers,
>> Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200607/1