• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula Help

aparvez007

aparvez007

Member
Hi All,


I have 4500 row in one column (text and number) and I want to find all the row which Contain 30000.. (e.g. 30000278, 3000001,3000009).


Thanks,

Pavi
 
SELECT THE SAME COLUMN

then press ctrl + F

then put the value 30000

then select the match case option only

remove the tick from the match entire cell contents...

then press find all....


Regards

CA Mahaveer Somani
 
create filter, from drop down menu select text filters and then begin with, put your desire number such as 300000... and enjoy.
 
do you have related info in the surronding columns?


any by "find all the rows" do you mean, see them (formatting), just see those rows (filter), extract those row/cells to a new location?


please give a little more info on what you want the result to be/look like as this will drive the correct solution.
 
Hi Jason/Mahaveer,


I have below data in column A and I want formula to get value started with 300000 and for others blank or zero.

20

21

22

23

24

25

26

27

28

29

30

32

4954100

4954200

4954300

4954400

30000001

30000001

30000004

30000029

30000034

30000036

30000036

30000037

30000038

30000270

30000272

30000274

30000278

40800344

80000933

80001206

80002004


Regards,

Pavi
 
Hi Pavi ,


If you just want the row numbers which have the searched for number portion within the cell , you can use this :


=SMALL(IF(ISNUMBER(FIND(30000,VALUE(Data_Range))),ROW(Data_Range)-MIN(ROW(Data_Range))+1),ROW(A1))


entered as an array formula , using CTRL SHIFT ENTER.


Data_Range is your range of numbers , e.g. A6:A500.


If you want the numbers themselves , you can use :


=INDEX(Data_Range,SMALL(IF(ISNUMBER(FIND(30000,VALUE(Data_Range))),ROW(Data_Range)-MIN(ROW(Data_Range))+1),ROW(A1)))


again entered as an array formula , using CTRL SHIFT ENTER.


I have assumed that the segment 30000 will not occur in the middle of a number e.g. 803000051 , in which case , this will also be taken as meeting the criteria. If this can happen , then the formula will need to be changed.


Narayan
 
Hi Narayan,


Below formula working but I want same like vlookup if that no. not begining with 300000 then it should be blank or zero and below formula giving sorted value.


=INDEX(Data_Range,SMALL(IF(ISNUMBER(FIND(30000,VALUE(Data_Range))),ROW(Data_Range)-MIN(ROW(Data_Range))+1),ROW(A2)))


Regards,

Pavi
 
i assume your data are starting from A1 then put the following formula in B1 and pull down up to your data...


=IF(LEFT(A1,5)="30000",A1,"")


hope it will work


Regards

CA Mahaveer Somani
 
You must log in or register to reply here.
Back
Top