Unique distinct records sorted by frequency
How can you use large with multiple criteria?? Example looking for top 5 of a list based on name, state and city..
Answer:
Array formula in cell E3:
=INDEX($A$2:$C$29, MATCH(LARGE(IF(COUNTIFS($E$2:E2, $A$2:$A$29, $F$2:F2, $B$2:$B$29, $G$2:G2, $C$2:$C$29)=0, COUNTIFS($A$2:$A$29, $A$2:$A$29, $B$2:$B$29, $B$2:$B$29, $C$2:$C$29, $C$2:$C$29), ""), 1), COUNTIFS($A$2:$A$29, $A$2:$A$29, $B$2:$B$29, $B$2:$B$29, $C$2:$C$29, $C$2:$C$29)*(COUNTIFS($E$2:E2, $A$2:$A$29, $F$2:F2, $B$2:$B$29, $G$2:G2, $C$2:$C$29)=0), 0), COLUMN(A1))
Formula in cell H3:
=COUNTIFS($A$2:$A$29, E3, $B$2:$B$29, F3, $C$2:$C$29, G3)
How to create an array formula
- Select cell E3
- Paste array formula
- Press and hold Ctrl + Shift
- Press Enter
How to copy array formula
- Select cell E3
- Copy cell (not formula)
- Select cell range F3:G3
- Paste
- Select cell range E3:G3
- Copy cells (not formulas)
- Select cell range E4:G9
- Paste
Explaining array formula in cell E3
See post: Create a unique distinct list and sort by occurrances from large to small
Download example *.xlsx file
Unique distinct records sorted by frequency.xlsx
Related posts:
Filter unique distinct records with a condition in excel 2007
Count unique distinct records with a date and column criteria in excel 2007
Count unique distinct records in excel 2007
Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
Recommended categories
Thi site helped me crate a list of unique users from a data extract however I want to go one more step and next o the unique user add in the location they are at. Location is column A, user is column B and unique user is in column I, in column J I just want to have their location - can you direct or guide me to the tip to clip? Thank you!!
William J. Ryan,
Formula in J2: