Unique list with multiple criteria

Hello,

Hoping for some help with obtaining a unique list with a formula.

Effectively, I would like to reference one large data sheet and output a unique list on another sheet based upon the following criteria:

- IF (Data!$B:$B) matches $I$2 in Sheet1
- IF (Data!$H:$H) matches $A$3 in Sheet1
- Then produce unique list from (Data!$AC) in B5 down on Sheet1

Hope I've outlined that clear enough and hope this is possible. Much appreciate the support!

Something like

Formula:

=IF(B5="","",IFERROR(INDEX(Data!$AC:$AC,AGGREGATE(15,6,ROW(Data!$AC$2:$AC$100)/(Data!$B$2:$B$100=$I$2)/(Data!$H$2:$H$100=$A$3),ROWS(B$5:B5))))

I've set the Data sheet ranges for rows 2:100, this can be adjusted as needed to a realistic range, with the exception of the INDEX range (which must be a full column) this type of formula is not suitable for use with entire columns, unless you want to wait a few days every time it recalculates

Thanks Jason and well noted on the recalculation front, it's quite a large data set (10k rows) so is my best bet then a macro for what I'm looking to achieve.

On your formula, it's given a too many arguments error.

Appreciate your support.

If you could I would appreciate it

My bad, I typed the formula here instead of excel, I think you would have seen a 'too few' error rather than 'too many', I missed the ,"" in iferror.

Formula:

=IF(B5="","",IFERROR(INDEX(Data!$AC:$AC,AGGREGATE(15,6,ROW(Data!$AC$2:$AC$100)/(Data!$B$2:$B$100=$I$2)/(Data!$H$2:$H$100=$A$3),ROWS(B$5:B5))),""))

vba should only be a last resort if the formula proves not practical.

10k rows might not be too bad, the efficiency will depend on the number of unique entries as well.

To maximise efficiency, dynamic named ranges could be useful, or format the data as a structured table.

Thanks Jason, however, am now getting a Circular Reference Warning and can't quite see where this is occurring.

Third time lucky

Formula:

=IF(B4="","",IFERROR(INDEX(Data!$AC:$AC,AGGREGATE(15,6,ROW(Data!$AC$2:$AC$100)/(Data!$B$2:$B$100=$I$2)/(Data!$H$2:$H$100=$A$3),ROWS(B$5:B5))),""))

The first B5 should have been B4 (it needs to look at the cell above).

Note that I've assumed B4 conatians header text of some kind. If B4 is part of a merged cell then it could cause additional errors.

While a sample file is not strictly necessary, it is (with an accurate representation) an advantage as it would enable us to get it right first time (or closer with a more complex requirement).

Originally Posted by jason.b75

Third time lucky

Formula:

=IF(B4="","",IFERROR(INDEX(Data!$AC:$AC,AGGREGATE(15,6,ROW(Data!$AC$2:$AC$100)/(Data!$B$2:$B$100=$I$2)/(Data!$H$2:$H$100=$A$3),ROWS(B$5:B5))),""))

The first B5 should have been B4 (it needs to look at the cell above).

Note that I've assumed B4 conatians header text of some kind. If B4 is part of a merged cell then it could cause additional errors.

While a sample file is not strictly necessary, it is (with an accurate representation) an advantage as it would enable us to get it right first time (or closer with a more complex requirement).

Amazing, thanks Jason it works as requested. Really appreciate the support