formula to show number of orders ignoring duplicates between two date ranges

Bvendett4

New Member
Joined
Apr 10, 2018
Messages
24
Hello,
I have a workbook that contains a list of orders amongst a vast amount of other information.
The orders are listed by a sales order due date from oldest to newest and are repeated multiple times but referencing different line items on that order(its almost safe to assume every order will have more than one line with the same due date)
What I am trying to determine is the amount of orders on the book between two dates specific ranges(our financial month).
The order count is constantly changing as we invoice orders off the system and take new orders to go in and out of the same month based on their due date.
I hope the below provides some clarity on what I am trying to achieve, so I need to calculate how many orders(not lines) we need to invoice but I don't want to ignore duplicate orders that have a different due date as this will be an additional invoice on a separate date within the month (I appreciate I could just pivot this but I need it to be constantly updating so I can measure our loading against what we can achieve).
All help will be appreciated.

HTML:
         OrderNo
  AccountName  CustomerOrderNo  AccountNo  Line
  DueDate    BM84285  Koso Kent Introl Limited
  1  K070  15  01/04/19    BM84285  Koso  Kent Introl Limited  1  K070  20  01/04/19    BM84489  Gaffney-Kroese Supply Corp.  2  G085  5  01/04/19    BM85480  WT  Petrotech USA Inc.  3  P516  5  01/04/19    BM85480  WT Petrotech USA Inc.  3  P516  10  01/04/19    BM85480  WT  Petrotech USA Inc.  3  P516  15  01/04/19    BM85480  WT Petrotech USA Inc.  3  P516  20  01/04/19    BM85480  WT  Petrotech USA Inc.  3  P516  25  01/04/19    BM85622  H2R Automation GMBH  4  H154  5  01/04/19    BM85633  H2R  Automation GMBH  4  H154  5  01/04/19    BM85680  JOHN ZINK INT.LUXEMBOURG SARL  5  K063  6  01/04/19    BM85680  JOHN  ZINK INT.LUXEMBOURG SARL  5
  K063  15  01/04/19    BM84285  Koso Kent Introl Limited  1  T013  5  10/04/19
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Apologies for the HTML wrap - please see snapshot intended below
[TABLE="width: 374"]
<colgroup><col width="98" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;" span="2"> <col width="163" style="width: 122pt; mso-width-source: userset; mso-width-alt: 5961;"> <col width="64" style="width: 48pt;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <tbody>[TR]
[TD="width: 98, bgcolor: #DBDBDB"]Order Number[/TD]
[TD="width: 98, bgcolor: #DBDBDB"]Account Name[/TD]
[TD="width: 163, bgcolor: #DBDBDB"]Customer Order Number[/TD]
[TD="width: 64, bgcolor: #DBDBDB"]Line Item[/TD]
[TD="width: 75, bgcolor: #DBDBDB"]Due Date[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]BM84825[/TD]
[TD="bgcolor: transparent"]Company A[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]01/04/2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]BM84825[/TD]
[TD="bgcolor: transparent"]Company A[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]01/04/2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]BM85480[/TD]
[TD="bgcolor: transparent"]Company B[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]01/04/2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]BM85480[/TD]
[TD="bgcolor: transparent"]Company B[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]01/04/2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]BM85480[/TD]
[TD="bgcolor: transparent"]Company B[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: transparent, align: right"]01/04/2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]BM85480[/TD]
[TD="bgcolor: transparent"]Company B[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent, align: right"]01/04/2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]FP40150[/TD]
[TD="bgcolor: transparent"]Company C[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]01/04/2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]FP40150[/TD]
[TD="bgcolor: transparent"]Company C[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]01/04/2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]FP49300[/TD]
[TD="bgcolor: transparent"]Company D[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]01/04/2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]FP49300[/TD]
[TD="bgcolor: transparent"]Company D[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]01/04/2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]BM84825[/TD]
[TD="bgcolor: transparent"]Company A[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: transparent, align: right"]05/04/2019[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,260
Members
452,627
Latest member
KitkatToby

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