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]

@Peter_SSs @Trebor76 please help. Its just a modification of the original request which is why i didn't post it in a new thread. Sample sheet here. Please help

 

[Peter_SSs]

Its just a modification of the original request

I don't understand what is required this time. Can you spell out in detail how we would decide which rows from that sample sheet get transferred to columns J to O?
That is, exactly how did you manually decide which rows to bold in columns A:F?

 

[Oraekene]

I don't understand what is required this time. Can you spell out in detail how we would decide which rows from that sample sheet get transferred to columns J to O?
That is, exactly how did you manually decide which rows to bold in columns A:F?

Thank you for the response and apologies for my late reply. Here is the explanation

There are basically 2 criteria: the Account Numbers (the first column with the 10xe001, 10xe002 type numbers) and the project names (the second column with the Jaguda, Nsobu, mandela names). The whole range is sorted by time from the earliest to the latest time

What i hope to achieve is for ever project (eg. Mandela), i'd like to find the third unique wallet (eg. 10xe003) linked to that project. So for example Mandela might have rows with Mandela-10xe001, Mandela-10xe001, Mandela-10xe002, Mandela-10xe001, Mandela-10xe002,Mandela-10xe003. The row that would be selected would be the Mandela-10xe003 because that would be the row with the third unique wallet - 10xe003 - for the project named Mandela.

I don't know if this helps, i hope it does

 

[Peter_SSs]

Thanks for the clarification. At this point I don't see an obvious solution for your excel version.

 

[Oraekene]

Thanks for the clarification. At this point I don't see an obvious solution for your excel version.

Oh no problem. Wanted to ask if there would be possible solutions on later excel versions?

 

[Peter_SSs]

Wanted to ask if there would be possible solutions on later excel versions?

Unfortunately your description in post 13 about how to decide which rows to return only covered one row, not how to also get subsequent rows. The best guess that I can give, which does not not match your given expected results**, is ..

** From what I understand, row 21 should be in the results since that row contains the 3rd unique value for mandela since the previously recorded mandela row (row 6)

Oraekene kantakura.xlsx
	A	B	C	D	E	F	G			J	K	L	M	N	O
1	10xe001	jagudam	12/03/2022	A	1	i				10xe003	mandela	14/03/2022	C	2	i
2	10xe001	jagudam	12/03/2022	A	1	ii				10xe005	jagudam	18/03/2022	E	1	i
3	10xe002	mandela	12/03/2022	B	2	i	FALSE			10xe005	mandela	18/03/2022	E	2	i
4	10xe002	nsobu	3/12/2022	B	3	i	FALSE			10xe001	nsobu	19/03/2022	A	3	i
5	10xe001	mandela	13/03/2022	A	2	i	FALSE			10xe004	deeper life	20/03/2022	D	5	i
6	10xe003	mandela	14/03/2022	C	2	i	TRUE			10xe005	jagudam	22/03/2022	E	1	ii
7	10xe004	mandela	14/03/2022	D	1	i	FALSE			10xe002	jagudam	24/03/2022	B	1	ii
8	10xe002	caricature	14/03/2022	B	4	i	FALSE			10xe004	caricature	25/03/2022	D	4	i
9	10xe002	nsobu	14/03/2022	B	3	ii	FALSE
10	10xe002	deeper life	16/03/2022	B	5	i	FALSE
11	10xe001	deeper life	17/03/2022	A	5	i	FALSE
12	10xe001	jagudam	17/03/2022	A	1	iii	FALSE
13	10xe004	jagudam	18/03/2022	D	1	ii	FALSE
14	10xe004	jagudam	18/03/2022	D	1	iii	FALSE
15	10xe005	jagudam	18/03/2022	E	1	i	TRUE
16	10xe002	jagudam	18/03/2022	B	1	i	FALSE
17	10xe003	mandela	18/03/2022	C	2	ii	FALSE
18	10xe003	nsobu	18/03/2022	C	3	i	FALSE
19	10xe003	mandela	18/03/2022	C	2	iii	FALSE
20	10xe003	mandela	18/03/2022	C	2	iv	FALSE
21	10xe005	mandela	18/03/2022	E	2	i	TRUE
22	10xe003	caricature	19/03/2022	C	4	i	FALSE
23	10xe001	nsobu	19/03/2022	A	3	i	TRUE
24	10xe001	deeper life	19/03/2022	A	5	ii	FALSE
25	10xe004	deeper life	20/03/2022	D	5	i	TRUE
26	10xe004	jagudam	22/03/2022	D	1	iii	FALSE
27	10xe005	jagudam	22/03/2022	E	1	ii	TRUE
28	10xe003	jagudam	22/03/2022	C	1	i	FALSE
29	10xe004	jagudam	23/03/2022	D	1	iv	FALSE
30	10xe002	jagudam	24/03/2022	B	1	ii	TRUE
31	10xe001	jagudam	24/03/2022	A	1	iv	FALSE
32	10xe001	mandela	25/03/2022	A	2	ii	FALSE
33	10xe001	nsobu	25/03/2022	A	3	ii	FALSE
34	10xe001	mandela	25/03/2022	A	2	iii	FALSE
35	10xe002	mandela	25/03/2022	B	2	i	FALSE
36	10xe002	mandela	25/03/2022	B	2	ii	FALSE
37	10xe004	caricature	25/03/2022	D	4	i	TRUE
38	10xe005	nsobu	25/03/2022	E	3	i	FALSE
39	10xe005	deeper life	25/03/2022	E	5	i	FALSE
40	10xe005	deeper life	26/03/2022	E	5	ii	FALSE
41	10xe005	jagudam	27/03/2022	E	1	iii	FALSE
42	10xe001	jagudam	27/03/2022	A	1	v	FALSE
43	10xe005	jagudam	28/03/2022	E	1	iv	FALSE
Sheet1

Cell Formulas
Range	Formula
J1:O8	J1	=FILTER(A1:F43,G1:G43)
G3:G43	G3	=LET(fr,IFNA(XMATCH(B3&TRUE,B$1:B2&G$1:G2,,-1),0)+1,MOD(ROWS(UNIQUE(FILTER(INDEX(A$1:A3,fr):A3,INDEX(B$1:B3,fr):B3=B3))),3)=0)
Dynamic array formulas.

 

[Oraekene]

Unfortunately your description in post 13 about how to decide which rows to return only covered one row, not how to also get subsequent rows. The best guess that I can give, which does not not match your given expected results**, is ..

** From what I understand, row 21 should be in the results since that row contains the 3rd unique value for mandela since the previously recorded mandela row (row 6)

Oraekene kantakura.xlsx
	A	B	C	D	E	F	G			J	K	L	M	N	O
1	10xe001	jagudam	12/03/2022	A	1	i				10xe003	mandela	14/03/2022	C	2	i
2	10xe001	jagudam	12/03/2022	A	1	ii				10xe005	jagudam	18/03/2022	E	1	i
3	10xe002	mandela	12/03/2022	B	2	i	FALSE			10xe005	mandela	18/03/2022	E	2	i
4	10xe002	nsobu	3/12/2022	B	3	i	FALSE			10xe001	nsobu	19/03/2022	A	3	i
5	10xe001	mandela	13/03/2022	A	2	i	FALSE			10xe004	deeper life	20/03/2022	D	5	i
6	10xe003	mandela	14/03/2022	C	2	i	TRUE			10xe005	jagudam	22/03/2022	E	1	ii
7	10xe004	mandela	14/03/2022	D	1	i	FALSE			10xe002	jagudam	24/03/2022	B	1	ii
8	10xe002	caricature	14/03/2022	B	4	i	FALSE			10xe004	caricature	25/03/2022	D	4	i
9	10xe002	nsobu	14/03/2022	B	3	ii	FALSE
10	10xe002	deeper life	16/03/2022	B	5	i	FALSE
11	10xe001	deeper life	17/03/2022	A	5	i	FALSE
12	10xe001	jagudam	17/03/2022	A	1	iii	FALSE
13	10xe004	jagudam	18/03/2022	D	1	ii	FALSE
14	10xe004	jagudam	18/03/2022	D	1	iii	FALSE
15	10xe005	jagudam	18/03/2022	E	1	i	TRUE
16	10xe002	jagudam	18/03/2022	B	1	i	FALSE
17	10xe003	mandela	18/03/2022	C	2	ii	FALSE
18	10xe003	nsobu	18/03/2022	C	3	i	FALSE
19	10xe003	mandela	18/03/2022	C	2	iii	FALSE
20	10xe003	mandela	18/03/2022	C	2	iv	FALSE
21	10xe005	mandela	18/03/2022	E	2	i	TRUE
22	10xe003	caricature	19/03/2022	C	4	i	FALSE
23	10xe001	nsobu	19/03/2022	A	3	i	TRUE
24	10xe001	deeper life	19/03/2022	A	5	ii	FALSE
25	10xe004	deeper life	20/03/2022	D	5	i	TRUE
26	10xe004	jagudam	22/03/2022	D	1	iii	FALSE
27	10xe005	jagudam	22/03/2022	E	1	ii	TRUE
28	10xe003	jagudam	22/03/2022	C	1	i	FALSE
29	10xe004	jagudam	23/03/2022	D	1	iv	FALSE
30	10xe002	jagudam	24/03/2022	B	1	ii	TRUE
31	10xe001	jagudam	24/03/2022	A	1	iv	FALSE
32	10xe001	mandela	25/03/2022	A	2	ii	FALSE
33	10xe001	nsobu	25/03/2022	A	3	ii	FALSE
34	10xe001	mandela	25/03/2022	A	2	iii	FALSE
35	10xe002	mandela	25/03/2022	B	2	i	FALSE
36	10xe002	mandela	25/03/2022	B	2	ii	FALSE
37	10xe004	caricature	25/03/2022	D	4	i	TRUE
38	10xe005	nsobu	25/03/2022	E	3	i	FALSE
39	10xe005	deeper life	25/03/2022	E	5	i	FALSE
40	10xe005	deeper life	26/03/2022	E	5	ii	FALSE
41	10xe005	jagudam	27/03/2022	E	1	iii	FALSE
42	10xe001	jagudam	27/03/2022	A	1	v	FALSE
43	10xe005	jagudam	28/03/2022	E	1	iv	FALSE
Sheet1

Cell Formulas
Range	Formula
J1:O8	J1	=FILTER(A1:F43,G1:G43)
G3:G43	G3	=LET(fr,IFNA(XMATCH(B3&TRUE,B$1:B2&G$1:G2,,-1),0)+1,MOD(ROWS(UNIQUE(FILTER(INDEX(A$1:A3,fr):A3,INDEX(B$1:B3,fr):B3=B3))),3)=0)
Dynamic array formulas.

Yes yes yes you are right! It was my error. For mandela Row 21 should be the next after row 6, you've very correct. I made an error on my part

 

[Peter_SSs]

Glad to help.

 

[Oraekene]

Glad to help.

Thank you so much for this. How do i use this in vba? Do i use Range.Formula? If so, can i make the range dynamic?

 

[Peter_SSs]

How do i use this in vba?

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