count without duplicate

K

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
460
Office Version
  1. 2019
greeting to all

i was wondering is there any formula that i can quickly return how many order SN total in daily without duplicate calculation?
example: 11 order on 12/29/2023, 18 order on 12/23/2023

Book1
ACE
1Order SnCreated AtUpdated At
223122012181247290812/20/2023 12:18:11 PM12/29/2023
323122012181247290812/20/2023 12:18:11 PM12/29/2023
423122012181247290812/20/2023 12:18:11 PM12/29/2023
523122012181247290812/20/2023 12:18:11 PM12/29/2023
623122113293850664312/21/2023 1:29:38 PM12/29/2023
723122113293850664312/21/2023 1:29:38 PM12/29/2023
823122113293850664312/21/2023 1:29:38 PM12/29/2023
923122113343088138112/21/2023 1:34:30 PM12/29/2023
1023122112364830432212/21/2023 12:36:48 PM12/29/2023
1123122112041678641312/21/2023 12:04:16 PM12/29/2023
1223122112173964697812/21/2023 12:17:39 PM12/29/2023
1323122112061887234512/21/2023 12:06:18 PM12/29/2023
1423122112044492327912/21/2023 12:04:44 PM12/29/2023
1523122112021075330512/21/2023 12:02:10 PM12/29/2023
1623122112590730557512/21/2023 12:59:07 PM12/29/2023
1723122112274392043912/21/2023 12:27:43 PM12/29/2023
1823122112274392043912/21/2023 12:27:43 PM12/29/2023
1923122112274392043912/21/2023 12:27:43 PM12/29/2023
2023122323425474686912/23/2023 11:42:54 PM12/23/2023
2123122323374261449312/23/2023 11:37:42 PM12/23/2023
2223122322261068046912/23/2023 10:26:10 PM12/23/2023
2323122320291110686012/23/2023 8:29:11 PM12/23/2023
2423122320291110686012/23/2023 8:29:11 PM12/23/2023
2523122318363278373212/23/2023 6:36:32 PM12/23/2023
2623122318363278373212/23/2023 6:36:32 PM12/23/2023
2723122318363278373212/23/2023 6:36:32 PM12/23/2023
2823122318021073513312/23/2023 6:02:10 PM12/23/2023
2923122317580230451312/23/2023 5:58:02 PM12/23/2023
3023122317175182098312/23/2023 5:17:51 PM12/23/2023
3123122317175182098312/23/2023 5:17:51 PM12/23/2023
3223122315272869865012/23/2023 3:27:28 PM12/23/2023
3323122314123488995912/23/2023 2:12:34 PM12/23/2023
3423122313492547787412/23/2023 1:49:25 PM12/23/2023
3523122312424390431712/23/2023 12:42:43 PM12/23/2023
3623122312052580813212/23/2023 12:05:25 PM12/23/2023
3723122310485254666512/23/2023 10:48:52 AM12/23/2023
3823122303244757073412/23/2023 3:24:47 AM12/23/2023
3923122300550963460912/23/2023 12:55:09 AM12/23/2023
4023122300521884820112/23/2023 12:52:18 AM12/23/2023
4123122300521884820112/23/2023 12:52:18 AM12/23/2023
4223122300500986120112/23/2023 12:50:09 AM12/23/2023
4323122223450375330812/22/2023 11:45:03 PM12/22/2023
4423122223232060761612/22/2023 11:23:20 PM12/22/2023
4523122223232060761612/22/2023 11:23:20 PM12/22/2023
4623122222551643142712/22/2023 10:55:16 PM12/22/2023
4723122222250472779512/22/2023 10:25:04 PM12/22/2023
4823122222194066971112/22/2023 10:19:40 PM12/22/2023
4923122220163645864612/22/2023 8:16:36 PM12/22/2023
5023122220144079721212/22/2023 8:14:40 PM12/22/2023
5123122220121838867412/22/2023 8:12:18 PM12/22/2023
5223122220112228353712/22/2023 8:11:22 PM12/22/2023
5323122220112228353712/22/2023 8:11:22 PM12/22/2023
5423122220091578892412/22/2023 8:09:15 PM12/22/2023
5523122220091578892412/22/2023 8:09:15 PM12/22/2023
5623122219422227593312/22/2023 7:42:22 PM12/22/2023
5723122219341160318812/22/2023 7:34:11 PM12/22/2023
5823122219341160318812/22/2023 7:34:11 PM12/22/2023
5923122219301766232112/22/2023 7:30:17 PM12/22/2023
6023122219242440067312/22/2023 7:24:24 PM12/22/2023
6123122219242440067312/22/2023 7:24:24 PM12/22/2023
6223122219242440067312/22/2023 7:24:24 PM12/22/2023
6323122219011953773112/22/2023 7:01:19 PM12/22/2023
6423122219011953773112/22/2023 7:01:19 PM12/22/2023
6523122219011953773112/22/2023 7:01:19 PM12/22/2023
6623122218535888480212/22/2023 6:53:58 PM12/22/2023
6723122218470316436712/22/2023 6:47:03 PM12/22/2023
6823122218314396781612/22/2023 6:31:43 PM12/22/2023
6923122218191293013712/22/2023 6:19:12 PM12/22/2023
7023122218171973171912/22/2023 6:17:19 PM12/22/2023
7123122218094523186112/22/2023 6:09:45 PM12/22/2023
7223122218094523186112/22/2023 6:09:45 PM12/22/2023
7323122218030366178112/22/2023 6:03:03 PM12/22/2023
7423122218030366178112/22/2023 6:03:03 PM12/22/2023
7523122217552089584912/22/2023 5:55:20 PM12/22/2023
7623122217524646693712/22/2023 5:52:46 PM12/22/2023
7723122217524646693712/22/2023 5:52:46 PM12/22/2023
Sheet2
 
Sort by date Sort by votes
Do you really have 20,000 rows of data?
 
Upvote 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
With that volume of data I probably would not recommend formulas. Either a pivot table or power query would be a better option.
 
Upvote 0
kelvin_9 said:
how about this?:

thank you very much for your reply, felixstraube

i am curious why do i work with the formula on my work book but not in the macro with run time error 1004?

VBA Code: 
Sub Macro3()
'
' Macro3 Macro
'

'
    Range("C51").Select
    Selection.FormulaArray = _
        "=SUM((FREQUENCY(IFERROR(MATCH(data!R2C1:R20000C1,data!R2C1:R20000C1,0),0)*(INT(data!R2C5:R20000C5)=RC1)*(R49C=data!R2C25:R20000C25),IFERROR(MATCH(data!R2C1:R20000C1,data!R2C1:R20000C1,0),0)*(INT(data!R2C5:R20000C5)=RC1)*(R49C=data!R2C25:R20000C25))>0)*1)-1"
End Sub

thank you very much
Click to expand...


Try this:

VBA Code: 
Sub test_split_formula()
  Dim f1, f2, f3
  f1 = "data!$A$2:$A$20000"
  f2 = "data!$E$2:$E$20000"
  f3 = "data!$Y$2:$Y$20000"
  
  With Range("C52")
    .FormulaArray = "=SUM((FREQUENCY(IFERROR(MATCH(x_x,x_x,0),0)*(INT(y_y)=RC1)*(R50C=z_z)," & _
      "IFERROR(MATCH(x_x,x_x,0),0)*(INT(y_y)=RC1)*(R50C=z_z))>0)*1)-1"
    .Replace "x_x", f1
    .Replace "y_y", f2
    .Replace "z_z", f3
  End With
End Sub


😇
 
Upvote 0
Solution
DanteAmor said:
Try this:

VBA Code: 
Sub test_split_formula()
  Dim f1, f2, f3
  f1 = "data!$A$2:$A$20000"
  f2 = "data!$E$2:$E$20000"
  f3 = "data!$Y$2:$Y$20000"
 
  With Range("C52")
    .FormulaArray = "=SUM((FREQUENCY(IFERROR(MATCH(x_x,x_x,0),0)*(INT(y_y)=RC1)*(R50C=z_z)," & _
      "IFERROR(MATCH(x_x,x_x,0),0)*(INT(y_y)=RC1)*(R50C=z_z))>0)*1)-1"
    .Replace "x_x", f1
    .Replace "y_y", f2
    .Replace "z_z", f3
  End With
End Sub


😇
Click to expand...
thank you very much for your reply, DanteAmor

it is impeccable and wonderful cant believer it's working exactly what i want

last but not least, i think the reason of running this macro slow is because of too many data in my workbook?
a1:ab13089, 2mb file

thank you very much for your guidance
 
Upvote 0
kelvin_9 said:
i think the reason of running this macro slow is because of too many data in my workbook?
a1:ab13089, 2mb file
Click to expand...

That's correct, the macro only puts the array formula in the cells, but in the same way the array formula consumes the same resources as if you put it manually.
To avoid the above, a macro would have to be developed that does what the formula itself does.
But you should create a new thread, since this thread is to solve a problem with formula.
The performance issue is something different that will need to be resolved with a different macro.

🫡
 
Upvote 0
felixstraube said:
how about this?:

CountWithoutDuplicates.xlsx
ABCDEFGHIJAAABACADAEAXAY
1Order SnCreated AtUpdated At
223122012181247200012/20/2023 12:18:11 PM12/29/2023 02:45:49 PMaaa
323122012181247200012/20/2023 12:18:11 PM12/29/2023 02:45:49 PMbbb
423122012181247200012/20/2023 12:18:11 PM12/29/2023 02:45:49 PMccc
523122012181247200012/20/2023 12:18:11 PM12/29/2023 02:45:49 PMddd
623122113293850600012/21/2023 01:29:38 PM12/29/2023 12:39:58 PMeee
723122113293850600012/21/2023 01:29:38 PM12/29/2023 12:39:58 PMfff
823122113293850600012/21/2023 01:29:38 PM12/29/2023 12:39:58 PMggg
923122113343088100012/21/2023 01:34:30 PM12/29/2023 12:35:14 PMhhh
10aaabbbcccdddeeefffggghhhiii23122112364830400012/21/2023 12:36:48 PM12/29/2023 11:24:38 AMiii
1112/29/202341111111123122112041678600012/21/2023 12:04:16 PM12/29/2023 11:12:44 AMaaa
1212/20/202300000000023122112173964600012/21/2023 12:17:39 PM12/29/2023 11:11:27 AMaaa
1312/30/202300000000023122112061887200012/21/2023 12:06:18 PM12/29/2023 10:23:29 AMaaa
1401/02/202400000000023122112044492300012/21/2023 12:04:44 PM12/29/2023 10:20:39 AM
1523122112021075300012/21/2023 12:02:10 PM12/29/2023 10:18:22 AM
1623122112590730500012/21/2023 12:59:07 PM12/29/2023 10:15:14 AM
1723122112274392000012/21/2023 12:27:43 PM12/29/2023 10:14:26 AM
1823122112274392000012/21/2023 12:27:43 PM12/29/2023 10:14:26 AM
1923122112274392000012/21/2023 12:27:43 PM12/29/2023 10:14:26 AM
2023122323425474600012/23/2023 11:42:54 PM12/23/2023 11:43:37 PM
2123122323374261400012/23/2023 11:37:42 PM12/23/2023 11:37:57 PM
2223122322261068000012/23/2023 10:26:10 PM12/23/2023 10:26:38 PM
2323122320291110600012/23/2023 08:29:11 PM12/23/2023 08:29:55 PM
2423122320291110600012/23/2023 08:29:11 PM12/23/2023 08:29:55 PM
2523122318363278300012/23/2023 06:36:32 PM12/23/2023 06:36:57 PM
2623122318363278300012/23/2023 06:36:32 PM12/23/2023 06:36:57 PM
2723122318363278300012/23/2023 06:36:32 PM12/23/2023 06:36:57 PM
2823122318021073500012/23/2023 06:02:10 PM12/23/2023 06:03:23 PM
2923122317580230400012/23/2023 05:58:02 PM12/23/2023 05:59:22 PM
3023122317175182000012/23/2023 05:17:51 PM12/23/2023 05:19:42 PM
3123122317175182000012/23/2023 05:17:51 PM12/23/2023 05:19:42 PM
3223122315272869800012/23/2023 03:27:28 PM12/23/2023 03:27:55 PM
Hoja1
Cell Formulas
RangeFormula
B11:J14B11=SUM((FREQUENCY(IFERROR(MATCH($AA$2:$AA$20000,$AA$2:$AA$20000,0),0)*(INT($AE$2:$AE$20000)=$A11)*(B$10 = $AY$2:$AY$20000),IFERROR(MATCH($AA$2:$AA$20000,$AA$2:$AA$20000,0),0)*(INT($AE$2:$AE$20000)=$A11)*(B$10 = $AY$2:$AY$20000))>0)*1)-1
Click to expand...
sorry for the interrupt again,

if i need one more criteria to match column Z and cell a52
am i correct with below formula?

Excel Formula: 
=SUM((FREQUENCY(IFERROR(MATCH(data!$A$2:$A$20000,data!$A$2:$A$20000,0),0)*(INT(data!$E$2:$E$20000)=$A52)*(INT(data!$Z$2:$Z$20000)=$A52)*(E$50=data!$Y$2:$Y$20000),IFERROR(MATCH(data!$A$2:$A$20000,data!$A$2:$A$20000,0),0)*(INT(data!$E$2:$E$20000)=$A52)*(INT(data!$Z$2:$Z$20000)=$A52)*(E$50=data!$Y$2:$Y$20000))>0)*1)-1

thank you very much again for your answering and guidance
 
Upvote 0
DanteAmor said:
That's correct, the macro only puts the array formula in the cells, but in the same way the array formula consumes the same resources as if you put it manually.
To avoid the above, a macro would have to be developed that does what the formula itself does.
But you should create a new thread, since this thread is to solve a problem with formula.
The performance issue is something different that will need to be resolved with a different macro.

🫡
Click to expand...
thank you very much for your reply, DanteAmor

understood and well noted

thank you very much
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Networkdays formula issue
Replies
3
Views
258
Joe4
Joe4
K
  • Question Question
Populate Based on Actual and Budget
Replies
2
Views
78
kumara_faith
K
S
  • Solved
Count of active employees between dates.
Replies
3
Views
627
Fluff
Fluff
H
  • Solved
Delete the last character of cells in column "K" if it is equal to "X" or "x"
Replies
8
Views
69
Fuji
F
S
  • Question Question
Formula to identify the time & frequency of max value.
Replies
5
Views
265
Cubist
Cubist

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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