Extract unique list with multiple criteria without dynamic array formulae

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
329
Office Version
  1. 365
  2. 2010
Hello

I'm trying to create a list of unique values from a field in a large Table based upon multiple criteria that works in Excel 2010 (i.e. without dynamic array functions).

The following works is how I can do it Excel 365, but I'm struggling to get the same results without using the FILTER and UNIQUE functions:

Excel Formula:
=SORT(UNIQUE(FILTER(tbl_Events[Referral Reference],
(tbl_Events[Event]="Communication")*
(tbl_Events[Days to Diagnosis communication from recent referral]>=0)*
(tbl_Events[Days to Diagnosis communication from recent referral]<=Target_Ref_Diag_Comm_days)*
(tbl_Events[Clinic]=Report_Clinic),"No results"),FALSE))

In the above Target_Ref_Diag_Comm_days refers to a single cell named range that is a whole number

I've been looking at posts using INDEX SMALL and ROWS, but can't get my head round how to apply them.

Very grateful for some help!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,572
Office Version
  1. 365
Platform
  1. Windows
Someone else might have a different idea, but I wouldn't even consider attempting this without a couple of helper columns.

Helper formula 1, extract unique records based on criteria, enter into A2 then fill down.
Excel Formula:
=IFERROR(INDEX(tbl_Events[Referral Reference],
AGGREGATE(15,6,ROW(tbl_Events[Event])/
(tbl_Events[Event]="Communication")/
(tbl_Events[Days to Diagnosis communication from recent referral]>=0)/
(tbl_Events[Days to Diagnosis communication from recent referral]<=Target_Ref_Diag_Comm_days)/
(tbl_Events[Clinic]=Report_Clinic)/
ISERROR(MATCH(tbl_Events[Referral Reference],A$1:A1,0)),1)-ROW(tbl_Events[[#Headers],[Event]])),"")
Helper formula 2, used to rank the unique results in column A. In B2 then fill down
Excel Formula:
=IF(A2="","",COUNTIFS(A:A,"?*",A:A,"<>"&A1,A:A,"<="&A2))
Final formula to return the sorted results. In C2 then fill down.
Excel Formula:
=IF(B2="","",INDEX(A:A,MATCH(ROWS(C$2:C2),B:B,0)))

Hopefully I have the formulas right, without the tables to test the first one on, there may be missing / misplaced parenthesis, or other similar errors.
 

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
329
Office Version
  1. 365
  2. 2010
Thanks Jason,

In my dataset Helper formula 1 returns the first of the expected results, but when filled down, repeats that result, instead of the others that match the criteria.

This works to count the number of expected results (they are strings of text numbers):

Excel Formula:
=IFERROR(SUM(--(FREQUENCY(IF(
(tbl_Events[Event]="Communication")*
(tbl_Events[Days to Diagnosis communication from recent referral]>=0)*
(tbl_Events[Days to Diagnosis communication from recent referral]<=Target_Ref_Diag_Comm_days)*
(tbl_Events[Clinic]=Report_Clinic),
MATCH(tbl_Events[Referral Reference],tbl_Events[Referral Reference],0)),
ROW(tbl_Events[Referral Reference])-ROW(tbl_Events[[#Headers],[Referral Reference]]))>0)),"Not Found")

...and shows me that I am expecting a list of 10 items, with data I currently have (but unfortunately I can't upload the data because it contains confidential information).


If I understand correctly the plan with Helper formula 1 is that
- the INDEX and AGGREGATE are bringing the smallest of the row numbers that matches the criteria in the next 4 lines
- the MATCH will return the value that corresponds to that value
- the last part ensures the table header isn't counted in the row number passed to the match?
Excel Formula:
-ROW(tbl_Events[[#Headers],[Event]]))

Is that right?

But I don't think I understand this part:
Excel Formula:
ISERROR(MATCH(tbl_Events[Referral Reference],A$1:A1,0)),1)

Why iserror and why ,A$1:A1?
 

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
329
Office Version
  1. 365
  2. 2010
To get first part (i.e. list that matches the criteria, I tried this, dragged down the following 100 rows:

Excel Formula:
=IFERROR(INDEX(tbl_Events[Referral Reference],SMALL(IF(
(tbl_Events[Event]="Communication")*
(tbl_Events[Days to Diagnosis communication from recent referral]>=0)*
(tbl_Events[Days to Diagnosis communication from recent referral]<=Target_Ref_Diag_Comm_days)*
(tbl_Events[Clinic]=Report_Clinic),
ROW(tbl_Events[Referral Reference])),ROW(Events!1:1))-5,1),"")

It seems to work, but I wish I knew: how to use a structured reference for the last part or a more elegant way or to reference the ROW in the last part than "ROW(Events!1:1))-5". (The Table, 'tblEvents' starts on the 6th row of the sheet 'Events').

Then to get unique items, I tried:
Excel Formula:
=IFERROR(INDEX(list_Matching_Refs,MATCH(0,COUNTIF($L$127:L127,list_Matching_Refs),0)),"")
, where:
  • $L$127 is the cell above the list of unique items
  • list_Matching_Refs is the named of the range =OFFSET($K$128,0,0,H45)),1). The value of H45 is 10 which is calculated in the formulae in the previous post
Dragging that down 10 or more rows did extract the list of unique items.

And then I need to sort them.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,572
Office Version
  1. 365
Platform
  1. Windows
Why iserror and why ,A$1:A1?
The idea of that is to compare the previous results (first row looks at the header). Anything that is not a match (not found in previous rows) will return an error. ISERROR then flags errors as true, making those rows valid results.

When you drag down the range expands to A$1:A2 (with A2 being the first formula).
 

Forum statistics

Threads
1,148,332
Messages
5,746,139
Members
423,994
Latest member
blzxatly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top