Count Dupes only once

[gtd526]

Hello,
Count Dupes only once. If its duplicated 2 or 3 times, then count 1.
Dupes in V:V. Insert formula U214.
Thank you.

Wagers.xlsm
	U	V	W	X
213	Dupes	Team	W/L	SPRD
214		MIL	W	-9
215		TOR	W	-12
216		LAC	L	-9
217		LAC	L	-8
218		MIL	W	-8.5
219		PHX	L	-8
220		UTA	W	-8
221		MIL	W	-10.5
222		PHX	W	-8
223		SAC	L	-8
224		BOS	L	-8
225		GS	W	-8.5
226		MIL	W	-10
227		BRK	W	-8
228		MIL	W	-10.5
229		PHX	L	-10
230		UTA	W	-9
231		BRK	W	-8.5
232		LAC	L	-8.5
233		MIL	L	-9
NBA

Cell Formulas
Range	Formula
V214:V233	V214	=INDEX($H$2:$H$10009,AGGREGATE(15,6,ROW($H$2:$H$10009)-ROW($H$1)/($J$2:$J$10009<=-8),ROW()-ROW($V$213)))
W214:W233	W214	=INDEX($A$2:$A$10009,AGGREGATE(15,6,ROW($H$2:$H$10009)-ROW($H$1)/($J$2:$J$10009<=-8),ROW()-ROW($W$213)))
X214:X233	X214	=INDEX($J$2:$J$10009,AGGREGATE(15,6,ROW($H$2:$H$10009)-ROW($H$1)/($J$2:$J$10009<=-8),ROW()-ROW($X$213)))

 

[jasonb75]

One way

=--(COUNTIF($V$214:$V$233,$V214)>1)

 

[gtd526]

One way

=--(COUNTIF($V$214:$V$233,$V214)>1)

The result is showing 1.

Wagers.xlsm
	U	V	W	X
231		List of <-8 Teams
232		Team	W/L	SPRD
233	1	MIL	W	-9
234		TOR	W	-12
235		LAC	L	-9
236		LAC	L	-8
237		MIL	W	-8.5
238		PHX	L	-8
239		UTA	W	-8
240		MIL	W	-10.5
241		PHX	W	-8
242		SAC	L	-8
243		BOS	L	-8
244		GS	W	-8.5
245		MIL	W	-10
246		BRK	W	-8
247		MIL	W	-10.5
248		PHX	L	-10
249		UTA	W	-9
250		BRK	W	-8.5
251		LAC	L	-8.5
252		MIL	L	-9
253		TOR	0	-8.5
NBA

Cell Formulas
Range	Formula
U233	U233	=--(COUNTIF($V$233:$V$253,$V233)>1)
V233:V253	V233	=INDEX($H$2:$H$10009,AGGREGATE(15,6,ROW($H$2:$H$10009)-ROW($H$1)/($J$2:$J$10009<=-8),ROW()-ROW($V$232)))
W233:W253	W233	=INDEX($A$2:$A$10009,AGGREGATE(15,6,ROW($H$2:$H$10009)-ROW($H$1)/($J$2:$J$10009<=-8),ROW()-ROW($W$232)))
X233:X253	X233	=INDEX($J$2:$J$10009,AGGREGATE(15,6,ROW($H$2:$H$10009)-ROW($H$1)/($J$2:$J$10009<=-8),ROW()-ROW($X$232)))

 

[jasonb75]

The result is showing 1.

That's what you asked for.

If its duplicated 2 or 3 times, then count 1

 

[gtd526]

That's what you asked for.

Maybe I didn't ask the right question.
Its for each set of dupes in the list. If the same value is listed 4 times, only count 1 not 2.

 

[Fluff]

Maybe

=SUMPRODUCT(1/(COUNTIFS(V233:V253,V233:V253)))

 

[gtd526]

Maybe

=SUMPRODUCT(1/(COUNTIFS(V233:V253,V233:V253)))

thank you. can we do it in sections of 2.
i assume if there are 3 with the same value, it equals 1. so just count 2 as a dupe, not 3,4 or 5.
if 4 of the same value is listed it counts as 2 dupes, not 1.

 

[Fluff]

That is not what you asked for.

 

[jasonb75]

Not sure if this is what you want or not, but without a better explanation and the (accurate) expected results in the example rather than just a column of empty cells, it will be my last attempt.

Book1 (version 1).xlsb
	U	V	W	X
231		List of <-8 Teams
232		Team	W/L	SPRD
233	3	MIL	W	-9
234	1	TOR	W	-12
235	1	LAC	L	-9
236	1	LAC	L	-8
237	3	MIL	W	-8.5
238	1	PHX	L	-8
239	1	UTA	W	-8
240	3	MIL	W	-10.5
241	1	PHX	W	-8
242	0	SAC	L	-8
243	0	BOS	L	-8
244	0	GS	W	-8.5
245	3	MIL	W	-10
246	1	BRK	W	-8
247	3	MIL	W	-10.5
248	1	PHX	L	-10
249	1	UTA	W	-9
250	1	BRK	W	-8.5
251	1	LAC	L	-8.5
252	3	MIL	L	-9
253	1	TOR	0	-8.5
Sheet5

Cell Formulas
Range	Formula
U233:U253	U233	=INT(COUNTIF($V$233:$V$253,V233)/2)