Thread: filtering rows

I get sent a spreadsheet by my supplier every week with 1000's of products.

i am only interested in about 50 products.

Column A has 'reference' as a heading and below are the products numbers and in the next columns are weights and prices

I want to be able to filter out the 'reference' numbers i am interested in, every time I get sent a new spreadsheet and put this data either somewhere on existing sheet or on a new page or even a new spreadsheet so I dont have to wade thru the huge long list.

I have tried filtering but this would need to be set up again each time i receive a new spreadsheet.

I think I need to make a makro but am in the dark as to how to go about this.

Any ideas please!

Thanks

i am only interested in about 50 products.

Is it always the same items?
You might take a look at using Advanced Filter with criteria, using the option to copy the results to a new location. See this link of Advanced Filter with criteria

The advanced filter can be easily automated with VBA. For more specific help post a sample workbook that meets the following criteria

1. It EXACTLY duplicates the structure of your real workbook, AND
2. It contains representative but non-sensitive dummy data, AND
3. It manually shows the expected/desire results.

Here's a macro that will do it. It requires you create a sheet called "MasterList" and put your list of reference numbers of importance to you in column A as a list.

Then create a blank sheet called ExtractedList, and your raw data will go on a sheet called Data... then run this macro anytime you want a newly extracted set of data.

Code:

Option Explicit

Sub ExtractData()
'JBeaucaire  2/16/2010
Dim LR As Long:     LR = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False

Sheets("ExtractedList").Cells.Clear

    With Sheets("Data")
        .Range("F1") = "Key"
        .Range("F2:F" & LR).FormulaR1C1 = "=ISNUMBER(MATCH(RC1,MasterList!C1,0))"
        .Range("F:F").AutoFilter Field:=1, Criteria1:="TRUE"
        .Range("A1:E" & LR).Copy Sheets("ExtractedList").Range("A1")
        .AutoFilterMode = False
        .Range("F:F").ClearContents
    End With

Application.ScreenUpdating = True
Sheets("ExtractedData").Activate
Beep
End Sub

I would just drop the new data onto the DATA sheet in columns A:E (column F needs to remain clear for this macro). Run the macro and the ExtractedList is instantly updated for you.

======
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

Following Palmetto's Adv. Filter I came up with this macro

Code:

Sub advFilter()

Dim Ws As Worksheet
Dim HeaderRng As Range, OutputRng As Range, FilterRng As Range, CriteriaRng As Range
Dim Width As Long

Set Ws = Worksheets("Sheet1")
Set HeaderRng = Ws.Range(Range("A1"), Cells(1, Cells.Columns.Count).End(xlToLeft))
Set FilterRng = HeaderRng.CurrentRegion
Width = HeaderRng.Columns.Count
Set OutputRng = HeaderRng.Offset(1, Width + 3)
Set CriteriaRng = HeaderRng.Offset(1, Width + 1).Resize(1, 1)

HeaderRng.Copy OutputRng 'Output Range the same
HeaderRng.Resize(1, 1).Copy CriteriaRng 'Output Range the same
Range("RefList").Copy CriteriaRng.Offset(1, 0)
Set CriteriaRng = CriteriaRng.Resize(1 + Range("RefList").Rows.Count, 1)
    
  FilterRng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CriteriaRng, _
                           CopyToRange:=OutputRng, Unique:=True
End Sub

Your References go into Sheet2

press Alt-F11 and run the macro

I've tried to upload but It keeps on failing ??!!!

Change this Range("RefList")
into Range("Sheet2!A2:A51")

Originally Posted by peteknopp

Drop new data etc and then RUN MACRO. Does this happen automatically as I cant find a command.

You should study up on the Excel basics, running a macro can be done right from the Tools > Macros menu, or by pressing Alt-F8.

Jbeaucaire - I am using a Mac and TOOLS - Macro is where I went but no Macros listed. Is there a name for your macro that i need to insert.

The example you uploaded seemed to work automatically.