Count various duplicate ranges

[motilulla]

Hello,

I want to count various duplicate ranges in the separate columns, I got numbers in the columns C:G In the range C6 to G down and in the column "I" there is a text "End" it mean End of the row for first range for count of duplicates...and then new count start till next End...and so on continue....

In the column K there are unique numbers 1 to 25 which I want to use for duplicate counts.

For example--1st range will be C6:G12 count of duplicate result I want in L6 to down, for now in this example I have used Formula "=COUNTIF($C$6:$G$12,$K6)" copied down to L30

For example--2nd range will be C13:G18 count of duplicate result I want in M6 to down, for now in this example I have used Formula "=COUNTIF($C$13:$G$18,$K6)" copied down to L30

As I got long data list I need a VBA please help

*	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3												Count	Count	Count	Count	Count
4												Duplicate	Duplicate	Duplicate	Duplicate	Duplicate
5			n1	n2	n3	n4	n5		Status		Uniques	Till End	End	End	End	?
6			24	34	35	42	46				1	1	0	1	1	1
7			20	27	34	46	50				2	0	0	1	1	0
8			11	17	21	48	50				3	0	1	1	1	0
9			1	16	38	42	45				4	0	2	0	2	0
10			11	17	18	39	44				5	0	0	0	1	0
11			13	15	30	35	42				6	1	1	0	0	1
12			6	15	22	37	44		End		7	0	2	1	2	3
13			3	10	23	27	44				8	0	0	1	0	3
14			6	7	38	39	44				9	0	0	1	0	0
15			4	19	34	41	43				10	0	1	0	2	0
16			7	25	27	48	49				11	2	0	0	1	0
17			20	23	26	30	33				12	0	0	0	0	0
18			4	16	21	36	42		End		13	1	0	0	2	1
19			9	16	18	19	21				14	0	0	1	0	0
20			14	17	28	29	39				15	2	0	0	0	0
21			2	7	22	27	40				16	1	1	2	2	0
22			31	32	38	40	42				17	2	0	1	0	1
23			1	3	16	24	37				18	1	0	2	1	0
24			8	18	25	35	46		End		19	0	1	1	1	1
25			2	7	24	43	46				20	1	1	0	0	1
26			10	19	23	45	49				21	1	1	1	0	0
27			4	16	18	25	44				22	1	0	1	0	1
28			1	4	7	10	50				23	0	2	0	1	0
29			11	16	24	38	40				24	1	0	1	2	2
30			13	36	40	43	50				25	0	1	1	1	1
31			3	5	34	40	49
32			13	27	28	42	43		End
33			1	32	35	45	48
34			7	8	24	27	36
35			6	20	24	25	50
36			7	8	19	28	29
37			7	17	22	27	40
38			8	13	26	34	38
39			27	28	29	34	43
40
41
42

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[Marcelo Branco]

Try something like this...
Helper cells in Blue
Insert End in I39

Pasta1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1									Start	1	8	14	20	28
2									End	7	13	19	27	34
3										Count	Count	Count	Count	Count
4										Duplicate	Duplicate	Duplicate	Duplicate	Duplicate
5	n1	n2	n3	n4	n5		Status		Uniques	Till End	End	End	End	?
6	24	34	35	42	46				1	1	0	1	1	1
7	20	27	34	46	50				2	0	0	1	1	0
8	11	17	21	48	50				3	0	1	1	1	0
9	1	16	38	42	45				4	0	2	0	2	0
10	11	17	18	39	44				5	0	0	0	1	0
11	13	15	30	35	42				6	1	1	0	0	1
12	6	15	22	37	44		End		7	0	2	1	2	3
13	3	10	23	27	44				8	0	0	1	0	3
14	6	7	38	39	44				9	0	0	1	0	0
15	4	19	34	41	43				10	0	1	0	2	0
16	7	25	27	48	49				11	2	0	0	1	0
17	20	23	26	30	33				12	0	0	0	0	0
18	4	16	21	36	42		End		13	1	0	0	2	1
19	9	16	18	19	21				14	0	0	1	0	0
20	14	17	28	29	39				15	2	0	0	0	0
21	2	7	22	27	40				16	1	1	2	2	0
22	31	32	38	40	42				17	2	0	1	0	1
23	1	3	16	24	37				18	1	0	2	1	0
24	8	18	25	35	46		End		19	0	1	1	1	1
25	2	7	24	43	46				20	1	1	0	0	1
26	10	19	23	45	49				21	1	1	1	0	0
27	4	16	18	25	44				22	1	0	1	0	1
28	1	4	7	10	50				23	0	2	0	1	0
29	11	16	24	38	40				24	1	0	1	2	2
30	13	36	40	43	50				25	0	1	1	1	1
31	3	5	34	40	49
32	13	27	28	42	43		End
33	1	32	35	45	48
34	7	8	24	27	36
35	6	20	24	25	50
36	7	8	19	28	29
37	7	17	22	27	40
38	8	13	26	34	38
39	27	28	29	34	43		End
Plan2

Cell Formulas
Range	Formula
L1:P1	L1	=N(K2)+1
L2:P2	L2	=MATCH($K2,INDEX($I$6:$I$39,L$1):$I$39,0)+L1-1
L6:P30	L6	=COUNTIF(INDEX($C$6:$C$39,L$1):INDEX($G$6:$G$39,L$2),$K6)

M.

 

[motilulla]

Try something like this...
Helper cells in Blue
Insert End in I39

Pasta1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1									Start	1	8	14	20	28
2									End	7	13	19	27	34
3										Count	Count	Count	Count	Count
4										Duplicate	Duplicate	Duplicate	Duplicate	Duplicate
5	n1	n2	n3	n4	n5		Status		Uniques	Till End	End	End	End	?
6	24	34	35	42	46				1	1	0	1	1	1
7	20	27	34	46	50				2	0	0	1	1	0
8	11	17	21	48	50				3	0	1	1	1	0
9	1	16	38	42	45				4	0	2	0	2	0
10	11	17	18	39	44				5	0	0	0	1	0
11	13	15	30	35	42				6	1	1	0	0	1
12	6	15	22	37	44		End		7	0	2	1	2	3
13	3	10	23	27	44				8	0	0	1	0	3
14	6	7	38	39	44				9	0	0	1	0	0
15	4	19	34	41	43				10	0	1	0	2	0
16	7	25	27	48	49				11	2	0	0	1	0
17	20	23	26	30	33				12	0	0	0	0	0
18	4	16	21	36	42		End		13	1	0	0	2	1
19	9	16	18	19	21				14	0	0	1	0	0
20	14	17	28	29	39				15	2	0	0	0	0
21	2	7	22	27	40				16	1	1	2	2	0
22	31	32	38	40	42				17	2	0	1	0	1
23	1	3	16	24	37				18	1	0	2	1	0
24	8	18	25	35	46		End		19	0	1	1	1	1
25	2	7	24	43	46				20	1	1	0	0	1
26	10	19	23	45	49				21	1	1	1	0	0
27	4	16	18	25	44				22	1	0	1	0	1
28	1	4	7	10	50				23	0	2	0	1	0
29	11	16	24	38	40				24	1	0	1	2	2
30	13	36	40	43	50				25	0	1	1	1	1
31	3	5	34	40	49
32	13	27	28	42	43		End
33	1	32	35	45	48
34	7	8	24	27	36
35	6	20	24	25	50
36	7	8	19	28	29
37	7	17	22	27	40
38	8	13	26	34	38
39	27	28	29	34	43		End
Plan2

Cell Formulas
Range	Formula
L1:P1	L1	=N(K2)+1
L2:P2	L2	=MATCH($K2,INDEX($I$6:$I$39,L$1):$I$39,0)+L1-1
L6:P30	L6	=COUNTIF(INDEX($C$6:$C$39,L$1):INDEX($G$6:$G$39,L$2),$K6)

M.

Marcelo Branco, after adapting your formulas with my original worksheet with large data when I obtain all the result so accurate really I get amazed for me it has a magical formula solution. ?

And has worked for me more better than my expectations best part is a just adding a "End" text any where in the column "I" it recalculate fast and with accuracy

I am so happy by your work. Thank you so much for your help and time you took to giving a best solution

Good Luck! Have a good day

Kind Regards,
Moti

 

[Marcelo Branco]

You're welcome. Glad to help.

M.