I have a list of numbers in cells B3:B10.
There is only one unique value in the list and the rest are duplicates.
I need a formula in cell B1 that would tell me what that one unique value is?
Thanks.
I have a list of numbers in cells B3:B10.
There is only one unique value in the list and the rest are duplicates.
I need a formula in cell B1 that would tell me what that one unique value is?
Thanks.
Maybe this
=INDEX(B3:B10,SMALL(IF(B3:B10<>MODE(B3:B10),ROW(B3:B10)-ROW(B3)+1),1))
or this
=MIN(IF(B3:B10<>MODE(B3:B10),B3:B10))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
B C D 3 5 3 4 5 5 5 6 5 7 5 8 3 9 5 10 5
Last edited by AlKey; 08-28-2014 at 02:18 PM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
iN C3
=INDEX($B$3:$B$10,MATCH(0,COUNTIF($C$2:C2,$B$3:B10),0))
Give ctrl+shift+enter
☚ Click ★ just below left if it helps, Boo?ath?
maybe
=INDEX(B3:B10,MATCH(1,FREQUENCY(B3:B10,B3:B10),0))
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks