Count duplicates without knowing value

[Dobo_Bobo]

Please can someone tell me if it possible in Excel to look down a column and count if a value (say a user's ID number) appears more than 50 times?

I've looked via Google but all of the results are for when you have a known value to count.

In my case the data I am using is a list of thousands of users who have raised a fault. What I need to find out is if a user has logged a fault more than 50 times. I know I can do this via a pivot table but long story short I can't use a pivot table.

What would work best (so things are kept simple for other uses) is to have a formula that does the count and also displays the user ID.

So in summary:

In the data which is thousands of rows long I need Excel to count any user who has logged a fault more than 50 times and the result is displayed as user's ID number and in another cell the count total.


If anyone can help I will really appreciate it and I will return to give thanks.

 

[Aladin Akyurek]

Try to post a 10 rows sample.

 

[Dobo_Bobo]

Thanks for your reply Aladin. The data I use is sensitive so I have had to make some up.

So with this example (see image below) in F2 and G2 I need a formula to look in the table and count any user who has logged a fault more than 5 times. In this example Joe Bloggs is the user who has logged faults more than 5 times.

I use data that contains faults logged by thousands of users, therefore I can't use a simple CountIF because there are too many users. A simple pivot table can tell me but I was hoping there is a formula that I can use instead?

Thanks for your help.

 

[Aladin Akyurek]

Is it possible that you post what the picture shows in an Excel readable form?

 

[Dobo_Bobo]

Sorry, I am new to the forum. Is this suitable?

[TABLE="class: grid, width: 269"]
<tbody>[TR]
[TD]CallNo
[/TD]
[TD]User ID
[/TD]
[TD]Name
[/TD]
[TD]Fault
[/TD]
[/TR]
[TR]
[TD="align: right"]12859
[/TD]
[TD="align: right"]25487
[/TD]
[TD]Joe Bloggs
[/TD]
[TD]Desktop won't turn on
[/TD]
[/TR]
[TR]
[TD="align: right"]12863
[/TD]
[TD="align: right"]25487
[/TD]
[TD]Joe Bloggs
[/TD]
[TD]Monitor broken
[/TD]
[/TR]
[TR]
[TD="align: right"]12867
[/TD]
[TD="align: right"]25487
[/TD]
[TD]Joe Bloggs
[/TD]
[TD]Keyboard missing keys
[/TD]
[/TR]
[TR]
[TD="align: right"]12871
[/TD]
[TD="align: right"]24824
[/TD]
[TD]Mary Smith
[/TD]
[TD]Mouse broken
[/TD]
[/TR]
[TR]
[TD="align: right"]12875
[/TD]
[TD="align: right"]25487
[/TD]
[TD]Joe Bloggs
[/TD]
[TD]LAN socket not working
[/TD]
[/TR]
[TR]
[TD="align: right"]12879
[/TD]
[TD="align: right"]27892
[/TD]
[TD]Harold Kay
[/TD]
[TD]Not receiving email
[/TD]
[/TR]
[TR]
[TD="align: right"]12883
[/TD]
[TD="align: right"]51428
[/TD]
[TD]Jenny Jones
[/TD]
[TD]Desktop won't turn on
[/TD]
[/TR]
[TR]
[TD="align: right"]12887
[/TD]
[TD="align: right"]57489
[/TD]
[TD]Clark Kent
[/TD]
[TD]LAN socket not working
[/TD]
[/TR]
[TR]
[TD="align: right"]12891
[/TD]
[TD="align: right"]28749
[/TD]
[TD]Sonia Williams
[/TD]
[TD]LAN socket not working
[/TD]
[/TR]
[TR]
[TD="align: right"]12895
[/TD]
[TD="align: right"]25487
[/TD]
[TD]Joe Bloggs
[/TD]
[TD]Not receiving email
[/TD]
[/TR]
[TR]
[TD="align: right"]12899
[/TD]
[TD="align: right"]25487
[/TD]
[TD]Joe Bloggs
[/TD]
[TD]Desktop won't turn on
[/TD]
[/TR]
[TR]
[TD="align: right"]12903
[/TD]
[TD="align: right"]51287
[/TD]
[TD]Mary Bold
[/TD]
[TD]Monitor broken
[/TD]
[/TR]
[TR]
[TD="align: right"]12907
[/TD]
[TD="align: right"]58934
[/TD]
[TD]Jack Black
[/TD]
[TD]Keyboard missing keys
[/TD]
[/TR]
[TR]
[TD="align: right"]12911
[/TD]
[TD="align: right"]59428
[/TD]
[TD]Dawn French
[/TD]
[TD]Mouse broken
[/TD]
[/TR]
[TR]
[TD="align: right"]12915
[/TD]
[TD="align: right"]59848
[/TD]
[TD]Jennifer Saunders
[/TD]
[TD]LAN socket not working
[/TD]
[/TR]
[TR]
[TD="align: right"]12919
[/TD]
[TD="align: right"]22849
[/TD]
[TD]Harry Jones
[/TD]
[TD]Not receiving email
[/TD]
[/TR]
[TR]
[TD="align: right"]12923
[/TD]
[TD="align: right"]25487
[/TD]
[TD]Joe Bloggs
[/TD]
[TD]Desktop won't turn on
[/TD]
[/TR]
[TR]
[TD="align: right"]12927
[/TD]
[TD="align: right"]54988
[/TD]
[TD]Tim Gold
[/TD]
[TD]LAN socket not working
[/TD]
[/TR]
[TR]
[TD="align: right"]12931
[/TD]
[TD="align: right"]25487
[/TD]
[TD]Joe Bloggs
[/TD]
[TD]LAN socket not working
[/TD]
[/TR]
[TR]
[TD="align: right"]12935
[/TD]
[TD="align: right"]24868
[/TD]
[TD]Kate Williams
[/TD]
[TD]Not receiving email
[/TD]
[/TR]
[TR]
[TD="align: right"]12939
[/TD]
[TD="align: right"]24848
[/TD]
[TD]Dawn Jolly
[/TD]
[TD]Desktop won't turn on
[/TD]
[/TR]
[TR]
[TD="align: right"]12943
[/TD]
[TD="align: right"]56894
[/TD]
[TD]Mike Pickering
[/TD]
[TD]Monitor broken
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Thanks...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr][tr][td]

1​

[/td][td]CallNo[/td][td]User ID[/td][td]Name[/td][td]Fault[/td][td]Count[/td][td][/td][td]

8​

[/td][td]

1​

[/td][/tr]
[tr][td]

2​

[/td][td]

12859​

[/td][td]

25487​

[/td][td]Joe Bloggs[/td][td]Desktop won't turn on[/td][td]

8​

[/td][td][/td][td]

25487​

[/td][td]Joe Bloggs[/td][/tr]
[tr][td]

3​

[/td][td]

12863​

[/td][td]

25487​

[/td][td]Joe Bloggs[/td][td]Monitor broken[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]

12867​

[/td][td]

25487​

[/td][td]Joe Bloggs[/td][td]Keyboard missing keys[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]

12871​

[/td][td]

24824​

[/td][td]Mary Smith[/td][td]Mouse broken[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]

12875​

[/td][td]

25487​

[/td][td]Joe Bloggs[/td][td]LAN socket not working[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]

12879​

[/td][td]

27892​

[/td][td]Harold Kay[/td][td]Not receiving email[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]

12883​

[/td][td]

51428​

[/td][td]Jenny Jones[/td][td]Desktop won't turn on[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]

12887​

[/td][td]

57489​

[/td][td]Clark Kent[/td][td]LAN socket not working[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]

12891​

[/td][td]

28749​

[/td][td]Sonia Williams[/td][td]LAN socket not working[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]

12895​

[/td][td]

25487​

[/td][td]Joe Bloggs[/td][td]Not receiving email[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td]

12899​

[/td][td]

25487​

[/td][td]Joe Bloggs[/td][td]Desktop won't turn on[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td]

12903​

[/td][td]

51287​

[/td][td]Mary Bold[/td][td]Monitor broken[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td]

12907​

[/td][td]

58934​

[/td][td]Jack Black[/td][td]Keyboard missing keys[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

15​

[/td][td]

12911​

[/td][td]

59428​

[/td][td]Dawn French[/td][td]Mouse broken[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

16​

[/td][td]

12915​

[/td][td]

59848​

[/td][td]Jennifer Saunders[/td][td]LAN socket not working[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

17​

[/td][td]

12919​

[/td][td]

22849​

[/td][td]Harry Jones[/td][td]Not receiving email[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

18​

[/td][td]

12923​

[/td][td]

25487​

[/td][td]Joe Bloggs[/td][td]Desktop won't turn on[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

19​

[/td][td]

12927​

[/td][td]

54988​

[/td][td]Tim Gold[/td][td]LAN socket not working[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

20​

[/td][td]

12931​

[/td][td]

25487​

[/td][td]Joe Bloggs[/td][td]LAN socket not working[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

21​

[/td][td]

12935​

[/td][td]

24868​

[/td][td]Kate Williams[/td][td]Not receiving email[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

22​

[/td][td]

12939​

[/td][td]

24848​

[/td][td]Dawn Jolly[/td][td]Desktop won't turn on[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

23​

[/td][td]

12943​

[/td][td]

56894​

[/td][td]Mike Pickering[/td][td]Monitor broken[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

In E2 just enter and copy down:

=IF(ISNA(MATCH($B2,$B$1:$B1,0)),INDEX(FREQUENCY($B$2:$B$23,CHOOSE({1,2},$B2-1,$B2)),2),"")

G1 houses a criterion count.

In H1 just enter:

=COUNTIFS(E2:E23,">="&G1)

In G2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($G$2:G2)>$H$1,"",INDEX($B$2:$B$23,SMALL(IF($E$2:$E$23>=$G$1,ROW($A$2:$A$23)-ROW($A$2)+1),ROWS($G$2:G2))))

In H2 just enter and copy down:

=IF($G2="","",VLOOKUP($G2,B:C,2,0))

 

[Dobo_Bobo]

Wow! Thank you very much Aladin!

My request can be easily done in pivot tables so for you to take the time to write the formulas has been a huge help! Now that I have these formulas it will help me free up time so that I can spend more time researching the formulas you've used.

Many thanks!

 

[Aladin Akyurek]

Wow! Thank you very much Aladin!

My request can be easily done in pivot tables so for you to take the time to write the formulas has been a huge help! Now that I have these formulas it will help me free up time so that I can spend more time researching the formulas you've used.

Many thanks!

Glad to help. Thanks for the like and update.