Customer Order Number with Multiple Invoice Numbers

stevenhn

New Member
Joined
Sep 30, 2014
Messages
3
Hi,

I need to find out how many deliveries it took my company to complete an order. Each delivery has a different invoice number, but the customers order number remains the same. So, as per example 1 below, there are 2 invoice numbers against the 1 order number so it took 2 deliveries to complete the order.

Example 1
Order NumberInvoice Number
00/001027/365077
00/001027/365077
00/001027/365077
00/001027/365149
00/001027/365149

<tbody>
</tbody>

As you can see the order number and invoice numbers are repeated, this is because they both go down to the line item detail on the order and invoice as per example 2 below. i.e. these were the items ordered and supplied.
Example 2
00/001027/

365077

socket
00/001027/365077 plug
00/001027/365077 light
00/001027/365149 cable
00/001027/365149 mcb

<tbody>
</tbody>

It does not matter for the purpose of my report what items were delivered and I have only shown it in example 2 so you can see why I have so many repeated order and invoice numbers.

So, I need to know how to look at the order number, see how many different invoice numbers are against that order number to calculate how many deliveries that equates to as per example 1 above. I don't know if this will make a difference to any formula but some of the orders have only 1 invoice so they were completed in 1 delivery.

I hope this makes sense and that someone out there can help. I can provide more data if required.

Thank you in antisipation.

Steve
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
not sure if this is what you mean but maybe something like...

Excel 2010
ABCDE
1Order NumberInvoice NumberOrder #Deliveries
200/001027/36507700/001027/2
300/001027/365077
400/001027/365077
500/001027/365149
600/001027/365149

<tbody>
</tbody>
Sheet3

Array Formulas
CellFormula
E2{=SUM(IF(FREQUENCY(IF($B$2:$B$6<>"",IF(A2:A6=D2,MATCH(B2:B6,B2:B6,0))),ROW(B2:B6)-ROW(B2)+1),1))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 

stevenhn

New Member
Joined
Sep 30, 2014
Messages
3
Sorry Weazel I forgot to mention that I am using Excel 2013 not sure if that make a difference. Also not sure what you mean by using Ctrl+Shift+Enter. Do I type in your formula then hit Ctrl+Shift+Enter?
 

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
yes, you would need to hold down control and shift then hit enter because its an array formula
 

stevenhn

New Member
Joined
Sep 30, 2014
Messages
3
Sorry Weazel I keep getting this error message.

"Microsoft Excel cannot calculate a formula. There is a circular reference in an open workbook, but the references that cause it cannot be listed for you. Try editing the last formula you entered or removing it with the undo command"

Any ideas?

Thanks so far.

Steve
 
Last edited:

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
its hard to say, normally excel will tell you where the circular reference is.

you may want to double check the formula ranges

if you look on the formula menu under formula auditing click the drop down by Error Checking and select Circular references and see if the cell is listed
 

Watch MrExcel Video

Forum statistics

Threads
1,122,421
Messages
5,596,033
Members
414,039
Latest member
southike

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
Top