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?

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

<tbody>
</tbody>

 

[Aladin Akyurek]

Thanks...

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​
1​	CallNo	User ID	Name	Fault	Count		8​	1​
2​	12859​	25487​	Joe Bloggs	Desktop won't turn on	8​		25487​	Joe Bloggs
3​	12863​	25487​	Joe Bloggs	Monitor broken
4​	12867​	25487​	Joe Bloggs	Keyboard missing keys
5​	12871​	24824​	Mary Smith	Mouse broken	1​
6​	12875​	25487​	Joe Bloggs	LAN socket not working
7​	12879​	27892​	Harold Kay	Not receiving email	1​
8​	12883​	51428​	Jenny Jones	Desktop won't turn on	1​
9​	12887​	57489​	Clark Kent	LAN socket not working	1​
10​	12891​	28749​	Sonia Williams	LAN socket not working	1​
11​	12895​	25487​	Joe Bloggs	Not receiving email
12​	12899​	25487​	Joe Bloggs	Desktop won't turn on
13​	12903​	51287​	Mary Bold	Monitor broken	1​
14​	12907​	58934​	Jack Black	Keyboard missing keys	1​
15​	12911​	59428​	Dawn French	Mouse broken	1​
16​	12915​	59848​	Jennifer Saunders	LAN socket not working	1​
17​	12919​	22849​	Harry Jones	Not receiving email	1​
18​	12923​	25487​	Joe Bloggs	Desktop won't turn on
19​	12927​	54988​	Tim Gold	LAN socket not working	1​
20​	12931​	25487​	Joe Bloggs	LAN socket not working
21​	12935​	24868​	Kate Williams	Not receiving email	1​
22​	12939​	24848​	Dawn Jolly	Desktop won't turn on	1​
23​	12943​	56894​	Mike Pickering	Monitor broken	1​

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.