# Thread: Help With A function To Pull Quantity

1. ## Help With A function To Pull Quantity

Hi All,

I need help with the attached. The section highlighted in blue is a report that i pull off of our system and the part highlighted in Green is a stock list that i need to create per location.

So i need H2 to have a vlookup to bring back the value in D2 and I2 to return the value in D5. However i need to put in a function that looks at the plant Column C and if it is CT01 it places the available under CT Column H. I hope this makes sense.  Reply With Quote

2. In the attached 3 solutions:
1. On Sheet1 at cell L1 there's a table that can be right-clicked and Refreshed (Power Query/Get & Transform Data)
2. On Sheet1 (2) at cells H2:J2, 3 (very similar) formulas which can be copied down.
3. Also on Sheet1 (2) at cell L1 is a standard pivot table.  Reply With Quote

3. Wow thank you very much for the help, much appreciated, i will play around with both, the list i pull is over 12 000 line items and items are added and removed on a regular basis, trying to make it as bullet proof as possible. This will help a lot thanks again.  Reply With Quote

4. Here is the formula solution.
In F2 and copied down

Code:
`=IFERROR(INDEX(\$A:\$A,AGGREGATE(15,6, ROW(\$A\$2:\$A\$15)/((\$A\$2:\$A\$15<>"")*ISERROR(MATCH(\$A\$2:\$A\$15,\$F\$1:\$F1,0))),1)),"")`
In G2 and copied down

Code:
`=IF(\$F2="","",INDEX(\$B\$2:\$B\$15, MATCH(\$F2,\$A\$2:\$A\$15,0)))`
In H2 and copied down and across

Code:
`=IF(\$F2="","",INDEX(\$D:\$D,AGGREGATE(15,6,ROW(\$D\$2:\$D\$15)/((\$D\$2:\$D\$15<>"")*(\$A\$2:\$A\$15=\$F2)*(\$B\$2:\$B\$15=\$G2)*(LEFT(\$C\$2:\$C\$15,1)=LEFT(H\$1,1))),1)))`  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•