Filter spreadsheet based on a list

[rogueakula]

So I have a spreadsheet that has over a A HUNDRED AND FIFTY THOUSAND entries that makes it almost unusable. I didn't make it, but I am required to filter out everything except what belongs to me. I am looking for a way that I can filter based on a list. I have 59 names that is need from this spreadsheet and, if there is a way, copy the rows to another sheet. I would rather not have to search for each name individually and move to another sheet. I have been trying with advanced filter but I haven' found a way yet. Any ideas? I hope this makes sense. Thank you so much for your help!

-Josh

 

[Fluff]

Ok, another option

Sub FilterCopy()
   Dim Ary As Variant
   
   With Sheets("Data")
      Ary = Application.Transpose(.Range("A2", .Range("A" & Rows.Count).End(xlUp)))
   End With
   With Sheets("Sheet1")
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1", .UsedRange).AutoFilter 5, Ary, xlFilterValues
      .UsedRange.Offset(1).SpecialCells(xlVisible).Copy Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
      .AutoFilterMode = False
   End With
End Sub

Where you have a list of names in A2 downwards on a sheet called Data

 

[rogueakula]

Ok, another option

Sub FilterCopy()
   Dim Ary As Variant
   
   With Sheets("Data")
      Ary = Application.Transpose(.Range("A2", .Range("A" & Rows.Count).End(xlUp)))
   End With
   With Sheets("Sheet1")
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1", .UsedRange).AutoFilter 5, Ary, xlFilterValues
      .UsedRange.Offset(1).SpecialCells(xlVisible).Copy Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
      .AutoFilterMode = False
   End With
End Sub

Where you have a list of names in A2 downwards on a sheet called Data

Fluff,

Does this only look in column A for a match? If my row of names is in column E on sheet1 would it look like:
Range("E1", .UsedRange).AutoFilter 5, Ary, xlFilterValues
UsedRange.Offset(1).SpecialCells(xlVisible).Copy Sheets("sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1)

Thank you so much for your help!

 

[My Aswer Is This]

My Answer Is This,

I don't necessarily need a script, I just want something to weed through all the crap that I don't want to look at. So in my mind, I have a list of my 59 names in another sheet or anywhere really. A script or a filter looks at these 59 names and then copies any row in sheet 1 that has one of those names in column E to sheet2.

-Josh

I believe Fluff now has a script that will work for you. I will move on to help someone else.

 

[Fluff]

Fluff,

Does this only look in column A for a match?

Nope, it looks in col E (assuming you have data in col A).

.Range("A1", .UsedRange).AutoFilter [COLOR=#ff0000]5[/COLOR], Ary, xlFilterValues

The 5 (in red) says to filter on the 5th column

 

[rogueakula]

Fluff! You are 'effin brilliant! That works like a champ in my test spreadsheet! I am going to copy it over to my main one and give it a whril! Thank you so much for your help!

 

[Fluff]

Glad we could help & thanks for the feedback.
If you get any probs with your real data, just shout

 

[rogueakula]

Fluff,

No issues at all! It worked amazingly well and so much faster than I expected! Thank you again! There are other teams here that are going to need the same thing. With your permission, I would love to share this with them.

-Josh

 

[Fluff]

With your permission, I would love to share this with them.

Feel free to do that.
That's what we're here for