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 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.
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:
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.
