Filter down spreadsheet into another sheet based on a condition

Hi tyson,

I have attached the sample file and below is the code for your reference.

Do add Reputation, if i helped in someway.

Sub autfil()

Range("A1:M1").AutoFilter
    ActiveSheet.Range("$A$1:$M$6").AutoFilter Field:=8, Criteria1:="=*/*", Operator:=xlAnd
    ActiveSheet.Range("$A$1:$M$6").AutoFilter Field:=9, Criteria1:=">0", Operator:=xlAnd
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Copy
    Worksheets(2).Activate
    Range("A1").PasteSpecial
  Application.CutCopyMode = False
  Worksheets(1).AutoFilterMode = False
  Worksheets(1).Range("A1").Select
        
End Sub

@Pete.. I would love to see a formula solution if possible. Yes I was thinking that, I may try add in another column and join col H & I together and remove the slash.

In the attached file I have used column O in Sheet1 as a helper column (coloured blue), with this formula in O2:

=IF(OR(H2="",I2=""),"-",IF(ISNUMBER(--SUBSTITUTE(H2&I2,"/","")),MAX(O$1:O1)+1,"-"))

This is then copied down beyond your data to accommodate more data being added - the hyphens indicate how far the formula is active. The formula identifies the records which meet the criteria and gives each a unique sequential number.

Then in Sheet2 I have used column A as another helper, with this formula in A2:

=IFERROR(MATCH(ROWS($1:1),Sheet1!O:O,0),"")

The formula finds the rows in Sheet1 where the records meet the criteria. B2 in this sheet contains this formula:

=IF($A2="","",IF(INDEX(Sheet1!B:B,$A2)="","",INDEX(Sheet1!B:B,$A2)))

which returns the appropriate data, and this formula is copied across to Column K to get successive fields. Finally, the row of formulae from row 2 is copied down as far as you think you might need them (to row 10 in this case).

The formula-based solution will respond immediately to changes in Sheet1 - set I4 or I6 to a number to see the effect on Sheet2, or set H5 to a number.

Hope this helps.

Pete

Originally Posted by Pete_UK

In the attached file I have used column O in Sheet1 as a helper column (coloured blue), with this formula in O2:

=IF(OR(H2="",I2=""),"-",IF(ISNUMBER(--SUBSTITUTE(H2&I2,"/","")),MAX(O$1:O1)+1,"-"))

This is then copied down beyond your data to accommodate more data being added - the hyphens indicate how far the formula is active. The formula identifies the records which meet the criteria and gives each a unique sequential number.

Then in Sheet2 I have used column A as another helper, with this formula in A2:

=IFERROR(MATCH(ROWS($1:1),Sheet1!O:O,0),"")

The formula finds the rows in Sheet1 where the records meet the criteria. B2 in this sheet contains this formula:

=IF($A2="","",IF(INDEX(Sheet1!B:B,$A2)="","",INDEX(Sheet1!B:B,$A2)))

which returns the appropriate data, and this formula is copied across to Column K to get successive fields. Finally, the row of formulae from row 2 is copied down as far as you think you might need them (to row 10 in this case).

The formula-based solution will respond immediately to changes in Sheet1 - set I4 or I6 to a number to see the effect on Sheet2, or set H5 to a number.

Hope this helps.

Pete

@Peter that is exactly what I am looking for. Thank you very much. I got assigned this task and I was really only familiar with simple math formulas. You have helped me a lot. I have been looking all over the net and most attempted solutions were all macro based (which I never used before) but this solution is great and very flexible. Thanks once again.

You're welcome - glad to be of help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Originally Posted by prabhuduraraj09

Hi tyson,

I have attached the sample file and below is the code for your reference.

Do add Reputation, if i helped in someway.

Sub autfil()

Range("A1:M1").AutoFilter
    ActiveSheet.Range("$A$1:$M$6").AutoFilter Field:=8, Criteria1:="=*/*", Operator:=xlAnd
    ActiveSheet.Range("$A$1:$M$6").AutoFilter Field:=9, Criteria1:=">0", Operator:=xlAnd
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Copy
    Worksheets(2).Activate
    Range("A1").PasteSpecial
  Application.CutCopyMode = False
  Worksheets(1).AutoFilterMode = False
  Worksheets(1).Range("A1").Select
        
End Sub

When I try this code I get a run time error 1004 "method of range class failed", In debug mode it points to the last line before end sub. It still works as expected though?

@Pete If you get the chance to provide a macro solution, please do. I have tried the other macro solution provided above but it does not have the flexibility that I need. The table that I have has 19 Columns and a few thousand rows (which can change). The above macro solution seems to be a set number.

I used to do a lot of programming years ago, but now that I have retired I tend not to and it is very easy to forget how to do things if you are not doing them constantly - I tend to leave macro solutions to those who are more proficient.

Pete