Search Multiple columns for criterion asterisk (*) and Return Numeric Label

[Sam via OfficeKB.com]

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

 

[Biff]

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]

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

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

 

[Domenic]

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?

 

[Sam via OfficeKB.com]

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

Hi All,

[quoted text clipped - 33 lines]

Thanks
Sam

 

[Sam via OfficeKB.com]

Hi Domenic,

Correction:
Criterion is multiple asteriks

4. To search for criterion "~**" (Tilde ** in quotation marks)

Yes

Cheers,
Sam

Hi Domenic,


Sorry, I did not make that clear.

 

[Domenic]

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!

 

[Biff]

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]

Hi Domenic,

Thank you very much. That's Great!

Biff, thank you also for your contribution.

Biff's formula can be modified as follows...

B2, copied across:

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