Unique values from dynamic list

[ddice]

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))),"")

[p45cal]

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.

[Tom Jones]

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.

[ddice]

I believe using a pivot table will be the easiest way to do this. Thanks for the help