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 row records in excel 2007
Unique distinct list from a column sorted A to Z 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: