Excel duplicates

K

Kariba

Board Regular
Joined
Mar 15, 2023
Messages
59
Office Version
  1. 365
Platform
  1. Windows
Hopefully an easy one. I've set up a system to count occurrences between two dates which are marked as a "1" and then summed up at the top. However, I don't need to count duplicates, but only duplicates within the same date ranges. I know duplicates can be highlighted or deleted, but there will be other dates when they do occur and need to keep those - this is just one day extract.

Tried a few ways, but can't seem avoid counting these duplicates.

Thanks

Book1
ABCDEFL
112
2
3Sun
47
5SufFromTillWeekday12-Nov-23
634804 05-11-202326-11-2023SUNABC1
739200 12-11-202317-12-2023SUNABC1
822054 05-11-202319-11-2023SUNABC1
922054 12-11-202312-11-2023SUNABC1
1012824 05-11-202326-11-2023SUNABC1
1117220 05-11-202324-03-2024SUNABC1
1221616 05-11-202310-12-2023SUNABC1
1326012 05-11-202303-12-2023SUNABC1
1430408 12-11-202319-11-2023SUNABC1
1534804 12-11-202312-11-2023SUNABC1
1639200 12-11-202324-03-2024SUNABC1
1743596 05-11-202326-11-2023SUNABC1
Sheet2
Cell Formulas
RangeFormula
L1L1=SUMIF($F6:$F17,"ABC",L6:L17)
L3L3=TEXT(L5,"DDD")
L4L4=WEEKDAY(L5,2)
L6:L17L6=IF(AND(L$5>=$C6,L$5<=$D6,ISNUMBER(SEARCH(L$3,$E6))),1,0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A6:A17Cell ValueduplicatestextNO
G6:L229Cell Value=0textNO
G6:L229Cell Value=1textNO
G5:L5Dates OccurringtodaytextNO
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
How about
Excel Formula: 
=COUNT(UNIQUE(FILTER(A6:A100,L6:L100=1)))
 
Upvote 0
Solution
Thanks. Was that going in a helper column? Also would this not count any duplicate between row 6 and row 100 and not just unique ones between the date ranges. The whole file is thousands of rows and changes regularly so trying to get an automatic and accurate daily count.

Book3
ABCDEFLMNOP
1120
2
3SunMon
471Expected
5SufFromTillWeekday12-Nov-2313-Nov-23
634804 05-11-202326-11-2023SUNABC1111
739200 12-11-202317-12-2023SUNABC1111
822054 05-11-202319-11-2023SUNABC1111
922054 12-11-202312-11-2023SUNABC1110
1012824 05-11-202326-11-2023SUNABC1001
1117220 05-11-202324-03-2024SUNABC1001
1221616 05-11-202310-12-2023SUNABC1001
1326012 05-11-202303-12-2023SUNABC1001
1430408 12-11-202319-11-2023SUNABC1001
1534804 12-11-202312-11-2023SUNABC1000
1639200 12-11-202324-03-2024SUNABC1000
1743596 05-11-202326-11-2023SUNABC1001
181282406-11-202325-03-2024MONABC011
191722006-11-202320-11-2023MONABC011
202161606-11-202325-03-2024MONABC011
212601206-11-202320-11-2023MONABC011
223040806-11-202325-03-2024MONABC011
232601206-11-202325-03-2024MONABC010
243920006-11-202325-03-2024MONABC011
254359606-11-202308-01-2024MONABC011
264799206-11-202308-01-2024MONABC011
271282406-11-202325-03-2024MONABC010
285678413-11-202301-01-2024MONABC011
Sheet2
Cell Formulas
RangeFormula
L1:M1L1=SUMIF($F6:$F17,"ABC",L6:L17)
L3:M3L3=TEXT(L5,"DDD")
L4:M4L4=WEEKDAY(L5,2)
L6:L17,L18:M28L6=IF(AND(L$5>=$C6,L$5<=$D6,ISNUMBER(SEARCH(L$3,$E6))),1,0)
N6:N17N6=COUNT(UNIQUE(FILTER($A6:$A$100,$L6:$L$100=1)))
O6:O17O6=COUNT(UNIQUE(FILTER(A6:A100,L6:L100=1)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A18:A28Cell ValueduplicatestextNO
A6:A17Cell ValueduplicatestextNO
A18:A28Cell ValueduplicatestextNO
M18:M28Cell Value=0textNO
M18:M28Cell Value=1textNO
L6:M17Cell Value=0textNO
L6:M17Cell Value=1textNO
G29:M229,G6:K17,G18:L28Cell Value=0textNO
G29:M229,G6:K17,G18:L28Cell Value=1textNO
G5:M5Dates OccurringtodaytextNO
 
Upvote 0
That formula goes in L1, not a helper column.
 
Upvote 0
Thanks. I had tried that initially but was getting unexpected result.

Have since realised that some of the data in column A was not in number format so all good now once converted.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
How do I store a battery's state of charge past midnight?
Replies
3
Views
247
NdNoviceHlp
NdNoviceHlp
A
  • Solved
add formulas in last column after sorting for each duplicates ID
Replies
9
Views
126
Peter_SSs
Peter_SSs
L
  • Question Question
Formula Help with Count Number of Vessel Turnaround Trips monthly based on Criteria
Replies
1
Views
455
Lukma
L
A
  • Solved
AVERAGEIFS with 4 criteria
2
Replies
16
Views
750
Fluff
Fluff
C
  • Solved
Countifs Number Of Times Team Play on each weekday
Replies
3
Views
291
CordingBags
C

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,632
Latest member
jladair

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