Extract unique list with multiple criteria without dynamic array formulae

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
435
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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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
Back
Top