CODEWITHSUNDEEP
excelexcel-formulaexcel-2010vba
Evil Washing Machine
Evil Washing Machine

Reputation: 1341

How to filter a list with the contents of another list?

I have a list of ID's (about 140) which I want to query a big (50k+) spreadsheet. This is not a simple 'does the item exist on the list'; I want to view the details stored in the big spreadsheet as well. The simplest way is to use the filter and key in the ID's by hand, however the list of ID's is quite large and this would be a very painful process to type in 140 ID's one at a time. Is there a way I can use the advanced filter or a macro to help me filter the items I want?

Note: I don't want to use VBA as that takes quite a lot of time. I have since learned that Pivot Tables are the best solution. I was wondering if there are any formulas or macros which can do this even more easily.

Upvotes: 2

Views: 8973

Answers (2)

CRondao
CRondao

Reputation: 1903

Actually yes, you can use advanced filter to do that:

enter image description here

In the example F1:F6 contain the possible values (in your case the range containing 140 values). The criteria range is A8:C9 and in C9 you just write the formula:

=COUNTIF($F$1:$F$6;A13)>0

A13 is the cell that contains your first ID in your data range.

You should write this in Advanced Filter:

enter image description here

Dont forget the C9 formula.

Upvotes: 1

user2140261
user2140261

Reputation: 8003

If you wish to use VBA and not add a column you can use the following: 

Sub Filter()
Dim Criteria As Variant
Criteria = Worksheets("Sheet1").Range("A1:A140")

Worksheets("Sheet2").Range("$A$1:$B$10").AutoFilter Field:=1, Criteria1:=Criteria, Operator:=xlFilterValues
End Sub

NOTE: The above will only work if your id's are not numeric only, if they are you can use the following: 

Sub Filter2()
Dim tempCriteria As Variant
Dim i As Long
Dim Criteria() As String

tempCriteria = Worksheets("Sheet1").Range("A1:A140")

ReDim Criteria(1 To UBound(tempCriteria))

For i = 1 To UBound(tempCriteria)
    Criteria(i) = CStr(tempCriteria(i, 1))
Next

Worksheets("Sheet2").Range("$A$1:$B$10").AutoFilter Field:=1, Criteria1:=Criteria, Operator:=xlFilterValues
End Sub

In both above codes change Worksheets("Sheet1").Range("A1:A140") to the range that holds your 140 ID's, and Worksheets("Sheet2").Range("$A$1:$B$10") to the range that youre query is in.

Upvotes: 3

Related Questions