Results 1 to 4 of 4

Thread: Help With A function To Pull Quantity

  1. #1
    Neophyte docharding79's Avatar
    Join Date
    Mar 2021
    Posts
    2
    Articles
    0
    Excel Version
    Office 365

    Help With A function To Pull Quantity



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

    Thank you in advance
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,948
    Articles
    0
    Excel Version
    365
    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.
    Attached Files Attached Files
    Last edited by p45cal; 2021-03-30 at 03:01 PM.

  3. #3
    Neophyte docharding79's Avatar
    Join Date
    Mar 2021
    Posts
    2
    Articles
    0
    Excel Version
    Office 365
    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.

  4. #4
    Seeker kvsrinivasamurthy's Avatar
    Join Date
    Jun 2014
    Posts
    8
    Articles
    0
    Excel Version
    2019
    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)))
    Attached Files Attached Files

Posting Permissions

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