Consulting

Results 1 to 4 of 4

Thread: Unique values from dynamic list

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Newbie
    Joined
    Jul 2019
    Posts
    5
    Location

    Question Unique values from dynamic list

    Hi all,

    I am having a hard time finding a way to pull the unique results of an array based on certain search criteria. That's confusing so let me associate it with a picture (see attachment)

    I want to only find the unique lots associated with the product ID. Below is where I am at with my formula. It is pulling all of the lots associated with that product ID, not just the unique values.

    =IFERROR(INDEX(Table1[[Lot_Code]:[Lot_Code]],AGGREGATE(15,3,(Table1[[Product_ID]:[Product_ID]]=$F9)/(Table1[[Product_ID]:[Product_ID]]=$F9)*ROW(Table1[[Product_ID]:[Product_ID]])-ROW(Table1[[#Headers],[Product_ID]]),COLUMNS($G$7:G8))),"")
    Attached Images Attached Images

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    I can't think of a one-formula approach that wouldn't be a difficult-to-maintain mega-formula!
    So in the attached there are 3 offerings:
    1. By the far the easiest way to get the data you want (though not in the same arrangement) is a very simple pivot table. That's on Sheet1 at cell A14. It'll get you the data in seconds, robustly.
    2. Using a helper column (Column E on Sheet1) and an array-entered formula in cell G9, copied across and down.
    3. Using your formula, but acting on a new table (on Sheet1 (2) at cell D14) created by Advanced Filter for Unique rows. Your formula is normally entered in G9 and copied across and down.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Newbie
    Joined
    Jul 2019
    Posts
    5
    Location
    I believe using a pivot table will be the easiest way to do this. Thanks for the help

  4. #4
    Hi,

    If Product ID is in F7 and if data are in F8, F9, F10... in G8 put this array formula (Ctrl + Shift + Enter)

    =IFERROR(INDEX($C$2:$C$30,SMALL(IF(FREQUENCY(IF($B$2:$B$30=$F8,MATCH($C$2:$ C$30,$C$2:$C$30,0)),ROW($C$2:$CB$30)-ROW($C$2)+1),ROW($C$2:$C$30)-ROW($C$2)+1),COLUMNS($G$8:G8))),"")

    Then "drag" formula down and right or vice versa.

    PS Obviously changes the data range according to the data in the file.

Posting Permissions

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