CODEWITHSUNDEEP
excel
redviper2100
redviper2100

Reputation: 265

Spread values across rows based on codes

I have two tables (1.Purchase order and 2.The invoice) and I want to spread the quantity from the invoice table to the purchase order invoice quantity column by code but I want to match the exact quantity from the purchase order quantity.

Here is how the table looks now:

Purchase order table and Invoice table enter image description here

And this is how i want it to look:

enter image description here

In this post a formula was suggested

=MAX(MIN(M$2-SUM(E$1:E1), D2), 0)

which I customized to use vlookup so it can match the code,

=MAX(MIN(VLOOKUP(A2,J:M,4,FALSE)-SUM(E$1:E1), D2), 0)

but that doesn't work.

@Jeeped suggested to use AGGREGATE function for a one column conditional match, but can anyone give me an example related to this situation?

Here is the sample Excel file

Thank you!

Upvotes: 1

Views: 70

Answers (1)

user4039065
user4039065

Reputation:

In E2 as,

=MAX(MIN(VLOOKUP(A2, J:N, 4, FALSE)-SUMIFS(E$1:E1, A$1:A1, A2), D2), 0)

What happened to row 1?

Upvotes: 1

Related Questions