0

Hi please see my excel structure

id  value
1   e
2   rrr
3   ttt
4  ghy

How can i select all rows with have id 1,2,4 .

In

Data->filter

i can select id by check box . But it is long process if i have 1000 or 2000 records . So i need to give id separated by comma or any special character .

Is there any method to do this ?

Please help .

Improve this question
1
  • Not exactly related to programming. Which and How many values you want to select? If it's a number field (which looks like it is) you can also use the number filters option. Which version of Excel are you using? Commented May 2, 2016 at 12:14

2 Answers 2

Reset to default
1 
Dim MyArray As Variant

MyArray = Split(Range("C1"), ",")
    ActiveSheet.Range("$A$1:$B$5").AutoFilter Field:=1, Criteria1:=Array(MyArray), Operator:=xlFilterValues
End Sub

Using this, enter the values you want split by a comma in cell C1 for example 1,2,3,12,16

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I don't want to make an extra column in excel , I want to select all details that have id =1,or 2 or 3 . Just like data filter in excell . If we filter with id=1 then i get all cells and columns of id=1 .
So you want to filter your data by certain values? You are describing exactly what the Data Filter is built for.
yes . But in data filter i need to select order id by clicking corresponding check box . But what i do if i have 5000 id and i need to select only data for given 200 id . It is very difficult to select each checkbox one by one for a large record .
Ah ok, then the best solution to your problem would be as per my solution, have a helper cell somewhere, even in a different sheet if needs be, where you can add all the ID's you need separated by a comma. They are basically your two options.
0

You could also use an array formula e.g. if the criteria are in C2 in the form:-

,1,2,4,

enter the following in D2 using CtrlShiftEnter

=IFERROR(INDEX($B$1:$B$1000,SMALL(IF(ISNUMBER(FIND(","&$A$1:A$1000&",",$C$2)),ROW($A$1:A$1000)),ROWS(D1:D$1))),"")

and pull it down. Usual warnings apply that it could be slow if used with many rows.

enter image description here

Improve this answer

3 Comments

I don't want to make an extra column in excel , I want to select all details that have id =1,or 2 or 3 . Just like data filter in excell . If we filter with id=1 then i get all cells and columns of id=1 .
OK, well you could put the results in a separate sheet, but looks as if the VBA solution is better for you.
in data filter i need to select order id by clicking corresponding check box . But what i do if i have 5000 id and i need to select only data for given 200 id . It is very difficult to select each checkbox one by one for a large record

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.