Count Dupes only once

gtd526

Active Member
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
UVWX
213DupesTeamW/LSPRD
214MILW-9
215TORW-12
216LACL-9
217LACL-8
218MILW-8.5
219PHXL-8
220UTAW-8
221MILW-10.5
222PHXW-8
223SACL-8
224BOSL-8
225GSW-8.5
226MILW-10
227BRKW-8
228MILW-10.5
229PHXL-10
230UTAW-9
231BRKW-8.5
232LACL-8.5
233MILL-9
NBA
Cell Formulas
RangeFormula
V214:V233V214=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:W233W214=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:X233X214=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)))

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

jasonb75

Well-known Member
One way
Excel Formula:
``=--(COUNTIF(\$V\$214:\$V\$233,\$V214)>1)``

gtd526

Active Member
One way
Excel Formula:
``=--(COUNTIF(\$V\$214:\$V\$233,\$V214)>1)``
The result is showing 1.
Wagers.xlsm
UVWX
231List of <-8 Teams
232TeamW/LSPRD
2331MILW-9
234TORW-12
235LACL-9
236LACL-8
237MILW-8.5
238PHXL-8
239UTAW-8
240MILW-10.5
241PHXW-8
242SACL-8
243BOSL-8
244GSW-8.5
245MILW-10
246BRKW-8
247MILW-10.5
248PHXL-10
249UTAW-9
250BRKW-8.5
251LACL-8.5
252MILL-9
253TOR0-8.5
NBA
Cell Formulas
RangeFormula
U233U233=--(COUNTIF(\$V\$233:\$V\$253,\$V233)>1)
V233:V253V233=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:W253W233=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:X253X233=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)))

gtd526

Active Member

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

MrExcel MVP, Moderator
Maybe
Excel Formula:
``=SUMPRODUCT(1/(COUNTIFS(V233:V253,V233:V253)))``

gtd526

Active Member

Maybe
Excel Formula:
``=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

MrExcel MVP, Moderator
That is not what you asked for.

jasonb75

Well-known Member
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
UVWX
231List of <-8 Teams
232TeamW/LSPRD
2333MILW-9
2341TORW-12
2351LACL-9
2361LACL-8
2373MILW-8.5
2381PHXL-8
2391UTAW-8
2403MILW-10.5
2411PHXW-8
2420SACL-8
2430BOSL-8
2440GSW-8.5
2453MILW-10
2461BRKW-8
2473MILW-10.5
2481PHXL-10
2491UTAW-9
2501BRKW-8.5
2511LACL-8.5
2523MILL-9
2531TOR0-8.5
Sheet5
Cell Formulas
RangeFormula
U233:U253U233=INT(COUNTIF(\$V\$233:\$V\$253,V233)/2)

Replies
3
Views
105
Replies
2
Views
76
Replies
5
Views
181
Replies
0
Views
71
Replies
1
Views
198

1,141,075
Messages
5,704,162
Members
421,331
Latest member
imdumb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back