CODEWITHSUNDEEP
excel
user5844810
user5844810

Reputation:

Select columns based on multiple values in Excel

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 .

Upvotes: 0

Views: 139

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34230

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

Upvotes: 0

Tim Wilkinson
Tim Wilkinson

Reputation: 3801

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

Upvotes: 1

Related Questions