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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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
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

Sufiyan97
  • Solved
VBA Filter Names from List One by One and then Copy filtered data v2
Replies
6
Views
175
Sufiyan97
Sufiyan97
staticfluids
  • Solved
Dynamic Array Formula (FILTER) But Show the Other Criteria with Blank
Replies
4
Views
492
staticfluids
staticfluids
M
  • Solved
Formula Help.
Replies
3
Views
429
Fluff
Fluff
H
  • Solved
Getting the last price for each duplicates ID
Replies
2
Views
128
Hasson
H
T
  • Question Question
How to sum with multiple criteria in 2 different columns? - census data
Replies
1
Views
158
hagia_sofia
hagia_sofia

Forum statistics

Threads
1,223,275
Messages
6,171,126
Members
452,381
Latest member
Nova88

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