Find the optimum combination of non-overlapping groups of numbers

[spavlou]

Hello,
I would be absolutely grateful if someone could help me, as I am totally lost at the moment.
In cells A:F I receive 5 statistical values and in cells G:K their relevant position according to a 1 - 45 ranking system. The positions are then
sorted in ascending order in cells L:P

Cells Q:W do not belong to the spreadsheet. I just give a very simple example of what I'm trying to find.
I suppose that I need a repetitive process through a Visual Basic macro but my visual basic knowledge is limited
so, I count on your help

Note that every day I receive a new row of data, but I don't mind at all to run the macro once a day manually.

Thank you!!!

OPTIMUM_G7.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	DAY	Q-STAT VALUES					UNSORTED POSITIONS					SORTED POSITIONS						GROUP-1	GROUP-2	GROUP-3	SUM	TOTAL CASES
2		QS_1	QS_2	QS_3	QS_4	QS_5	UP_1	UP_2	UP_3	UP_4	UP_5	SP_1	SP_2	SP_3	SP_4	SP_5	EXAMPLE ->	3-10	19-26	38-45		144	<- TOTAL NUMBER OF
3	1	6.0019	17.0022	2.0023	7.0032	9.0041	21	6	33	19	15	6	15	19	21	33		1	2	0	3		CASES WITH AT LEAST
4	2	15.0001	15.0011	3.0016	7.0039	5.0042	8	7	30	17	21	7	8	17	21	30		2	1	0	3		4 POSITIONS CONTAINED
5	3	0.0016	1.0032	2.0035	19.0037	0.0039	43	37	32	5	42	5	32	37	42	43		1	0	2	3		IN THE 3 GROUPS
6	4	19.0013	5.0021	2.0023	3.0029	6.0038	5	24	35	31	19	5	19	24	31	35		1	2	0	3
7	5	14.0009	1.0016	8.0024	17.0026	1.0039	7	40	16	5	36	5	7	16	36	40		2	0	1	3
8	6	9.0006	16.0018	6.0040	4.0041	5.0043	14	5	20	27	24	5	14	20	24	27		1	2	0	3		GOAL IS:
9	7	6.0003	16.0005	14.0027	15.0033	9.0036	22	6	8	7	13	6	7	8	13	22		3	1	0	4
10	8	15.0002	1.0018	2.0024	0.0027	2.0039	6	39	33	43	31	6	31	33	39	43		1	0	2	3		TO FIND EVERY POSSIBLE
11	9	1.0003	2.0006	45.0008	10.0025	0.0039	40	36	2	11	41	2	11	36	40	41		0	0	2	2		COMBINATION
12	10	5.0013	1.0027	5.0029	10.0034	10.0044	27	37	24	11	10	10	11	24	27	37		1	1	0	2		OF 3 NON-OVERLAPPING
13	11	11.0017	9.0022	0.0029	12.0031	0.0044	11	13	43	9	41	9	11	13	41	43		1	0	2	3		GROUPS OF 7
14	12	21.0007	6.0009	0.0022	3.0024	8.0035	4	23	44	30	16	4	16	23	30	44		1	1	1	3		CONSECUTIVE NUMBERS
15	13	5.0005	7.0016	0.0024	1.0029	5.0033	26	20	42	39	25	20	25	26	39	42		0	3	2	5		FROM A POOL OF 1 TO 45
16	14	4.0003	31.0004	9.0021	1.0022	10.0032	30	3	18	38	15	3	15	18	30	38		1	0	1	2
17	15	1.0005	13.0019	1.0024	8.0041	3.0044	40	9	38	18	30	9	18	30	38	40		1	0	2	3		CHECK FOR EVERY DAY
18	16	20.0010	16.0020	15.0028	1.0032	0.0044	4	7	8	36	41	4	7	8	36	41		3	0	1	4		IN CELLS L:P,
19	17	2.0003	1.0005	4.0009	45.0014	2.0021	36	40	28	2	34	2	28	34	36	40		0	0	1	1		HOW MANY POSITIONS
20	18	0.0005	15.0011	1.0020	8.0025	12.0026	44	6	39	18	11	6	11	18	39	44		1	0	2	3		ARE CONTAINED
21	19	23.0012	10.0018	8.0027	57.0030	16.0042	2	13	19	1	5	1	2	5	13	19		1	1	0	2		IN EACH ONE OF THE
22	20	7.0007	15.0023	6.0029	4.0041	13.0043	19	6	21	25	7	6	7	19	21	25		2	3	0	5		ABOVE GROUP OF 7
23	21	3.0009	10.0013	3.0014	23.0015	8.0035	29	13	28	2	16	2	13	16	28	29		0	0	0	0		COMBINATIONS
24	22	0.0015	11.0034	0.0035	14.0036	15.0040	42	11	41	6	5	5	6	11	41	42		2	0	2	4
25	23	9.0016	7.0024	3.0030	3.0042	2.0043	12	15	29	28	33	12	15	28	29	33		0	0	0	0		FIND THE
26	24	2.0013	2.0014	8.0019	7.0028	0.0042	34	33	14	17	42	14	17	33	34	42		0	0	1	1		OPTIMUM COMBINATION/S
27	25	16.0002	10.0022	5.0027	21.0037	4.0041	5	12	24	3	27	3	5	12	24	27		2	1	0	3		WICH GIVE
28	26	1.0013	2.0016	6.0018	5.0023	3.0040	40	35	21	24	27	21	24	27	35	40		0	2	1	3		THE HIGHEST VALUE
29	27	15.0017	8.0020	2.0028	4.0034	30.0045	8	18	33	27	1	1	8	18	27	33		1	0	0	1		IN CELL "V2"
30	28	13.0004	7.0007	1.0018	0.0020	0.0045	8	20	38	44	41	8	20	38	41	44		1	1	3	5
31	29	11.0003	2.0013	11.0021	10.0025	5.0030	12	37	11	14	24	11	12	14	24	37		0	1	0	1
32	30	2.0017	1.0020	16.0033	20.0039	4.0041	35	37	7	3	25	3	7	25	35	37		2	1	0	3
33	31	9.0009	14.0010	1.0013	6.0019	5.0037	14	8	39	21	23	8	14	21	23	39		1	2	1	4
34	32	22.0008	1.0020	20.0031	1.0039	1.0041	3	39	5	37	36	3	5	36	37	39		2	0	1	3
35	33	7.0002	13.0012	1.0013	7.0027	0.0031	20	9	38	18	43	9	18	20	38	43		1	1	2	4
36	34	1.0020	6.0034	7.0040	10.0043	17.0044	39	21	18	13	4	4	13	18	21	39		1	1	1	3
Sheet1

Cell Formulas
Range	Formula
V2	V2	=COUNTIFS(U3:U735,">=4")
L3:L36	L3	=SMALL($G3:$K3,1)
M3:M36	M3	=SMALL($G3:$K3,2)
N3:N36	N3	=SMALL($G3:$K3,3)
O3:O36	O3	=SMALL($G3:$K3,4)
P3:P36	P3	=SMALL($G3:$K3,5)
R3:R36	R3	=COUNTIFS($L3:$P3,">=3",$L3:$P3,"<=10")
S3:S36	S3	=COUNTIFS($L3:$P3,">=19",$L3:$P3,"<=26")
T3:T36	T3	=COUNTIFS($L3:$P3,">=38",$L3:$P3,"<=45")
U3:U36	U3	=SUM(R3:T3)

 

[bebo021999]

Option Explicit
Sub test()
Dim Lr&, i&, j&, sum&, count&
Lr = Cells(Rows.count, "G").End(xlUp).Row
Const pos = 4 ' define position criteria
    For i = 3 To Lr ' start from row 3 till last row
        For j = 7 To 11 ' start from column G to K
            If (Cells(i, j) >= 3 And Cells(i, j) <= 10) Or (Cells(i, j) >= 19 And Cells(i, j) <= 26) Or (Cells(i, j) >= 38 And Cells(i, j) <= 45) Then _
            count = count + 1
        Next
        If count >= pos Then sum = sum + 1
        count = 0
    Next
MsgBox sum ' to read sum
'Or to paste sum into cell V2
'Range("V2").Value = sum
End Sub

 

[spavlou]

Option Explicit
Sub test()
Dim Lr&, i&, j&, sum&, count&
Lr = Cells(Rows.count, "G").End(xlUp).Row
Const pos = 4 ' define position criteria
    For i = 3 To Lr ' start from row 3 till last row
        For j = 7 To 11 ' start from column G to K
            If (Cells(i, j) >= 3 And Cells(i, j) <= 10) Or (Cells(i, j) >= 19 And Cells(i, j) <= 26) Or (Cells(i, j) >= 38 And Cells(i, j) <= 45) Then _
            count = count + 1
        Next
        If count >= pos Then sum = sum + 1
        count = 0
    Next
MsgBox sum ' to read sum
'Or to paste sum into cell V2
'Range("V2").Value = sum
End Sub

I apologize because my English are not very good and I was not very clear about my goal.
However, your code was perfect because it gave me a good idea of how to solve my problem.
Thanks a lot for your time!!!