Populate Countif values (Offset) into Array and loop through it

**mchilapur** · 02-15-2017, 10:24 AM

Dear all,
I am working with data in attached file.

I am struggling hard to populate few values into array. below is what i am trying to achieve.

1) I am taking countifs values in column E:E
2) For each values, if CountIfs is >= 2, then i need its offset(0,3) values (Column D:D) to be populated into Dynamic array.
3) Later i will loop through this array if any Status has string "Updates on".

Could anyone help advise how can i achieve this.?

Thanks

**karedog** · 02-16-2017, 05:19 AM

It's not clear what the final result that you want, but please try this :
(note : you can remove column E formulas, countifs() is performed by the macro itself)

Sub Test()
  Dim a(), c As New Collection, strKey As String, i As Long, j As Long, p As Long
  a = Range("A1").CurrentRegion.Resize(, 4).Value
  For i = 2 To UBound(a, 1)
      strKey = a(i, 1) & "|" & a(i, 2) & "|" & a(i, 3)
      On Error Resume Next
         c.Add key:=strKey, Item:=New Collection
      On Error GoTo 0
      c(strKey).Add a(i, 4)
  Next i
  p = 1
  For i = 2 To UBound(a, 1)
      strKey = a(i, 1) & "|" & a(i, 2) & "|" & a(i, 3)
      If c(strKey).Count >= 2 And InStr(1, a(i, 4), "Updates on") > 0 Then
         p = p + 1
         For j = 1 To UBound(a, 2)
             a(p, j) = a(i, j)
         Next j
      End If
  Next i
  For i = p + 1 To UBound(a, 1)
      For j = 1 To UBound(a, 2)
          a(i, j) = vbNullString
      Next j
  Next i
  Range("G1").Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub

**jindon** · 02-16-2017, 05:55 AM

Array x is the one you are looking for.

Sub test()
    Dim x
    With Cells(1).CurrentRegion
        x = Filter(Evaluate("transpose(if((countifs(" & .Columns(1).Address & "," & .Columns(1).Address & "," & _
        .Columns(2).Address & "," & .Columns(2).Address & "," & .Columns(3).Address & "," & .Columns(3).Address & _
        ")>=2)*(left(" & .Columns(4).Address & ",10)=""updates on""),row(1:" & .Rows.Count & ")))"), False, 0)
        If UBound(x) > -1 Then
            x = Application.Index(.Value, Application.Transpose(x), Evaluate("column(" & .Rows(1).Address & ")"))
        Else
            MsgBox "No matched data"
        End If
    End With
End Sub

Populate Countif values (Offset) into Array and loop through it

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Populate Countif values (Offset) into Array and loop through it

Re: Populate Countif values (Offset) into Array and loop through it

Re: Populate Countif values (Offset) into Array and loop through it

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Replace Array values through loop

[SOLVED] Assigning array values with a loop

Loop new messages containing a table, populate a dynamic array, paste array to Excel

[SOLVED] Loop Through Column, Populate Values In Another Sheet With Matching Identifiers

[SOLVED] Populate ComboBox from array criteria from Worksheet Values

[VBA] Populate Array with Range Values applying an IF criteria

VBA. Return values offset loop issues

Bookmarks

Bookmarks

Posting Permissions