Enter Quantity from Duplicates in Rows

[kariadill]

Hi,

Looking for a formula that would give me a quantity amount from duplicates in a column as per uploaded file. An order is received for qty of 4 for an item. There are partial deliveries. To sum up order qty field would be incorrect as this would add up to 12 whereas the order quantity was for 4 only.

Hoping for a formula to solve this.

Many thanks,
Kariadill

 

[NARAYANK991]

Hi Kariadill ,

I am not able to understand the logic behind the 4 appearing in cell G3 ; I would have expected it to appear as and when the order is fully shipped i.e. in cell G4.

Instead of just one example , can you post more data so that we can come to the right conclusions ; preferably include orders which have larger quantities , more deliveries etc.

Narayan

 

[kariadill]

Hi Narayan,

Thanks for getting back.
Hope this makes it a little clearer.
The order received has many items that are delivered in full but this item is out of stock for the first delivery. When another delivery is made to the customer only two is delivered and say a week latter on the third delivery the balance is invoiced.
It does not matter on which row the order quantity appears in column G. Column G is then used to sum up the order quantity's received.
If we summed column E we get 12 but the order quantity is only 4 hence the need for column G.

Thanks and kind regards,
Tom

 

[Sajan]

Hi Tom,
Welcome to the forum!

You are better off restructuring your data so that you do not repeat the order qty for each shipment line item. (For example, have an Orders table, and a separate Shipments table.)

But, if you wish to keep the existing layout, you could try:
=IF(SUMPRODUCT(($A$2:$A2=$A2)*($B$2:$B2=$B2)*($C$2:$C2=$C2))=1, SUMPRODUCT(($A$2:$A2=$A2)*($B$2:$B2=$B2)*($C$2:$C2=$C2)*($E$2:$E2)),0)

entered into cell H2.

The formula assumes that the Order Qty would be supplied at least the first time that order is listed in your table.

Cheers,
Sajan.

 

[Xiq]

Hi there, will this formula do?

=IF(COUNTIF($C$2:$C2,$C2)=1,SUMIFS($F$2:$F$4,$C$2:$C$4,C2),)

Edit:
Here is a little modification on the formula to also check columns A and B

=IF(COUNTIF($C$2:$C2,$C2)=1,SUMIFS($F$2:$F$4,$A$2:$A$4,$A2,$B$2:$B$4,$B2,$C$2:$C$4,$C2),)

 

[kariadill]

Sajan and Xiq, thank you very much.
Unfortunately our ERP is such that there is no seperate order table to the shipments table.
Our item codes are 8 digits in length and order numbers are 13 digits so while your formula works the computer processors and memory cannot handle a reasoable size file. May have to take monthly chunks and see if the formulas can give me the required results.

Kind regards,
Tom

 

[Xiq]

Hi Tom,

What you can try is separating the formula in sizable bits.
So something like this:

1. Place this formula in a helper column, lets say column G and then copy down
   

   =COUNTIF($C$2:$C2,$C2)=1

2. After the calculation is done, select column G, copy, paste special "value" (ctrl + alt + v)
3. Place this formula in another column, lets say column H and then copy down
   

   =IF(G2,SUMIFS($F$2:$F$4,$A$2:$A$4,$A2,$B$2:$B$4,$B2,$C$2:$C$4,$C2),)

4. ...
5. Profit!

Maybe you want to remove the formulas in column H after it is done too (same as step 2).