VBA Loop Lookup of every nth (eg. every 3rd) match of an item

[Oraekene]

Hi. Good day. Grateful for all the help i've received on this site so far. Here again cap in hand seeking help.

I would like a vb script to loop through a range of values and find every nth eg. Every 3rd occurence of that value and copy and paste the single row of values in the next empty row in another range

Eg. I have customer data of 50 purchases made by 4 customers eg. A B C & D. Each purchase is a row of values showing date of purchase, item of purchase, price etc. I'd like to find every 3rd purchase A made (so her 3rd, 6th, 9th, 12th etc) and copy the row of data (date, item etc) to another range, then repeat for B, then repeat for C and D.

Would be grateful for any help on this. Attached is the sample sheet Sample Sheet

 

[Oraekene]

Glad to help.

Thank you so much for this. How do i use this in vba? Do i use worksheet.

Try this with a copy of your workbook.

Sub Every_Nth()
  Dim d As Object
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, uba2 As Long
 
  Const N As Long = 3 '<- Edit to suit. This is for every 3rd one
 
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("A1", Range("F" & Rows.Count).End(xlUp)).Value
  uba2 = UBound(a, 2)
  ReDim b(1 To UBound(a), 1 To uba2)
  For i = 1 To UBound(a)
    If InStr(1, d(a(i, 2)) & "|", "|" & a(i, 1) & "|") = 0 Then
      If UBound(Split(d(a(i, 2)), "|")) = N - 1 Then
        k = k + 1
        For j = 1 To uba2
          b(k, j) = a(i, j)
        Next j
        d.Remove a(i, 2)
      Else
        d(a(i, 2)) = d(a(i, 2)) & "|" & a(i, 1)
      End If
    End If
  Next i
  If k > 0 Then
    With Range("J1").Resize(k, uba2)
      .Value = b
      .Columns.AutoFit
    End With
  End If
End Sub

Thank you so much for this. I'l guess this would work in 2016 and on. I'd find a copy or try to use 365 online and try this code and give feedback. Thank you so much for this

 

[Peter_SSs]

I'l guess this would work in 2016 and on.

Yes, but your profile says Excel 2013 though it will run in that version too. Does your profile need updating?

How do i use this in vba?

To implement ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

 

[Oraekene]

Yes, but your profile says Excel 2013 though it will run in that version too. Does your profile need updating?

Oh no, i still use 2013, its because earlier you'd said you couldn't find any solution at the time for my version for that particular version, so i thought this vba code was a solution that could only work in newer excel versions. So this script would work in 2013? So so grateful!

 

[Oraekene]

To implement ..

Oh i know how to add vba scripts to new macros, i was referring to the insheet formula you'd written earlier, before the vba. I was asking how to use that formula directly in the vba environment, whether by calling it via worksheet function or via formula. But then you wrote a perfect vba script after so it would no longer be necessary to need to call the initial formula in the vba. Again i am very grateful for this help, thank you

 

[Peter_SSs]

Sorry about the confusion. Glad we got there in the end.

 

[Oraekene]

Sorry about the confusion. Glad we got there in the end.

I thought i'd be able to do this myself, on the surface seemed simple enough, but once again struggled with this and i need help. Again. When i'm done with this project, i'll sit down and do an advanced VBA course to really understand how it works. VBA can be really confusing. Here's the link to the thread Thread 'Loop through subset of worksheets' Loop through subset of worksheets