LinkBack URL
About LinkBacks
I would like a formula which finds the unique number(which appears one time only) and extract him at C1. thanks so much guys!
I would like a formula which finds the unique number(which appears one time only) and extract him at C1. thanks so much guys!
Loius - as you have been told before, you need to tell us what your expected result is in C1. What is it based on this sample data? What happens if there is more than one unique number?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
imagine a random generator which extracts numbers,so at some time in the box there is only one unique number and i want it at C1
can you help me?
And what is the expected result in your sample file? Is it 23? Please answer the questions asked of you - it will save SO much time.
sorry i make a mistake , please see attached a new table,my expected result in that case is 9 but generally the expected result is the unique number in a box which exists only ones
It can be done in Power Query using this M code:
Please Login or Register to view this content.
is there any more simple formula,please?
It can't be done with a simple formula as far as I can see. Someone else may be able to help.
dear Ali, i know that you are very proffesional and thanks you! but that is very complicated for me
It isn't difficult and I can talk you through it IF you are interested to learn. Are you?
iam not sure
By the way, which version of Excel are you using? I have just noticed that your profile says 2007 - is that correct?
i think a formula with vlook and count it would work,something like that maybe
If you are CERTAIN that there is only one unique number:
this array formula will do that:
=IFERROR(SMALL(IF(COUNTIF($E$1:$N$11,$E$1:$N$11)=1,$E$1:$N$11),1),"")
If you are not CERTAIN, this array formula, copied down will list all unique numbers:
=IFERROR(SMALL(IF(COUNTIF($E$1:$N$11,$E$1:$N$11)=1,$E$1:$N$11),ROWS(C$1:C1)),"")
Last edited by Glenn Kennedy; 01-20-2019 at 06:04 AM. Reason: Got the formulas the wrong way round!!
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
Ooops.
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...
If you are using a later version of excel, there is a non-array version, too.
No, not as simple as that. OK, I take it that means you are not going to tell us if you are using Excel 2007 or not and that you are not interested in learning Power Query. Over to Glenn!Originally Posted by louis128
“A silent mouth is sweet to hear.”my regards! my friend
And what is that meant to mean, Louis? I hope you are not being critical of my questions and attempts to help you. Which version of Excel are you using? Please answer my question. Is it still Excel 2007?
2003 and 2007
thanks ali
Last edited by louis128; 01-20-2019 at 06:30 AM.
Luis, take care that you do not "overstep the mark" and get barred from the site. Advice: stop.
Thanks for the confirmation of Excel versions. This means that my PQ suggestion is moot, as you don't have it. Sorry about that - my mistake.
thanks! alot!
Another option that will find the first unique number in the array.
=MATCH(1,FREQUENCY(E1:N11,ROW(A1:INDEX(A:A,MAX(E1:N11)))))
Array confirmed with shift ctrl enter.
Note that this will only work with positive integers, where Glenn's suggestion will work with negatives and decimals too.
By the way, Glenn - what was the non-array alternative? Just out of curiosity.
=IFERROR(AGGREGATE(15,6,$E$1:$N$11/(COUNTIF($E$1:$N$11,$E$1:$N$11)=1),ROWS(C$1:C1)),"")
or
=IFERROR(AGGREGATE(15,6,$E$1:$N$11/(COUNTIF($E$1:$N$11,$E$1:$N$11)=1),1),"")
if CERTAIN that there's only 1 unique number.
Thanks - I am still trying to get my head around the AGGREGATE function. Lightbulb moments are longer in the making these days ...
Non-array, or Non-CSE array?
Surely if the functions used create arrays within the formula then it is an array formula regardless of whether or not it needs to be confirmed as such.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks