Find quads delay (Loop quads within 5 n1:n5 results)

[motilulla]

Using Excel 2010

Hello, I got lottery results in column D:H approximately 2000+ rows, and set of quads in column K:N approximately 65000+ rows, I need a VBA which can loop quads… through column D:H and generate the find DELAY result in column O as shown in the few lines example below.

Find Dealy MrExcel.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2															Example Results
3			30												Need VBA To Find
4															Last Delay
5		Draw	Date	n1	n2	n3	n4	n5			Quads				Loop Delay Within n1:n5
6		1	13/02/2004	16	29	32	36	41			16	29	32	36	29	<--Last Appeared In Draw 1 (Total Draw=30) So Delay = 29
7		2	20/02/2004	7	13	39	47	50			15	24	28	44	14	<--Last Appeared In Draw 16 (Total Draw=30) So Delay = 14
8		3	27/02/2004	14	18	19	31	37			8	12	14	15	9	<--Last Appeared In Draw 21 (Total Draw=30) So Delay = 9
9		4	05/03/2004	4	7	33	37	39			3	21	30	34	10	<--Last Appeared In Draw 20 (Total Draw=30) So Delay = 10
10		5	12/03/2004	15	24	28	44	47			4	12	24	36	22	<--Last Appeared In Draw 8 (Total Draw=30) So Delay = 22
11		6	19/03/2004	33	36	37	42	45			14	15	28	35	?
12		7	26/03/2004	3	4	10	23	43			9	13	34	41	?
13		8	02/04/2004	4	12	24	27	36			5	15	24	35	?
14		9	09/04/2004	1	4	10	19	23			1	11	22	28	?
15		10	16/04/2004	14	15	28	35	40			16	29	32	41	?
16		11	23/04/2004	6	10	21	45	49			15	16	21	36	?
17		12	30/04/2004	5	6	16	23	27			20	27	41	43	?
18		13	07/05/2004	15	16	21	36	38			8	12	15	34	0	<--Last Appeared In Draw 30 (Total Draw=30) So Delay = 0
19		14	14/05/2004	1	3	21	32	39
20		15	21/05/2004	15	29	37	39	49
21		16	28/05/2004	15	24	28	32	44
22		17	04/06/2004	9	13	34	41	42
23		18	11/06/2004	2	7	8	10	47
24		19	18/06/2004	2	23	28	40	43
25		20	25/06/2004	3	21	30	34	35
26		21	02/07/2004	8	12	14	15	37
27		22	09/07/2004	2	5	12	19	44
28		23	16/07/2004	24	26	31	38	50
29		24	23/07/2004	7	10	27	31	34
30		25	30/07/2004	9	10	19	37	50
31		26	06/08/2004	5	15	24	35	44
32		27	13/08/2004	20	27	41	43	50
33		28	20/08/2004	6	9	10	27	35
34		29	27/08/2004	1	11	22	28	44
35		30	03/09/2004	8	12	14	15	34
36
37
38
Find Last Delay

Cell Formulas
Range	Formula
C3	C3	=MAX(IF(C6:C35<>"",ROW(C6:C35)))-5
Press CTRL+SHIFT+ENTER to enter array formulas.

Regards,
Moti

 

[felixstraube]

Not VBA, nor the cleanest method, but it gets the job done for excel 2010:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2														Example Results
3		30												Need VBA To Find
4														Last Delay
5	Draw	Date	n1	n2	n3	n4	n5			Quads				Loop Delay Within n1:n5
6	1	13/02/2004	16	29	32	36	41			16	29	32	36	29	<--Last Appeared In Draw 1 (Total Draw=30) So Delay = 29
7	2	20/02/2004	7	13	39	47	50			15	24	28	44	14	<--Last Appeared In Draw 16 (Total Draw=30) So Delay = 14
8	3	27/02/2004	14	18	19	31	37			8	12	14	15	9	<--Last Appeared In Draw 21 (Total Draw=30) So Delay = 9
9	4	05/03/2004	4	7	33	37	39			3	21	30	34	10	<--Last Appeared In Draw 20 (Total Draw=30) So Delay = 10
10	5	12/03/2004	15	24	28	44	47			4	12	24	36	22	<--Last Appeared In Draw 8 (Total Draw=30) So Delay = 22
11	6	19/03/2004	33	36	37	42	45			14	15	28	35	20
12	7	26/03/2004	3	4	10	23	43			9	13	34	41	13
13	8	02/04/2004	4	12	24	27	36			5	15	24	35	4
14	9	09/04/2004	1	4	10	19	23			1	11	22	28	1
15	10	16/04/2004	14	15	28	35	40			16	29	32	41	29
16	11	23/04/2004	6	10	21	45	49			15	16	21	36	17
17	12	30/04/2004	5	6	16	23	27			20	27	41	43	3
18	13	07/05/2004	15	16	21	36	38			8	12	15	34	0	<--Last Appeared In Draw 30 (Total Draw=30) So Delay = 0
19	14	14/05/2004	1	3	21	32	39
20	15	21/05/2004	15	29	37	39	49
21	16	28/05/2004	15	24	28	32	44
22	17	04/06/2004	9	13	34	41	42
23	18	11/06/2004	2	7	8	10	47
24	19	18/06/2004	2	23	28	40	43
25	20	25/06/2004	3	21	30	34	35
26	21	02/07/2004	8	12	14	15	37
27	22	09/07/2004	2	5	12	19	44
28	23	16/07/2004	24	26	31	38	50
29	24	23/07/2004	7	10	27	31	34
30	25	30/07/2004	9	10	19	37	50
31	26	06/08/2004	5	15	24	35	44
32	27	13/08/2004	20	27	41	43	50
33	28	20/08/2004	6	9	10	27	35
34	29	27/08/2004	1	11	22	28	44
35	30	03/09/2004	8	12	14	15	34
Sheet8

Cell Formulas
Range	Formula
C3	C3	=MAX(IF(C6:C3000<>"",ROW(C6:C3000)))-5
O6:O18	O6	=$C$3-MAX( IFERROR(MATCH(($K6&"|"&$L6&"|"&$M6&"|"&$N6), ( $D$6:$D$3000&"|"&$E$6:$E$3000&"|"&$F$6:$F$3000&"|"&$G$6:$G$3000), 0), 0), IFERROR(MATCH(($K6&"|"&$L6&"|"&$M6&"|"&$N6), ( $D$6:$D$3000&"|"&$E$6:$E$3000&"|"&$F$6:$F$3000&"|"&$H$6:$H$3000), 0), 0), IFERROR(MATCH(($K6&"|"&$L6&"|"&$M6&"|"&$N6), ( $D$6:$D$3000&"|"&$E$6:$E$3000&"|"&$G$6:$G$3000&"|"&$H$6:$H$3000), 0), 0), IFERROR(MATCH(($K6&"|"&$L6&"|"&$M6&"|"&$N6), ( $D$6:$D$3000&"|"&$F$6:$F$3000&"|"&$G$6:$G$3000&"|"&$H$6:$H$3000), 0), 0), IFERROR(MATCH(($K6&"|"&$L6&"|"&$M6&"|"&$N6), ( $E$6:$E$3000&"|"&$F$6:$F$3000&"|"&$G$6:$G$3000&"|"&$H$6:$H$3000), 0), 0) )

 

[motilulla]

Not VBA, nor the cleanest method, but it gets the job done for excel 2010:

Hello felixstraube, this is amazing, I tried with my original data as per post#1 results in column D:H approximately 2000+ rows, and set of quads in column K:N approximately 65000+ rows, speechless by your excel art work, formula worked like magic 100% perfect. I am using it now, may someone make it shorter or provide VBA solution.

I am happy by your help and appreciate it. Have a pleasant time. Good Luck.

Best Regards,
Moti

 

[Phuoc]

Try this code:

Sub Last_Delay()
Dim a As Variant, b As Variant, c As Variant
Dim i&, j&, ra&, rb&, temp$
Dim t As Double
t = Timer
a = Range("D6", Range("H" & Rows.Count).End(xlUp)).Value
b = Range("K6", Range("N" & Rows.Count).End(xlUp)).Value
ra = UBound(a, 1)
rb = UBound(b, 1)
ReDim c(1 To rb, 1 To 2)
For i = 1 To rb
    c(i, 2) = "*" & Format(b(i, 1), "00") & "|*" & Format(b(i, 2), "00") & "|*" & Format(b(i, 3), "00") & "|*" & Format(b(i, 4), "00") & "|*"
Next i
For j = ra To 1 Step -1
    temp = Format(a(j, 1), "00|") & Format(a(j, 2), "00|") & Format(a(j, 3), "00|") & Format(a(j, 4), "00|") & Format(a(j, 5), "00|")
    For i = 1 To rb
        If c(i, 1) = "" Then
            If temp Like c(i, 2) Then c(i, 1) = ra - j
        End If
    Next i
Next j
Range("P6").Resize(rb, 1) = c
MsgBox Timer - t
End Sub

 

[motilulla]

Try this code:

Hello Phuoc, this is remarkable, I tried with my original data as per post#1 results in column D:H approximately 2000+ rows, and set of quads in column K:N approximately 65000+ rows, speechless by your excel art work, VBA worked like magic with 100% accuracy. It took only 28 second to accomplish find the delays. You made my day

I am happy and admire your kind help. Have a great weekend. Good Luck.

Best Regards,
Moti

 

[Phuoc]

If K:N is 65000+ rows and D:H is 2000+ rows, try this code:

Sub Last_Delay_V2()
Dim a As Variant, b As Variant, c As Variant
Dim i&, j&, ra&, rb&, temp$
Dim t As Double
t = Timer
a = Range("D6", Range("H" & Rows.Count).End(xlUp)).Value
b = Range("K6", Range("N" & Rows.Count).End(xlUp)).Value
ra = UBound(a, 1)
rb = UBound(b, 1)
ReDim c(1 To rb, 1 To 1)

For j = 1 To ra
    a(j, 1) = Format(a(j, 1), "00|") & Format(a(j, 2), "00|") & Format(a(j, 3), "00|") & Format(a(j, 4), "00|") & Format(a(j, 5), "00|")
Next j

For i = 1 To rb
    temp = "*" & Format(b(i, 1), "00") & "|*" & Format(b(i, 2), "00") & "|*" & Format(b(i, 3), "00") & "|*" & Format(b(i, 4), "00") & "|*"
    For j = ra To 1 Step -1
        If a(j, 1) Like temp Then
           c(i, 1) = ra - j
           Exit For
        End If
    Next j
Next i
Range("P6").Resize(rb, 1) = c
MsgBox Timer - t
End Sub

 

[motilulla]

If K:N is 65000+ rows and D:H is 2000+ rows, try this code:

Hello Phuoc, this macro took only and only 20 second to finish to find last delays and with 100% accuracy too. This is really time saver version. I am grateful with your kind help.

Have a peaceful and joyful weekend. Good Luck.

Best Regards,
Moti