I would like to have an index match function provide a unique list of items in alphabetical order and at one criteria for now.
But I think what is messing up this is the criteria part that shows +IF($A$2:$A$52<>"AI",1,0). Without this, I get the entire list, sorted into alphabetical order.
Please Login or Register to view this content.
When looking at the attached spreadsheet, I'd like to filter out only if column A includes "AI" and then uniquely sort this in alphabetical order.
Pl see file . I have given example in Sheet2.
ARRAY formula in G2 then drag down.
Please Login or Register to view this content.
Last edited by kvsrinivasamurthy; 02-25-2019 at 02:06 AM.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Pl see file . I have given example in Sheet2.
ARRAY formula in G2 then drag down. This formula avoids duplicates if any.
Please Login or Register to view this content.
Please try at H2 with CSE and drag down
=IFERROR(INDEX($C$2:$C$52,MATCH(AGGREGATE(15,6,(IF($A$2:$A$52="AI",COUNTIF($C$2:$C$52,"<"&$C$2:$C$52)/NOT(COUNTIF(H$1:H1,$C$2:$C$52)))),1),COUNTIF($C$2:$C$52,"<"&$C$2:$C$52),)),"")
An alternative non-CSE solution:
=IF(ROWS(A$2:A2)>COUNTIF(A$2:$A$52,"AI"),"",LOOKUP(1,0/FREQUENCY(ROWS(A$2:A2),COUNTIFS(A$2:$A$52,"AI",C$2:C$52,"<="&C$2:C$52)),C$2:C$52))
Regards
Including the condition $A$2:$A$52="AI"
ARRAY in G2 then copied down.
Please Login or Register to view this content.
Ah, just realised that you might be dealing with cases in which there are duplicates in column C corresponding to "AI". If so, ignore my solution.
Regards
Thanks Bo_Ry and kvsrinivasamurthy! Both of those worked well =)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks