Consulting

Results 1 to 4 of 4

Thread: Unique values from dynamic list

  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 Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,901
    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 - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    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
    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.

  4. #4
    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

Posting Permissions

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