Formula to count duplicate Values every x # of rows

[inosent1]

I have a column of data, 100 rows deep

Here are the first 15 rows

1
2
3
4
3
6
6
8
8
10
11
12
12
14
15

What I am looking for is a formula I can Ctrl-D that will check every 5 rows and count the number of duplicate values. The result would look like this

Rows 1-5: 1 (3 is duplicated)
Rows 2-6: 1 (3 is duplicated)
Rows 3-7: 2 (3 and 6 is duplicated)
Rows 4-8: 1 (6 is duplicated)

etc

 

[KRice]

Is your profile up to date?…you’re using Excel 2010?

 

[inosent1]

Yes, it is 2010

 

[KRice]

From your initial example, you mention wanting the number of duplicate values (assuming you do not care how many times they are duplicated), but then in your description, you also show in parentheses which numbers are duplicated. Is the information in parentheses shown only to help others understand the answers shown?...or do you want the final results also to show the numbers that are duplicated.

 

[inosent1]

From your initial example, you mention wanting the number of duplicate values (assuming you do not care how many times they are duplicated), but then in your description, you also show in parentheses which numbers are duplicated. Is the information in parentheses shown only to help others understand the answers shown?...or do you want the final results also to show the numbers that are duplicated.

It's just for the sake of clarification for this post. I only need to know if the # of dups in the selected range is >0

 

[KRice]

Try this. You'll need to confirm this as an array formula by entering it with Ctrl-Shift-Enter ...then pull the formula down the sheet.

Book1
	A	B
1	1	1
2	2	1
3	3	2
4	3	2
5	3	2
6	6	2
7	6	1
8	8	2
9	8	1
10	10	2
11	12	2
12	12
13	14
14	14
15	15
Sheet1

Cell Formulas
Range	Formula
B1:B11	B1	=SUMPRODUCT(--(FREQUENCY(OFFSET(A1,,,5),OFFSET(A1,,,5))>1))

 

[inosent1]

Try this. You'll need to confirm this as an array formula by entering it with Ctrl-Shift-Enter ...then pull the formula down the sheet.

Book1
	A	B
1	1	1
2	2	1
3	3	2
4	3	2
5	3	2
6	6	2
7	6	1
8	8	2
9	8	1
10	10	2
11	12	2
12	12
13	14
14	14
15	15
Sheet1

Cell Formulas
Range	Formula
B1:B11	B1	=SUMPRODUCT(--(FREQUENCY(OFFSET(A1,,,5),OFFSET(A1,,,5))>1))

Checking against the data, thanks!

 

[KRice]

And if you want the text before each answer, and to accommodate data beginning on some row other than 1:

Book1
	A	B
1
2	1	Rows 1-6: 1
3	2	Rows 2-7: 1
4	3	Rows 3-8: 2
5	4	Rows 4-9: 1
6	3	Rows 5-10: 2
7	6	Rows 6-11: 2
8	6	Rows 7-12: 1
9	8	Rows 8-13: 1
10	8	Rows 9-14: 1
11	10	Rows 10-15: 1
12	11	Rows 11-16: 1
13	12
14	12
15	14
16	15
Sheet1

Cell Formulas
Range	Formula
B2:B12	B2	="Rows "&ROW(A2)-ROW(A$2)+1&"-"&ROW(A2)-ROW(A$2)+6&": "&SUMPRODUCT(--(FREQUENCY(OFFSET(A2,,,5),OFFSET(A2,,,5))>1))

And if you'd prefer to avoid a volatile function like OFFSET, you could replace those functions with:

A2:INDEX(A:A,ROW(A2)+4)

 

[Fluff]

A non volatile version of KRice's formula

Fluff.xlsm
	A	B
1
2	1	1
3	2	1
4	3	2
5	4	1
6	3	2
7	6	2
8	6	1
9	8	1
10	8	1
11	10	1
12	11	1
13	12	1
14	12	0
15	14	0
16	15
Sheet4

Cell Formulas
Range	Formula
B2:B15	B2	=SUMPRODUCT(--(FREQUENCY(A2:A6,A2:A6)>1))
Press CTRL+SHIFT+ENTER to enter array formulas.