Unique list with multiple criteria

[donjamin]

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!

[jason.b75]

Something like

Please Login or Register  to view this content.

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

[donjamin]

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.

[AliGW]

Shall I move this to the VBA section for you?

[donjamin]

If you could I would appreciate it

[jason.b75]

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.

Please Login or Register  to view this content.

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.

[AliGW]

Try the formula first. Let us know how it goes.

[donjamin]

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

[jason.b75]

Third time lucky

Please Login or Register  to view this content.

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).

[donjamin]

Third time lucky

Please Login or Register  to view this content.

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