Need advance filtering to grab records that contain certain charac

[Guest]

I need to filter a large spreadsheet of over 1000 product/sku numbers to find
records that contain 200 product/sku numbers that i am interest in analyzing.
These are currently stored as a 9 digit number in the large spreadsheet.
What I need to do is to query on the first 6 digits of the 9 digits for the
200 product/skus that I am looking for. I'd like to extract the records and
save them to another workbook.

 

[Otto Moehrbach]

This macro should do that. As written, this macro assumes your long list is
in Column A and your short list is in Column B, both starting in row 1.
That sheet must be the active sheet when you run this macro. Also, I
assumed the name of the other file is "The Other File.xls" and the pertinent
sheet in that other file is named "The Sheet". Also, the file "The Other
File.xls" must be open when this macro is run. The pertinent numbers from
the file that holds the 2 lists are placed in Column A of "The Sheet" in
Column A starting in row 1. HTH Otto

Sub Find6()
Dim rLongList As Range
Dim rShortList As Range
Dim i As Range
Dim Dest As Range
Set rLongList = Range("A1", Range("A" & Rows.Count).End(xlUp))
Set rShortList = Range("B1", Range("B" & Rows.Count).End(xlUp))
With Workbooks("The Other File.xls").Sheets("The Sheet")
Set Dest = .Range("A1")
For Each i In rLongList
If Not rShortList.Find(What:=Left(i.Value, 6),
Lookat:=xlWhole) Is Nothing Then
Dest.Value = i.Value
Set Dest = Dest.Offset(1)
End If
Next i
End With
End Sub

 

[Debra Dalgleish]

In the workbook with the product records, create a criteria area with a
blank heading cell, and a formula in the cell below.
For example, if the SKUs are in column D:

=AND(--LEFT(D2,6)>=200000,--LEFT(D2,6)<300000)

Change the 200000 and 300000 to match the range of SKU you want to extract.
Activate the workbook where you want the filtered records to go.
Then, run the Advanced Filter, and select the blank heading cell, and
the cell with the formula, as the criteria range.

 

[Guest]

Debra,

Thanks for the suggestion. The one problem is that the SKUs are not all SKUs
within a range of SKUs. in you example i might only want 200000, 210000,
250000, and so on. Would i need to setup a separate row for each SKU so that
Excel will us or?

 

[Debra Dalgleish]

Enter the list of SKU on a worksheet, and name the range, e.g. SKUList
For the criteria formula, use:
=COUNTIF(SKUList,D2)