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,
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,
Last edited by Berna11; 10-22-2020 at 07:16 AM.
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.
Last edited by mehmetcik; 10-20-2020 at 01:50 PM.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
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)),),)),"")
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)),)),"")
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...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
thank you all
I know how a matrix formula works
solved thanks
Sorry, little problem
in the case of numbered data
puts the .7 after the .10
cell F3: F4
something can be done
Thank you
Can we see a VERY representative selection of the type of ID strings that you are working with
Please try at E2
Formula:Please Login or Register to view this content.
Shouldn't it be in the order 1, 7, 10....??
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
I sort by Alphabet before the dot then by numbers after the dot.
Change A6 Gami.1 to Fami.1 to see the result.
I apologize for Gami.1
I am attaching a file with more data
in yellow those out of order in the 2 formulas
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));"")
Do the ID's ALWAYS begin with Fani. ??? If not, please supply a VERY REPRESENTATIVE selection, as previously requested.
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.
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));"")
Hello
now here we are, it works
sorry for the problems
Thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks