Customer Order Number with Multiple Invoice Numbers

[stevenhn]

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 Number	Invoice 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

 

[Weazel]

not sure if this is what you mean but maybe something like...

Excel 2010

	A	B	C	D	E
1	Order Number	Invoice Number		Order #	Deliveries
2	00/001027/	365077		00/001027/	2
3	00/001027/	365077
4	00/001027/	365077
5	00/001027/	365149
6	00/001027/	365149

<tbody>
</tbody>

Sheet3​

Array Formulas Cell Formula 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]

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]

yes, you would need to hold down control and shift then hit enter because its an array formula

 

[stevenhn]

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

 

[Weazel]

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