LinkBack URL
About LinkBacks
Looking for non-array formula to pull values unique values in a range that has duplicate values and then sort. Do not want to use an array formula as the range has over 4,000 values. Values in A2:A8000.
Thanks
Looking for non-array formula to pull values unique values in a range that has duplicate values and then sort. Do not want to use an array formula as the range has over 4,000 values. Values in A2:A8000.
Thanks
Are the values text or numeric?
One way to pull unique values without an array formula is to use a helper column (e.g. column H), with a formula like this in H2:
=IF(COUNTIF(A$2:A2,A2)=1,MAX(H$1:H1)+1,"-")
That will give sequential numbers down column H for the first occurrence of a value in column A, so you can pick out those unique values using a formula like this:
=IFERROR(INDEX(A:A,MATCH(ROWS($1:1),H:H,0)),"")
Copy this down until you start to get blanks.
Hope this helps.
Pete
Pete_UK: formula works like a charm. Need a non-array formula to sort the values in ascending order.
Is there a way this formula =IFERROR(INDEX(A:A,MATCH(ROWS($1:1),H:H,0)),"") could be modified to start from =IFERROR(INDEX(A2:A8000,MATCH(ROWS($1:1),H2:H8000,0)),"")?
Thanks
Last edited by bjnockle; 10-23-2016 at 01:34 PM.
Yes, but you will need to anchor the row references using the $ symbol, like this:Originally Posted by bjnockle
=IFERROR(INDEX(A$2:A$8000,MATCH(ROWS($1:1),H$2:H$8000,0)),"")
As for sorting, you didn't answer the question about whether the data values are text or numeric.
Hope this helps.
Pete
Pete_UK: As for sorting, the data values are mixture of text and numbers. For example. Apple12, UK55.
Thanks
The usual way of sorting by formulae is to use the RANK function or the SMALL (or LARGE) function. These all operate on numeric values, so I'm not sure how you would do this with text values.
Pete
I am not sure if there is a non-array formula that would extract unique values and sort. However, an array formula can do that
Give this one a try
Assuming your data range is A2:A4000
Your first array formula to count unique values in B$1
Formula:
Please Login or Register to view this content.
then next array formula that will get the unique records and sort: Enter in B2 and copy down
Formula:Please Login or Register to view this content.
***Array formula
...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.
Last edited by AlKey; 10-23-2016 at 05:33 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
I did this with all text, and blended the above suggestions in 3 helper columns.
Random U.S. states are in column A with duplicates.
Unique data in column B withFormula:Please Login or Register to view this content.
Alpha ranked in column C withFormula:Please Login or Register to view this content.
Rank those ranks in column D withFormula:Please Login or Register to view this content.
And the final sorted formula in column EFormula:Please Login or Register to view this content.
It's more steps than an array.
Dave
Dave,
that's great. You can avoid the errors (purely for aesthetic purposes) by using this in C1:
=IF(B1<>"",COUNTIF($B$1:$B$25,"<="&$B$1:$B$25),"")
and this in D1:
=IF(C1="","",RANK(C1,$C$1:$C$25,1))
Hope this helps.
Pete
Yes, Pete. Good idea! Thanks for that. I was so surprised that it worked I missed it. LOL
Thank You So much
WOW!! Is that really your first post after joining in November 2003 ?
Pete
Hi all,
I need this formula without VBA & Helper columns & Array formula (Ctrl + shift + Enter)
=IF(ROWS(B$2:B2)>B$1,"",INDEX($A$2:$A$5000,MATCH(0,COUNTIF($A$2:$A$5000,"<"&$A$2:$A$5000)-SUM(COUNTIF($A$2:$A$5000,$B$1:B1)),0)))
Thank you so much for the help me
Notice:
my main language is not English
Yaghoub61. By now you should know the forum rules. Thread hijacking is NOT allowed.
Rule 4: Do not post a new help request in an existing thread.
Start your OWN thread and post a small sample sheet.
Thread closed.
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks