CODEWITHSUNDEEP
excelexcel-formulaautofilter
Icebreaker
Icebreaker

Reputation: 287

How to filter rows in excel based on this criteria

I have 8 rows like this delimited by comma:

ID    Code    Cost    
1     X       20  
2             20  
3             50  
4             50  
5     Y       10  
6             10  
7             70  
8             70

The criteria is:

If a value in the code column exists, print the row and the row after otherwise do nothing.

How can you do this in excel for any number of rows?

Note the order always will be the same so you can assume if a code is found that row and the one after will always be printed. In the above example the output I would get is:

ID    Code    Cost    
1     X       20  
2             20  
5     Y       10  
6             10

Upvotes: 1

Views: 111

Answers (3)

user4039065
user4039065

Reputation:

Before:

autofilter_before

Run this code:

Option Explicit

Sub specialFilter()
    Dim a As Long, aARRs As Variant, dKEYs As Object

    Set dKEYs = CreateObject("Scripting.Dictionary")
    dKEYs.CompareMode = vbTextCompare

    With Worksheets("Sheet2")
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(1, 1).CurrentRegion
            'build a dictionary so the keys can be used as the array filter
            aARRs = .Range(.Cells(2, 1), .Cells(.Rows.Count, 2)).Value2
            For a = LBound(aARRs, 1) To UBound(aARRs, 1)
                If CBool(Len(aARRs(a, 2))) Then
                    dKEYs.Add Key:=CStr(aARRs(a, 1)), Item:=aARRs(a, 1)
                    If a < UBound(aARRs, 1) Then _
                        dKEYs.Add Key:=CStr(aARRs(a + 1, 1)), Item:=aARRs(a + 1, 1)
                End If
            Next a

            'filter on column B if dictionary keys exist
            If CBool(dKEYs.Count) Then _
                .AutoFilter Field:=1, Criteria1:=dKEYs.Keys, _
                                      Operator:=xlFilterValues

            'data is filtered in column A for any value in column B (and the subsequent row)
            'Perform work on filtered data here

        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With

    dKEYs.RemoveAll: Set dKEYs = Nothing
End Sub

After:

autofilter_after

Upvotes: 1

Mark Fick
Mark Fick

Reputation: 11

Assume 1,X,20 is in cell A2.

In cell B2 copy the following code:

=IF(OR(IF(RIGHT(LEFT(A2,SEARCH(",",A2,1)),1)=MID(A2,SEARCH(",",A2,1)+1,1),FALSE,TRUE),AND(B1=A1,B1<>"",IF(A1<>"",IF(RIGHT(LEFT(A1,SEARCH(",",A1,1)),1)=MID(A1,SEARCH(",",A1,1)+1,1),FALSE,TRUE),FALSE))),A2,"")

In cell C2 enter the following code:

=IF(B2="","",MAX(C$1:C1)+1)

In cell D2 enter the following code and instead of hitting enter, hit Ctrl+Shift+Enter:

=IFERROR(INDEX($B$2:$B$9,MATCH(ROW()-ROW(D$1),C$2:C$9,0)),"")

Copy cells B2, C2, and D2 down to row 9.

Your desired output will be in column D - hide columns B and C if desired.

Upvotes: 1

Mark Fick
Mark Fick

Reputation: 11

Okay, since each data is in a different cell, and assuming 1 is in A2, X is in B2, and 20 is in C2,

Enter in D2:

=IF(OR(B2<>"",AND(B2="",B1<>"")),MAX(D$1:D1)+1,"")

Enter in E2:

=IF(COUNTBLANK($D2)=0,IF(A2="","",A2),"")

Enter in F2:

=IF(COUNTBLANK($D2)=0,IF(B2="","",B2),"")

Enter in G2:

=IF(COUNTBLANK($D2)=0,IF(C2="","",C2),"")

Ctrl+Shift+Enter in H2:

=IFERROR(INDEX(E$2:E$9,MATCH(ROW()-ROW(H$1),$D$2:$D$9,0)),"")

Ctrl+Shift+Enter in I2:

=IFERROR(INDEX(F$2:F$9,MATCH(ROW()-ROW(I$1),$D$2:$D$9,0)),"")

Ctrl+Shift+Enter in J2:

=IFERROR(INDEX(G$2:G$9,MATCH(ROW()-ROW(J$1),$D$2:$D$9,0)),"")

Drag D2:J2 down to row 9 and hide columns D through G.

Upvotes: 0

Related Questions