0

I have two tables (one for purchase order and the other for the invoice) and I want to distribute the quantity from the invoice table to the purchase order invoice quantity column but I want to match the exact quantity from the purchase order quantity.

Here is how the table looks now

Here is how the table looks now

And this is how I want it to look:

enter image description here

How do I manage to do that? Thanks!

Here is the excel attachment file: Book1.xlsx

Update: Is it possible to do that for multiple codes?

enter image description here

redviper2100
  • 265
  • 4
  • 15
  • Changing the conditions of a question sufficiently that valid answers are no longer valid is considered bad forum etiquette. Close this question off and start a new one if you have new requirements. See [Etiquette for Russian Doll Questions](http://meta.stackexchange.com/questions/188625/etiquette-for-russian-doll-questions). –  Jan 18 '16 at 10:54

2 Answers2

1

With Invoice Quantity in the E2 cell and and Qty in the M2 cell, put this formula into E3,

=MAX(MIN(M$3-SUM(E$2:E2), D3), 0)

Fill down as necessary.

        max_min_qty

  • Wow! You worth millions! Would it be possible to do that for multiple codes like this http://i.stack.imgur.com/fI46x.jpg – redviper2100 Jan 18 '16 at 10:33
  • Yes it would. Use the [AGGREGATE function](https://support.office.com/en-us/article/AGGREGATE-function-43B9278E-6AA7-4F17-92B6-E19993FA26DF) for a one column conditional match. There are numerous examples on this site; I've written dozens myself. –  Jan 18 '16 at 10:46
  • I see. Could you give me a little example related to this situation? I'm kinda new to the aggregate function. – redviper2100 Jan 18 '16 at 11:25
  • Actually, I may have spoken prematurely. I completed the formula on your sample worksheet using nothing more complicated than the [VLOOKUP](https://support.office.com/en-us/article/vlookup-function-adceda66-30de-4f26-923b-7257939faa65) and [SUMIF](https://support.office.com/en-us/article/SUMIF-function-169B8C99-C05C-4483-A712-1697A653039B) functions inside the formula provided above. –  Jan 18 '16 at 11:38
0

You can use the Consolidate function to do so.

  1. Click a cell where you want to locate the result in your current worksheet.

  2. Go to click Data > Consolidate

doc-combine-sum3

  1. In the Consolidate dialog box:

    (1.) Select Sum from Function drop down list;

    (2.) Click doc-combine-sum5 button to select the range that you want to consolidate, and then click Add button to add the reference to All references list box;

    (3.) Check Top row and Left column from Use labels in option.

doc-combine-sum4

  1. After finishing the settings, click OK, and the duplicates are combined and summed.

doc-combine-sum6

Note: If the range do not have the header row, you need uncheck Top row from the Use labels in option.

Reference: http://www.extendoffice.com/documents/excel/1268-excel-combine-duplicate-rows-and-sum.html

CustomX
  • 9,948
  • 30
  • 85
  • 115