Need VBA help Cut and paste in to another locations if data and condition matches.

[Kishan]

Using Excel 2000</SPAN></SPAN>
Hi,</SPAN></SPAN>

It is bit difficult to explain in the brief; so far here I will explain my requirement in the 4 steps to make it easer to understand, sorry for the trouble.</SPAN></SPAN>

Step1</SPAN></SPAN>
This is how looks my data sheet in the columns C, D, E and F, I got 41 data rows and in the columns I, J, K and I got 24 result rows</SPAN></SPAN>
In the G8 I have "SUMPRODUCT formula" copied down to G48, which is checking only I4:L4 result with each row of the columns C, D, E and F,</SPAN></SPAN>
In the G1 there are count for 3 matches and in the G2 there are count for 4 matches sum for both matches are shown in the cell G4</SPAN></SPAN>
Step-1 sheet</SPAN></SPAN>

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Step-1			Match	3	8
2					Match	4	1
3
4						Sum	9
5
6
7		Line nº	P1	P2	P3	P4	Matches		P1	P2	P3	P4		Line nº	P1	P2	P3	P4
8		1	1	1	1	1	4		1	1	1	1
9		2	1	1	1	X	3		1	1	1	X
10		3	1	1	1	2	3		1	1	1	2
11		4	1	1	X	1	3		1	1	X	1
12		5	1	1	X	X	2		1	1	X	X
13		6	1	1	X	2	2		1	1	X	2
14		7	1	1	2	1	3		1	1	2	1
15		8	1	1	2	X	2		1	1	2	X
16		9	1	1	2	2	2		1	X	X	X
17		10	1	X	1	1	3		1	X	X	1
18		11	1	X	1	X	2		X	1	1	2
19		12	1	X	1	2	2		X	1	2	2
20		13	1	X	X	1	2		X	X	1	2
21		14	1	X	X	X	1		X	X	X	1
22		15	1	X	X	2	1		X	X	2	X
23		16	1	X	2	1	2		X	X	2	2
24		17	1	X	2	X	1		X	2	X	X
25		18	1	X	2	2	1		X	2	X	2
26		19	1	2	1	1	3		X	2	2	1
27		20	1	2	1	X	2		X	2	2	X
28		21	1	2	1	2	2		X	2	2	2
29		22	1	2	X	1	2		2	1	1	1
30		23	1	2	X	X	1		2	1	1	X
31		24	1	2	X	2	1		2	1	1	2
32		25	1	2	2	1	2
33		26	1	2	2	2	1
34		27	X	1	1	1	3
35		28	X	1	1	X	2
36		29	X	X	1	2	1
37		30	X	X	X	1	1
38		31	X	X	X	X	0
39		32	X	X	X	2	0
40		33	X	2	2	2	0
41		34	2	1	1	1	3
42		35	2	1	1	X	2
43		36	2	X	1	2	1
44		37	2	X	X	1	1
45		38	2	X	X	X	0
46		39	2	X	X	2	0
47		40	2	X	2	X	0
48		41	2	X	2	2	0
49

<COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0"><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Step-1​

Worksheet Formulas Cell Formula G1 =COUNTIF(G8:G48,F1) G2 =COUNTIF(G8:G48,F2) G4 =SUM(G1:G2) G8 =SUMPRODUCT(--($I$8:$L$8=C8:F8)) <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

Step-2</SPAN></SPAN>
Here I want a VBA that can check all the result (of course changing the row numbers of the I:J columns in the G8 SUMPRODUCT formula) "one by one with each of the row are find in the columns C, D, E and F" and if the Sum of cell G4 is = 9 than filter the match greeter than 2 in the column G and cut them from column B, C, D, E and F" and paste in to the columns N:R as shown sheet below </SPAN></SPAN>
Step-2 sheet</SPAN></SPAN>

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Step-2			Match	3	0
2					Match	4	0
3
4						Sum	0
5
6
7		Line nº	P1	P2	P3	P4	Matches		P1	P2	P3	P4		Line nº	P1	P2	P3	P4
8							0		1	1	1	1		1	1	1	1	1
9							0		1	1	1	X		2	1	1	1	X
10							0		1	1	1	2		3	1	1	1	2
11							0		1	1	X	1		4	1	1	X	1
12		5	1	1	X	X	2		1	1	X	X		7	1	1	2	1
13		6	1	1	X	2	2		1	1	X	2		10	1	X	1	1
14							0		1	1	2	1		19	1	2	1	1
15		8	1	1	2	X	2		1	1	2	X		27	X	1	1	1
16		9	1	1	2	2	2		1	X	X	X		34	2	1	1	1
17							0		1	X	X	1
18		11	1	X	1	X	2		X	1	1	2
19		12	1	X	1	2	2		X	1	2	2
20		13	1	X	X	1	2		X	X	1	2
21		14	1	X	X	X	1		X	X	X	1
22		15	1	X	X	2	1		X	X	2	X
23		16	1	X	2	1	2		X	X	2	2
24		17	1	X	2	X	1		X	2	X	X
25		18	1	X	2	2	1		X	2	X	2
26							0		X	2	2	1
27		20	1	2	1	X	2		X	2	2	X
28		21	1	2	1	2	2		X	2	2	2
29		22	1	2	X	1	2		2	1	1	1
30		23	1	2	X	X	1		2	1	1	X
31		24	1	2	X	2	1		2	1	1	2
32		25	1	2	2	1	2
33		26	1	2	2	2	1
34							0
35		28	X	1	1	X	2
36		29	X	X	1	2	1
37		30	X	X	X	1	1
38		31	X	X	X	X	0
39		32	X	X	X	2	0
40		33	X	2	2	2	0
41							0
42		35	2	1	1	X	2
43		36	2	X	1	2	1
44		37	2	X	X	1	1
45		38	2	X	X	X	0
46		39	2	X	X	2	0
47		40	2	X	2	X	0
48		41	2	X	2	2	0
49

<COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0"><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Step-2​

Worksheet Formulas Cell Formula G1 =COUNTIF(G8:G48,3) G2 =COUNTIF(G8:G48,4) G4 =SUM(G1:G2) G8 =SUMPRODUCT(--($I$8:$L$8=C8:F8)) <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

Step-3</SPAN></SPAN>
Checking the next matches it has found with G4 Sum = 9 with the row I16:L16 filter greeter than 2 in the column G and cut them from column B, C, D, E and F" and paste in to the columns N:R below the first match as shown sheet below </SPAN></SPAN>
Step-3 sheet</SPAN></SPAN>

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Step-3			Match	3	8
2					Match	4	1
3
4						Sum	9
5
6
7		Line nº	P1	P2	P3	P4	Matches		P1	P2	P3	P4		Line nº	P1	P2	P3	P4
8							0		1	1	1	1		1	1	1	1	1
9							0		1	1	1	X		2	1	1	1	X
10							0		1	1	1	2		3	1	1	1	2
11							0		1	1	X	1		4	1	1	X	1
12		5	1	1	X	X	3		1	1	X	X		7	1	1	2	1
13		6	1	1	X	2	2		1	1	X	2		10	1	X	1	1
14							0		1	1	2	1		19	1	2	1	1
15		8	1	1	2	X	2		1	1	2	X		27	X	1	1	1
16		9	1	1	2	2	1		1	X	X	X		34	2	1	1	1
17							0		1	X	X	1
18		11	1	X	1	X	3		X	1	1	2
19		12	1	X	1	2	2		X	1	2	2
20		13	1	X	X	1	3		X	X	1	2
21		14	1	X	X	X	4		X	X	X	1
22		15	1	X	X	2	3		X	X	2	X
23		16	1	X	2	1	2		X	X	2	2
24		17	1	X	2	X	3		X	2	X	X
25		18	1	X	2	2	2		X	2	X	2
26							0		X	2	2	1
27		20	1	2	1	X	2		X	2	2	X
28		21	1	2	1	2	1		X	2	2	2
29		22	1	2	X	1	2		2	1	1	1
30		23	1	2	X	X	3		2	1	1	X
31		24	1	2	X	2	2		2	1	1	2
32		25	1	2	2	1	1
33		26	1	2	2	2	1
34							0
35		28	X	1	1	X	1
36		29	X	X	1	2	1
37		30	X	X	X	1	2
38		31	X	X	X	X	3
39		32	X	X	X	2	2
40		33	X	2	2	2	0
41							0
42		35	2	1	1	X	1
43		36	2	X	1	2	1
44		37	2	X	X	1	2
45		38	2	X	X	X	3
46		39	2	X	X	2	2
47		40	2	X	2	X	2
48		41	2	X	2	2	1
49

<COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0"><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Step-3​

Worksheet Formulas Cell Formula G1 =COUNTIF(G8:G48,3) G2 =COUNTIF(G8:G48,4) G4 =SUM(G1:G2) G8 =SUMPRODUCT(--($I$16:$L$16=C8:F8)) <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

Step-4</SPAN></SPAN>
Here is the final sheet find 2 match only within whole data with sum of 9 in the cell G4</SPAN></SPAN>
Step-4 Sheet

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Step-4			Match	3	0
2					Match	4	0
3
4						Sum	0
5
6
7		Line n	P1	P2	P3	P4	Matches		P1	P2	P3	P4		Line n	P1	P2	P3	P4
8							0		1	1	1	1		1	1	1	1	1
9							0		1	1	1	X		2	1	1	1	X
10							0		1	1	1	2		3	1	1	1	2
11							0		1	1	X	1		4	1	1	X	1
12							0		1	1	X	X		7	1	1	2	1
13		6	1	1	X	2	2		1	1	X	2		10	1	X	1	1
14							0		1	1	2	1		19	1	2	1	1
15		8	1	1	2	X	2		1	1	2	X		27	X	1	1	1
16		9	1	1	2	2	1		1	X	X	X		34	2	1	1	1
17							0		1	X	X	1
18							0		X	1	1	2		5	1	1	X	X
19		12	1	X	1	2	2		X	1	2	2		11	1	X	1	X
20							0		X	X	1	2		13	1	X	X	1
21							0		X	X	X	1		14	1	X	X	X
22							0		X	X	2	X		15	1	X	X	2
23		16	1	X	2	1	2		X	X	2	2		17	1	X	2	X
24							0		X	2	X	X		23	1	2	X	X
25		18	1	X	2	2	2		X	2	X	2		31	X	X	X	X
26							0		X	2	2	1		38	2	X	X	X
27		20	1	2	1	X	2		X	2	2	X
28		21	1	2	1	2	1		X	2	2	2
29		22	1	2	X	1	2		2	1	1	1
30							0		2	1	1	X
31		24	1	2	X	2	2		2	1	1	2
32		25	1	2	2	1	1
33		26	1	2	2	2	1
34							0
35		28	X	1	1	X	1
36		29	X	X	1	2	1
37		30	X	X	X	1	2
38							0
39		32	X	X	X	2	2
40		33	X	2	2	2	0
41							0
42		35	2	1	1	X	1
43		36	2	X	1	2	1
44		37	2	X	X	1	2
45							0
46		39	2	X	X	2	2
47		40	2	X	2	X	2
48		41	2	X	2	2	1
49
Step-4

Cell Formulas
Range	Formula
G1	=COUNTIF(G8:G48,3)
G2	=COUNTIF(G8:G48,4)
G4	=SUM(G1:G2)
G8	=SUMPRODUCT(--($I$16:$L$16=C8:F8))

I believe it is explained and understand also it is not an easy task hope some one please can take a look and provide a VBA solution </SPAN></SPAN>

Thank you in advance</SPAN></SPAN>

Regards,</SPAN>
Kishan</SPAN></SPAN>

 

[Kishan]

Bump