Search in Excel

[Vishaal]

Hi,

Thanks in advance.

May i search the following value/text

13452
3009
12744

in given sheet

Excel 2010 32 bit

	A​	B​
1​
2​	10089​
3​	3363​
4​	13452​
5​	3009​
6​	12744​
7​	12744​
8​	56000​
9​	56000​
10​	66080​
11​	56000​
12​	10620​
13​	17700​
14​	10620​
15​	17700​
16​	10620​
17​	5310​
18​	7080​
19​	7080​
20​	5310​
21​	10620​
22​	3363​
23​	13452​
24​	13452​
25​	3009​
26​	12744​
27​	56000​
28​	56000​
29​	66080​
30​	56000​
31​	10620​
32​	17700​
33​	10620​
34​	17700​
35​	10620​
36​	5310​
37​	7080​
38​	7080​
39​	5310​
40​	10620​

Sheet: Sheet1

pls help

 

[tyija1995]

Hi,

In col B you can try:

B2:
=--ISNUMBER(SEARCH(A2,"13452.3009.12744"))

OR if you put your list of numbers you want to search in to a named range "list" then:
=COUNTIF(list,A2)

 

[Vishaal]

Hi tyija1995 Ji

its working

thanks​

 

[Vishaal]

There is one problem in code

Its searching the any one amount which i have given

But i need the same

13452
3009
12744

Not any of them, Sequence also

 

[Vishaal]

I mean i want the same sequences (whole amount )

13452
3009
12744

 

[salim hasan]

try this array formula

=IF(ROWS($A$1:A1)>COUNT($C$1:$C$3),"",INDEX($A$1:$A$11,SMALL(IF(COUNTIF($C$1:$C$3,$A$1:$A$11),ROW($A$1:$A$11)-ROW($A$1)+1),ROWS($A$1:A1))))

Book1
	A	B	C	D	E	F	G
1	5733		13452				sum
2	6736		3009		13452		29205
3	13452		12744		3009
4	6518				12744
5	12811
6	3009
7	10700
8	10646
9	12363
10	12744
11	7076
Sheet1

Cell Formulas
Range	Formula
G2	=SUM($E$2:$E$10)
E2	{=IF(ROWS($A$1:A1)>COUNT($C$1:$C$3),"",INDEX($A$1:$A$11,SMALL(IF(COUNTIF($C$1:$C$3,$A$1:$A$11),ROW($A$1:$A$11)-ROW($A$1)+1),ROWS($A$1:A1))))}
E3	{=IF(ROWS($A$1:A2)>COUNT($C$1:$C$3),"",INDEX($A$1:$A$11,SMALL(IF(COUNTIF($C$1:$C$3,$A$1:$A$11),ROW($A$1:$A$11)-ROW($A$1)+1),ROWS($A$1:A2))))}
E4	{=IF(ROWS($A$1:A3)>COUNT($C$1:$C$3),"",INDEX($A$1:$A$11,SMALL(IF(COUNTIF($C$1:$C$3,$A$1:$A$11),ROW($A$1:$A$11)-ROW($A$1)+1),ROWS($A$1:A3))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Vishaal]

No,

In your sheet you are searching the three number (anyone of them)

but i want this group, sequence

ok let try to understand by text not value

in this sheet

Excel 2010 32 bit

	A​	B​
1​
2​	Rozi
3​	John
4​	Lakhn
5​	Peter
6​	Javed
7​	Rozi
8​	Salim
9​	Javed
10​	Rajan
11​	Salim
12​	Javed
13​	Rozi
14​	Salim
15​	Javed
16​

Sheet: Sheet1

I am searching
Excel 2010 32 bit

	A​
7​	Rozi
8​	Salim
9​	Javed

Sheet: Sheet1

In above sheet i want to check when Rozi, Salim and Javed come together, answer is only two times they come together and i have highlighted that cells when they come together

Excel 2010 32 bit

	A​	B​	C​	D​
1​
2​	Rozi
3​	John
4​	Lakhn
5​	Peter
6​	Javed
7​	Rozi	After Five Days​	One Time​
8​	Salim	​	​
9​	Javed	​	​
10​	Rajan
11​	Salim
12​	Javed
13​	Rozi	After Four Days​	Second Time​
14​	Salim	​	​
15​	Javed	​	​
16​

Sheet: Sheet1

 

[tyija1995]

How about

B2:
=IF(SUMPRODUCT(--({"Rozi";"Salim";"Javed"}=A2:A4))=3,"FLAG","")

Then drag it down

	A	B
1
2	Rozi
3	John
4	Lakhn
5	Peter
6	Javed
7	Rozi	FLAG
8	Salim
9	Javed
10	Rajan
11	Salim
12	Javed
13	Rozi	FLAG
14	Salim
15	Javed

<tbody>
</tbody>

 

[Vishaal]

Thanks tyija1995 Ji,

Its working fine for me

Only one thing missing, After how many days/rows

 

[salim hasan]

How About This formula

=IF(OR(COLUMNS($A$1:A1)>COUNTIF($A$2:$A$13,$D2),$D2=""),"",INDEX($B$2:$B$13,SMALL(IF(COUNTIF($D2,$A$2:$A$13),ROW($B$2:$B$13)-ROW($B$2)+1),COLUMNS($A$1:A1))))

Book1
	A	B	C	D	E	F	G
2	A	1 :Time A		A	1 :Time A	2 :Time A
3	B	1 :Time B		B	1 :Time B	2 :Time B
4	C	1 :Time C		E	1 :Time E	2 :Time E	3 :Time E
5	D	1 :Time D
6	E	1 :Time E
7	A	2 :Time A
8	B	2 :Time B
9	E	2 :Time E
10	D	2 :Time D
11	M	1 :Time M
12	E	3 :Time E
13	O	1 :Time O
Sheet1

Cell Formulas
Range	Formula
B2	=COUNTIF($A$2:A2,A2)&" :Time "&A2
E2	{=IF(OR(COLUMNS($A$1:A1)>COUNTIF($A$2:$A$13,$D2),$D2=""),"",INDEX($B$2:$B$13,SMALL(IF(COUNTIF($D2,$A$2:$A$13),ROW($B$2:$B$13)-ROW($B$2)+1),COLUMNS($A$1:A1))))}
F2	{=IF(OR(COLUMNS($A$1:B1)>COUNTIF($A$2:$A$13,$D2),$D2=""),"",INDEX($B$2:$B$13,SMALL(IF(COUNTIF($D2,$A$2:$A$13),ROW($B$2:$B$13)-ROW($B$2)+1),COLUMNS($A$1:B1))))}
G2	{=IF(OR(COLUMNS($A$1:C1)>COUNTIF($A$2:$A$13,$D2),$D2=""),"",INDEX($B$2:$B$13,SMALL(IF(COUNTIF($D2,$A$2:$A$13),ROW($B$2:$B$13)-ROW($B$2)+1),COLUMNS($A$1:C1))))}
Press CTRL+SHIFT+ENTER to enter array formulas.