Count if uniq id after a filter in a collumn

S

Sindrom

New Member
Joined
Nov 18, 2022
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
Please help me as fallows i have a set of informations wich belongs to different dates (in this case i made an example for 2 dates), i would like to extract only duplicate number and bring the uniq count for it (like if i have 3 same numbers for date 1.11 it would return 1), To look in collumn a for this primary key.

As a helper i ascked you guys for formula in collumn E wich helps me to identify wich doubled or tripeled and so on primary key has no resolution(agent) (and i have to place it for further inspections). After this check in collumn e i need to extract uniq primari key (coll a) for each duplicated primary key and for each date in part.
In collumn F it s a way that i can say if an primary key is uniqe but it applys for all information.

Is there a way how i can sepparate this by date withouth moving my information with filter in another sheet and apply collumn F formula or make an infinite collumns with statements for every day and if a day is equal to something to start and apply the formula (i could also do this if information is always sorted in order) but it would be a pain in the *** and a lot of multplications of formulas.

Count IF uniq ids after a filter in a collumn (1).xlsx
ABCDEFGHIJK
1ANIIncomingCallTimeAgent NameIncomingCallTime
2572448811/1/2022 9:25duma.ana11/1/2022 0:00TRUETRUE
3024694011/1/2022 9:55duma.ana11/1/2022 0:00TRUETRUE
4024763411/1/2022 9:57duma.ana11/1/2022 0:00TRUETRUE
5024774011/1/2022 10:26duma.ana11/1/2022 0:00TRUETRUE
6024774011/1/2022 10:30duma.ana11/1/2022 0:00TRUEFALSE
7024654711/1/2022 11:18duma.ana11/1/2022 0:00TRUETRUE
8024741711/1/2022 12:16duma.ana11/1/2022 0:00TRUETRUE
9024766811/1/2022 12:1811/1/2022 0:00TRUETRUE
10024766811/1/2022 12:2111/1/2022 0:00TRUEFALSE
11024766811/1/2022 12:26duma.ana11/1/2022 0:00TRUEFALSE
12024645511/1/2022 13:06taslaoanu.elena11/1/2022 0:00TRUETRUE
13024694811/1/2022 13:0711/1/2022 0:00TRUETRUE
14354647511/1/2022 13:0911/1/2022 0:00####TRUE
15024633711/1/2022 13:12taslaoanu.elena11/1/2022 0:00TRUETRUE
16354647511/1/2022 13:1211/1/2022 0:00####FALSE
17024694811/1/2022 13:1311/1/2022 0:00TRUEFALSE
18515516611/1/2022 13:1511/1/2022 0:00####TRUE
19515516611/1/2022 13:1511/1/2022 0:00####FALSE
20515516611/1/2022 13:1811/1/2022 0:00####FALSE
21024746211/1/2022 13:1811/1/2022 0:00####TRUEI would like this one not to be taken in count because it is not a double key in this day
22181015511/1/2022 13:34taslaoanu.elena11/1/2022 0:00TRUETRUE
23024736811/1/2022 13:41taslaoanu.elena11/1/2022 0:00TRUETRUE
24361292511/1/2022 14:00taslaoanu.elena11/1/2022 0:00TRUETRUE
25024801011/1/2022 14:48taslaoanu.elena11/1/2022 0:00TRUETRUE
26024653111/1/2022 15:48taslaoanu.elena11/1/2022 0:00TRUETRUE
27024739611/1/2022 15:5211/1/2022 0:00TRUETRUE
28024739611/1/2022 15:57taslaoanu.elena11/1/2022 0:00TRUEFALSE
29024694811/1/2022 16:24taslaoanu.elena11/1/2022 0:00TRUEFALSE
30024680311/2/2022 9:11taslaoanu.elena11/2/2022 0:00TRUETRUE
31024785511/2/2022 9:1111/2/2022 0:00TRUETRUE
32024785511/2/2022 9:1311/2/2022 0:00TRUEFALSE
33024785511/2/2022 9:2111/2/2022 0:00TRUEFALSE
34024748311/2/2022 9:42taslaoanu.elena11/2/2022 0:00TRUETRUE
35702729711/2/2022 9:45taslaoanu.elena11/2/2022 0:00TRUETRUE
36024656011/2/2022 9:4811/2/2022 0:00TRUETRUE
37024656011/2/2022 9:5111/2/2022 0:00TRUEFALSE
38024656011/2/2022 9:56taslaoanu.elena11/2/2022 0:00TRUEFALSE
39024785511/2/2022 10:16taslaoanu.elena11/2/2022 0:00TRUEFALSE
40024819711/2/2022 11:33taslaoanu.elena11/2/2022 0:00TRUETRUE
41546086011/2/2022 11:50taslaoanu.elena11/2/2022 0:00TRUETRUE
42546086011/2/2022 11:57taslaoanu.elena11/2/2022 0:00TRUEFALSE
43024635111/2/2022 12:06duma.ana11/2/2022 0:00TRUETRUE
44515516611/2/2022 12:40duma.ana11/2/2022 0:00TRUETRUE
45024682311/2/2022 13:08duma.ana11/2/2022 0:00TRUETRUE
46024810411/2/2022 13:22duma.ana11/2/2022 0:00TRUETRUE
47024654711/2/2022 14:19duma.ana11/2/2022 0:00TRUETRUE
48024663911/2/2022 16:2411/2/2022 0:00####TRUE
49024663911/2/2022 16:3111/2/2022 0:00####FALSE
50
Sheet1
Cell Formulas
RangeFormula
E2:E49E2=IF(IFERROR(INDEX($C$2:$C$10000,MATCH(1,INDEX(($A$2:$A$10000=A2)*($C$2:$C$10000<>"")*($D$2:$D$10000=D2),0),0)),"")="",FALSE,TRUE)
F2:F29F2=(COUNTIFS(A$2:A2,A2)=1)
F30:F49F30=(COUNTIFS(A$30:A30,A30)=1)
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A30:A49Cell ValueduplicatestextNO
A1:A29Cell ValueduplicatestextNO
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Count if uniq id after a filter in a collumn
and Count if uniq id after a filter in a collumn

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
Fluff said:
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Count if uniq id after a filter in a collumn
and Count if uniq id after a filter in a collumn

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
Click to expand...
Hy can you point me exactly to what forum and link you make reference. Because i simply don`t know wich one. I posted in more than 3 forums. :)
 
Upvote 0
Sindrom said:
Hy can you point me exactly to what forum and link you make reference. Because i simply don`t know wich one. I posted in more than 3 forums. :)
Click to expand...

You can simpy click on the links provided by @Fluff , then you will know
 
Upvote 0
Thank you guys sorry for this, now i understand that these communities are related to eachother and i understand how to and when to cross post. I simply tought that these coummunities are 100 % different.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Formula Help.
Replies
3
Views
73
Fluff
Fluff
A
  • Question
code to copy last row & exclude zero value of the data in last row for each name
Replies
0
Views
130
Abdo
A
B
  • Question
countifs and remove data completed
2
Replies
10
Views
392
MuchToLearn
M
U
  • Question
Chart creation - best presentation?
Replies
2
Views
317
Fluff
Fluff
F
  • Question
Need to find and highlight values from high column which are greater than 10 times low value but on same expiry date,same strike price,same option typ
Replies
3
Views
312
etaf
etaf

Forum statistics

Threads
1,215,106
Messages
6,123,124
Members
449,097
Latest member
mlckr

Share this page

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
Back
Top