Count Dupes only once

gtd526

Active Member
Joined
Jul 30, 2013
Messages
418
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
13,534
Office Version
  1. 365
Platform
  1. Windows
One way
Excel Formula:
=--(COUNTIF($V$214:$V$233,$V214)>1)
 

gtd526

Active Member
Joined
Jul 30, 2013
Messages
418
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jul 30, 2013
Messages
418
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
Maybe
Excel Formula:
=SUMPRODUCT(1/(COUNTIFS(V233:V253,V233:V253)))
 

gtd526

Active Member
Joined
Jul 30, 2013
Messages
418
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
That is not what you asked for.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,534
Office Version
  1. 365
Platform
  1. Windows
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)
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top