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

  • Thread starter Sam via OfficeKB.com
  • Start date
S

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
 
B

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
 
S

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

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?
 
S

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

[quoted text clipped - 33 lines]
Thanks
Sam
 
D

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!
 
B

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
 
S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top