Spread values across rows Excel

Question

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

And this is how I want it to look:

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?

Answer 1

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

3. In the Consolidate dialog box:

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

   (2.) Click 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.

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

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

Answer 2

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.