Filter Data based on a set of values

[klturi421]

I have been utilizing an array formula for a while to filter out specific data that I am looking for, however, those arrays are causing some delays due to the amount of calculation that is going into each one. Yes I know that I can use a formula to get the job done but for simplicities sake i'd rather be able to run a macro that can check the fluctuating list and be able to output that same data without having to drag down a bunch of arrays that may not even run.

As you will see in the spreadsheet, there are a set of 4 columns, I want the second set of data (to the right) to have only data that is for "ACD" AFTER "RING", which I have included in an example above where the output should be.

Book2.xlsm

[HSV]

Hello,

Would this do the trick?

Sub hsv()
With Sheets("sheet1").Range("A3").CurrentRegion
   .Parent.Range("G10").CurrentRegion.ClearContents
   .AutoFilter 3, Array("ACD", "AFTER", "RING"), 7
   .Copy Sheets("sheet1").Range("G10")
   .AutoFilter
End With
End Sub

[klturi421]

Hello,

Would this do the trick?

Sub hsv()
With Sheets("sheet1").Range("A3").CurrentRegion
   .Parent.Range("G10").CurrentRegion.ClearContents
   .AutoFilter 3, Array("ACD", "AFTER", "RING"), 7
   .Copy Sheets("sheet1").Range("G10")
   .AutoFilter
End With
End Sub

That almost does the trick. While it does copy each RING and ACD event I'm wanting it to only pull the first ACD after each RING but does not need to include each one. Much more like the example that I posted.

[YasserKhalil]

Mr. HSV what number 7 at the end of this line?

.AutoFilter 3, Array("ACD", "AFTER", "RING"), 7

[HSV]

It stands for

xlFilterValues

[skywriter]

Try this.

[HSV]

Okay, that I have missed.
Without copy something faster.

Sub FilterData()
Dim sn, arr, cl As Range, i As Long, n As Long
With Sheets("sheet1")
  sn = .Range("a3").CurrentRegion
  ReDim arr(UBound(sn), 3)
 For i = 1 To UBound(sn) - 1
    If UCase(sn(i, 3)) = "RING" And UCase(sn(i + 1, 3)) = "ACD" Then
        arr(n, 0) = sn(i + 1, 1)
        arr(n, 1) = sn(i + 1, 2)
        arr(n, 2) = sn(i + 1, 3)
        arr(n, 3) = sn(i + 1, 4)
        n = n + 1
    End If
   Next i
     With .Range("G10")
        .CurrentRegion.ClearContents
        .Resize(, 4) = Array("Date", "Time", "State", "Length")
        .Offset(1).Resize(UBound(arr), 4) = arr
    End With
End With
End Sub

[klturi421]

Okay, that I have missed.
Without copy something faster.

Sub FilterData()
Dim sn, arr, cl As Range, i As Long, n As Long
With Sheets("sheet1")
  sn = .Range("a3").CurrentRegion
  ReDim arr(UBound(sn), 3)
 For i = 1 To UBound(sn) - 1
    If UCase(sn(i, 3)) = "RING" And UCase(sn(i + 1, 3)) = "ACD" Then
        arr(n, 0) = sn(i + 1, 1)
        arr(n, 1) = sn(i + 1, 2)
        arr(n, 2) = sn(i + 1, 3)
        arr(n, 3) = sn(i + 1, 4)
        n = n + 1
    End If
   Next i
     With .Range("G10")
        .CurrentRegion.ClearContents
        .Resize(, 4) = Array("Date", "Time", "State", "Length")
        .Offset(1).Resize(UBound(arr), 4) = arr
    End With
End With
End Sub

Works like a charm! Thanks for that!

[skywriter]

Works like a charm! Thanks for that!

It looks like my solution in post #5 did what you asked, but you seemed to have ignored it. Of course I guess I should get partial credit since the person who wrote the solution you chose took the name of my sub procedure and used it instead of making up their own.

[klturi421]

It looks like my solution in post #5 did what you asked, but you seemed to have ignored it. Of course I guess I should get partial credit since the person who wrote the solution you chose took the name of my sub procedure and used it instead of making up their own.

I honestly did not see your reply. Not due to looking past it but by the time I updated my post you posted yours and I didn't see it until after HSV already made a change to the VB. I do appreciate everyone's input. I will likely make use of both of them to be honest.