Count only last duplicate or same values.

[motilulla]

Using Excel 2010
Hello,

I need a formula which can Count only last duplicate or same values. Data are in cells C6:P13 and result of counts in range C2:P3

Count Last Duplicates.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2	Count Last-->	1		2				4	2	1	6	1		1	3
3	Count Last-->	0	3		4	2	1						4			6
4
5			n1	n2	n3	n4	n5	n6	n7	n8	n9	n10	n11	n12	n13	n14
6			0	1	1	0	1	0	1	0	1	1	0	1	0	0
7			1	1	0	1	1	0	1	0	0	1	1	1	0	1
8			1	1	1	1	1	0	1	0	1	1	1	1	0	0
9			1	0	1	1	0	0	1	1	1	0	1	1	1	0
10			1	1	0	0	1	1	1	0	1	1	0	0	0	0
11			0	0	0	1	0	1	0	1	1	1	0	0	1	0
12			0	1	0	0	1	1	1	0	1	0	0	0	1	0
13			0	1	0	0	0	1	1	1	1	1	0	1	1	0
14
15
Count Last Duplicates

Regards,
Moti

 

[kevin9999]

Until a more elegant solution comes along...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	Count Last-->	1		2				4	2	1	6	1		1	3
3	Count Last-->	0	3		4	2	1						4			6
4
5			n1	n2	n3	n4	n5	n6	n7	n8	n9	n10	n11	n12	n13	n14
6			0	1	1	0	1	0	1	0	1	1	0	1	0	0
7			1	1	0	1	1	0	1	0	0	1	1	1	0	1
8			1	1	1	1	1	0	1	0	1	1	1	1	0	0
9			1	0	1	1	0	0	1	1	1	0	1	1	1	0
10			1	1	0	0	1	1	1	0	1	1	0	0	0	0
11			0	0	0	1	0	1	0	1	1	1	0	0	1	0
12			0	1	0	0	1	1	1	0	1	0	0	0	1	0
13			0	1	0	0	0	1	1	1	1	1	0	1	1	0
14
Sheet1

Cell Formulas
Range	Formula
C2:P2	C2	=IF(C$13=0,"",8-(AGGREGATE(14, 6, ROW($6:$13)/(C$6:C$13=0), 1)-5))
C3:P3	C3	=IF(C$13=1,"",8-(AGGREGATE(14, 6, ROW($6:$13)/(C$6:C$13=1), 1)-5))

 

[motilulla]

@kevin9999, as far as answer is correct solution is beautiful. It work perfect with sample data. But of course in the original data rows will be extending when new data added. After I change the range I am getting following result please can you take a look?

I do appreciate your help. Good Luck!

Count Last Duplicates.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2	Count Last-->	1
3	Count Last-->	0	-2	-2	-1	-2	-2	0	-2	-1	-1	-2	-2	-2	-1	-2
4
5			n1	n2	n3	n4	n5	n6	n7	n8	n9	n10	n11	n12	n13	n14
6			0	1	1	0	1	0	1	0	1	1	0	1	0	0
7			1	1	0	1	1	0	1	0	0	1	1	1	0	1
8			1	1	1	1	1	0	1	0	1	1	1	1	0	0
9			1	0	1	1	0	0	1	1	1	0	1	1	1	0
10			1	1	0	0	1	1	1	0	1	1	0	0	0	0
11			0	0	0	1	0	1	0	1	1	1	0	0	1	0
12			0	1	0	0	1	1	1	0	1	0	0	0	1	0
13			0	1	0	0	0	1	1	1	1	1	0	1	1	0
14			1	0	1	1	0	0	1	1	1	0	1	1	1	0
15			1	1	0	1	1	0	1	0	0	1	1	1	0	1
16
17
Count Last Duplicates

Cell Formulas
Range	Formula
C2:P2	C2	=IF(C$1013=0,"",8-(AGGREGATE(14, 6, ROW($6:$1013)/(C$6:C$1013=0), 1)-5))
C3:P3	C3	=IF(C$1013=1,"",8-(AGGREGATE(14, 6, ROW($6:$1013)/(C$6:C$1013=1), 1)-5))

Kind Regards,
Moti

 

[kevin9999]

Bit difficult without the full data you're using, but try changing the 8-( part of the formulas to 1008-( and see if that does what you want.

 

[motilulla]

@kevin9999, changing as per your instruction I am getting following results not sure may be VBA require for this problem.

Count Last Duplicates.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2	Count Last-->	1
3	Count Last-->	0	998	998	999	998	998	1000	998	999	999	998	998	998	999	998
4
5			n1	n2	n3	n4	n5	n6	n7	n8	n9	n10	n11	n12	n13	n14
6			0	1	1	0	1	0	1	0	1	1	0	1	0	0
7			1	1	0	1	1	0	1	0	0	1	1	1	0	1
8			1	1	1	1	1	0	1	0	1	1	1	1	0	0
9			1	0	1	1	0	0	1	1	1	0	1	1	1	0
10			1	1	0	0	1	1	1	0	1	1	0	0	0	0
11			0	0	0	1	0	1	0	1	1	1	0	0	1	0
12			0	1	0	0	1	1	1	0	1	0	0	0	1	0
13			0	1	0	0	0	1	1	1	1	1	0	1	1	0
14			1	0	1	1	0	0	1	1	1	0	1	1	1	0
15			1	1	0	1	1	0	1	0	0	1	1	1	0	1
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Count Last Duplicates

Cell Formulas
Range	Formula
C2:P2	C2	=IF(C$1013=0,"",1008-(AGGREGATE(14, 6, ROW($6:$1013)/(C$6:C$1013=0), 1)-5))
C3:P3	C3	=IF(C$1013=1,"",1008-(AGGREGATE(14, 6, ROW($6:$1013)/(C$6:C$1013=1), 1)-5))

Kind Regards,
Moti

 

[motilulla]

Please refer cross post link: Count only last duplicate or same values.

 

[motilulla]

Please check the link above query is solved by JohnTopley. thank you everyone who has looked this request.