unique alphabetical order

[Berna11]

good evening I need your help to fix a formula that doesn't give me the correct result
I would like to sort column A uniquely provided that column B is YES
I skip a result cell D4
Thanks as always for your help,

[mehmetcik]

Select your Range A2:C7 Select my data has no headers.

Select sort by column B Z-A

Select Add a Level

Select sort by column A A-Z

Select Ok.



I cannot post my code.

The Macro Test does this for you.

[Bo_Ry]

Please try at D2

=IFERROR(INDEX($A$2:$A$9,MATCH(0,INDEX(COUNTIFS($A$2:$A$9,"<"&$A$2:$A$9,$B$2:$B$9,$D$1)/($B$2:$B$9=$D$1)-SUMPRODUCT(COUNTIFS($A$2:$A$9,D$1:D1,$B$2:$B$9,$D$1)),),)),"")

[Berna11]

Thanks, I've seen with sorting it works.
But, to avoid doing the sorting, and use the formula of Bp
I would have to add another condition in E1 = 2020
i tried but i don't get the correct result
F2:

=IFERROR(INDEX($A$2:$A$9,MATCH(0,COUNTIFS($A$2:$A$9,"<"&$A$2:$A$9,$B$2:$B$9,$D$1,$C$2:$C$9,$E$1)/(($B$2:$B$9=$D$1)/($C$2:$C$9,$E$1))-SUMPRODUCT(COUNTIFS($A$2:$A$9,F$1:F1,$B$2:$B$9,$D$1,$C$2:$C$9,$E$1)),)),"")

[Glenn Kennedy]

Is this array formula what you wanted:

=IFERROR(INDEX($A$2:$A$9,MATCH(0,COUNTIFS($A$2:$A$9,"<"&$A$2:$A$9,$B$2:$B$9,$D$1,$C$2:$C$9,2020)/(($B$2:$B$9=$D$1)*$C$2:$C$9=2020)-SUMPRODUCT(COUNTIFS($A$2:$A$9,D$1:D1,$B$2:$B$9,$D$1,$C$2:$C$9,2020)),)),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Berna11]

thank you all
I know how a matrix formula works
solved thanks

[Berna11]

Sorry, little problem
in the case of numbered data

puts the .7 after the .10
cell F3: F4
something can be done
Thank you

[Glenn Kennedy]

Can we see a VERY representative selection of the type of ID strings that you are working with

[Bo_Ry]

Please try at E2

Please Login or Register  to view this content.

[Glenn Kennedy]

Shouldn't it be in the order 1, 7, 10....??

[Berna11]

Thanks for your commitment and availability
in fact the order should be 1,7,10 ...
and not 10, 7, 1 if possible
otherwise you have to change the ID to 01,07,10
hanks

[Bo_Ry]

I sort by Alphabet before the dot then by numbers after the dot.

Change A6 Gami.1 to Fami.1 to see the result.

[Berna11]

I apologize for Gami.1
I am attaching a file with more data
in yellow those out of order in the 2 formulas

[Bo_Ry]

I intend to change A6 to Gami to demonstrate that the formula can sort Alphabet and Number.

But if just sort Number and ignore alphabet then

F2
=IFERROR(LEFT($A$2;5)&SMALL(IF(FREQUENCY(IF($B$2:$B$14=$D$1;IF($C$2:$C$14=$E$1;--MID($A$2:$A$14;6;9)));--MID($A$2:$A$14;6;9))>0;--MID($A$2:$A$14;6;9));ROWS(F$2:F2));"")

[Glenn Kennedy]

Do the ID's ALWAYS begin with Fani. ??? If not, please supply a VERY REPRESENTATIVE selection, as previously requested.

[Berna11]

After several tests I give up
I attach a more complete file
The initial part is Fami. over all
it doesn't work if I put the final range higher than 77
varying D1 / E1 leaves blank and duplicate lines.
Thanks sorry.

[Bo_Ry]

A15 is not Fami. that make formula can't capture correctly
Change to Fami.0 or add iferror

=IFERROR(LEFT($A$2;5)&SMALL(IF(FREQUENCY(IF($B$2:$B$77=$D$1;IF($C$2:$C$77=$E$1;--MID($A$2:$A$77;6;9)));IFERROR(--MID($A$2:$A$77;6;9);0));--MID($A$2:$A$77;6;9));ROWS(E$2:E2));"")

[Berna11]

Hello
now here we are, it works
sorry for the problems
Thank you