If/Find/Search??

[Fazila]

I'm not really sure how to do this or even if there is a way it can be done (but I'm really hoping it can).

I need to find the days when a specific subject is taught. The subject name will always contain the characters 11a/En which will be followed by 1, 2, 3, 4 etc. At the moment my table looks something like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]AMon1[/TD]
[TD]AMon2[/TD]
[TD]AMon3[/TD]
[TD]ATues1[/TD]
[/TR]
[TR]
[TD]AJA
[/TD]
[TD]11a/Ma1[/TD]
[TD]7b/Sc1[/TD]
[TD]11a/En1[/TD]
[TD]8b/Pe1[/TD]
[/TR]
[TR]
[TD]HGE[/TD]
[TD]8c/Te1[/TD]
[TD]11a/En2[/TD]
[TD]8b/Re1[/TD]
[TD]7a/En1[/TD]
[/TR]
</tbody>[/TABLE]

So basically I need a formula that will tell that an 11a/En class will be taught at AMon2 and AMon3 - is there any way this can be done?

Thanks

 

[jtakw]

Hi,

You didn't specify how you want the results, this is one way to identify the "days" for the subject you're searching:

Excel 2010
	A	B	C	D	E
1		AMon1	AMon2	AMon3	ATues1
2	AJA	11a/Ma1	7b/Sc1	11a/En1	8b/Pe1
3	HGE	8c/Te1	11a/En2	8b/Re1	7a/En1
4			AMon2	AMon3
5	11a/En		AMon2	AMon3
6	11a/Ma	AMon1
7	8b/Pe				ATues1
Sheet14

Cell Formulas
Range	Formula
B4	=IF(ISNUMBER(LOOKUP(2,1/SEARCH("11a/En*",B2:B3))),B1,"")
B5	=IF(ISNUMBER(LOOKUP(2,1/SEARCH($A5&"*",B$2:B$3))),B$1,"")

Use B4 formula where the Subject (11a/En) is hardcoded in the formula, Or
Use B5 formula if you might have Other Subjects you want to search for, enter the subject name of your choice, in my sample, A5:A7

B4 formula copied across.
B5 formula copied down and across.

 

[jtakw]

Hi,

Just realized, we don't need the 1/ within the above formulas, those can be taken out like so:

Excel 2010
	A	B	C	D	E
1		AMon1	AMon2	AMon3	ATues1
2	AJA	11a/Ma1	7b/Sc1	11a/En1	8b/Pe1
3	HGE	8c/Te1	11a/En2	8b/Re1	7a/En1
4			AMon2	AMon3
5	11a/En		AMon2	AMon3
6	11a/Ma	AMon1
7	8b/Pe				ATues1
Sheet14

Cell Formulas
Range	Formula
B4	=IF(ISNUMBER(LOOKUP(2,SEARCH("11a/En*",B2:B3))),B1,"")
B5	=IF(ISNUMBER(LOOKUP(2,SEARCH($A5&"*",B$2:B$3))),B$1,"")

 

[Aladin Akyurek]

I read the request a bit differently...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][/tr][tr][td]

1​

[/td][td][/td][td]AMon1[/td][td]AMon2[/td][td]AMon3[/td][td]ATues1[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]AJA[/td][td]11a/Ma1[/td][td]7b/Sc1[/td][td]11a/En1[/td][td]8b/Pe1[/td][td][/td][td]11a/En[/td][td]AMon2[/td][td]AMon3[/td][td][/td][/tr]
[tr][td]

3​

[/td][td]HGE[/td][td]8c/Te1[/td][td]11a/En2[/td][td]8b/Re1[/td][td]7a/En1[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In H2 control+shift+enter, not just enter, and copy across:

=IFERROR(INDEX($B$1:$E$1,SMALL(IF(ISNUMBER(SEARCH("|"&$G2,"|"&$B$2:$E$3)),COLUMN($B$1:$E$1)-COLUMN($B$1)+1),COLUMNS($H2:H2))),"")

 

[Fazila]

The formula works but there would be too many blanks in between i.e. given the example above the result would be

column G column H column I
[blank] [blank] AMon3

But thanks for offering a solution

 

[Fazila]

This works but the problem would be a lot of duplication. I have found a report that will provide me with the information I am looking for but again thanks for the solution as always. I don't suppose you offer any excel courses or could recommend any?

Thanks again.

 

[Aladin Akyurek]

The formula works but there would be too many blanks in between i.e. given the example above the result would be

column G column H column I
[blank] [blank] AMon3

But thanks for offering a solution

Th formula does not create blanks at all, if that's what you mean.

 

[Fazila]

Th formula does not create blanks at all, if that's what you mean.

Not so much a blank but as the formula returns no value the cell is blank until it comes across a match. Sorry my words probably don't make much sense but the result I got was jtakw's formula was:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AMon:3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]AMon:2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
but I would have to continue to drag the formula the original data is from B to BR which would mean the formula would have to go across a lot of columns to get the information I need.

 

[Aladin Akyurek]

Not so much a blank but as the formula returns no value the cell is blank until it comes across a match. Sorry my words probably don't make much sense but the result I got was jtakw's formula was:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AMon:3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]AMon:2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
but I would have to continue to drag the formula the original data is from B to BR which would mean the formula would have to go across a lot of columns to get the information I need.

Are you wanting a vertical list perhaps?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr][tr][td]

1​

[/td][td][/td][td]AMon1[/td][td]AMon2[/td][td]AMon3[/td][td]ATues1[/td][td][/td][td]11a/En[/td][/tr]
[tr][td]

2​

[/td][td]AJA[/td][td]11a/Ma1[/td][td]7b/Sc1[/td][td="bgcolor:#00B0F0"]11a/En1[/td][td]8b/Pe1[/td][td][/td][td]AMon2[/td][/tr]
[tr][td]

3​

[/td][td]HGE[/td][td]8c/Te1[/td][td="bgcolor:#00B0F0"]11a/En2[/td][td]8b/Re1[/td][td]7a/En1[/td][td][/td][td]AMon3[/td][/tr]
[tr][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In G2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$1:$E$1,SMALL(IF(ISNUMBER(SEARCH("|"&$G$1,"|"&$B$2:$E$3)),COLUMN($B$1:$E$1)-COLUMN($B$1)+1),ROWS($G$2:G2))),"")