Extract Unique List Based on multiple criteria

**Herbiec09** · 01-13-2017, 05:12 AM

Good morning All.

I have the following formula (courtesy of Siva), that extracts a list of values from a database based on multiple values:

=IFERROR(INDEX(Database!M:M,SMALL(INDEX(((TRIM(Database!$A$2:$A$69)<>$A$2)+(TRIM(Database!$H$2:$H$69)="Capex"))*10^10+ROW(Database!$H$2:$H$69),0),ROWS(A$6:A6))),"")

It works brilliantly.

I need some help taking it one step further. How can I tweak this so that it produces a UNIQUE list; say maybe incorporating the frequency formula?

Thank you

H

**Richard Buttrey** · 01-13-2017, 05:30 AM

It depends how you'e defining Unique.

For this sort of stuff I always use an Advanced Data Filter on the original data and specify the Unique parameter.

Altenatively add a helper column that uses a formula to determine whether a particular row is Unique or not and filter on the helper column.

**Herbiec09** · 01-13-2017, 05:41 AM

Thanks for the reply Richard,

However, I am using the above formula to create automated statements by looking into the database for certain transactions. I would prefer not to move away from this formula, because my statements are generated with one click (selection from a dropdown).

Therefore, the best solution for me would be an adjustment to the above formula so that if it sees more than one value in the database, it only returns the one in the list.

Thanks

**Herbiec09** · 01-13-2017, 05:42 AM

Thanks for the reply Richard,

However, I am using the above formula to create automated statements by looking into the database for certain transactions. I would prefer not to move away from this formula, because my statements are generated with one click (selection from a dropdown).

Therefore, the best solution for me would be an adjustment to the above formula so that if it sees more than one value in the database, it only returns the one in the list.

Thanks

**Richard Buttrey** · 01-13-2017, 01:21 PM

Would you upload the workbook and give me an example or two of what you expect to see and where for a situation where which needs to deal with multiple frequencies. Please also explain in a note how you have arrived at the result you expect the amended formula to give.

**Herbiec09** · 01-14-2017, 03:45 AM

Hi Richard,

Please see spreadsheet attached with formula.

The following values appear more than once as an example:

Dave 42607Dave Aug-CashConsumables &

Dave 42577Dave Jul-CashBank Charges

I would like the list to have only one instance of these values regardless of how many time they appear in the original raw data.

Many thanks for your help

Regards

Herbz