Unique Values Only

  • Thread starter Thread starter Cecil
  • Start date Start date
C

Cecil

Given a long column of values, how do I extract only unique values?
Small example > Values of 1, 2, 2, 3, 4, 4, 4, 5 in column A. Woul
like to extract unique values ... 1, 2, 3, 4, 5 ... and put them i
another column. Thanks
 
select the range in question
go to ....data....filter....advanced filter....copy to another location
select a cell in a different column
tick unique records only............. no need for a criteria range

Greetings from New Zealand
Bill K
 
Hi,

Here is a workaround to solving the problems through a formula

Assuming your data is in A3:A11 as follows:

1
2
3
1
4
2
3
14
15

In cell B3, enter the following formula and copy downwards (You may hide
this column because this is only a helper column)

=IF(AND(COUNTIF($A$3:$A$10,A3)>1,COUNTIF($A$2:A2,A3)>0),"","True")

In cell C3, enter the following array formula (Ctrl+Shift+Enter) and copy
downwards

=IF(ISERROR(INDEX($A$1:$B$15,SMALL(IF($B$3:$B$15="True",ROW($A$3:$A$15)),ROW(1:1)),1)),"",INDEX($A$1:$B$15,SMALL(IF($B$3:$B$15="True",ROW($A$3:$A$15)),ROW(1:1)),1))


Please note that i still have to modify the formula a bit but for the mean
time please keep the starting row for the data (in column B) as row 3.

Hope this helps.

If you have any clarifications, please feel free to contact me at
(e-mail address removed)

Regards,
 
Or with one formula, put this in B3 entered with ctrl + shift & enter and
copy down until
there is an error

=INDEX($A$3:$A$11,MATCH(0,COUNTIF($B$2:B2,$A$3:$A$11),0))

of course it can have additions to guard against errors as well

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon
 
Hi

Is there a way to handle empty cells within the A3:A11 range? Using th
formula exactly as in the example returns "0" for the first empty cell
I haven't really understood how excel handles empty cells.
I did some trial and error and found that putting 0 in B2 solved th
problem but it fells like a workaround.

BR
Anders
 
Try...

=INDEX(A3:$A$11,MATCH(0,COUNTIF($B$2:B2,A3:$A$11&""),0))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
Peo

In the solution you provided, is it necessary that there must be a
blank row between the first data row and the header row?


Best regards



Deepak Agarwal
 
Peo

Then tell me this :-

Is it then a requirement for it to work that the data (if there are no
header rows) cannot start from row1 - it must start from row2 or from
any row beyond row2?


Best regards


Deepak Agarwal
 
I apologize, I thought this thread was about something else, I believe I
posted this originally in February. So to extraxt the unique values with a
formula this part

COUNTIF($B$2:B2

always need to start one row above the cell it extracts from so

COUNTIF($B$1:B1

cannot be used as

=INDEX($A$1:$A$11,MATCH(0,COUNTIF($B$1:B1,$A$1:$A$11),0))

So this is as far "top" you can go

=INDEX($A$2:$A$11,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$11),0))

and the formula needs to be in B2



--

Regards,

Peo Sjoblom

Nothwest Excel Solutions

www.nwexcelsolutions.com

remove ^^ from email
 
Peo

That was exactly what I was trying to get to. I do understand that now
No problems there.

One other thing and this is for my education only.

With reference to the above problem only, with the full formula arra
entered, when the "range" argument of the countif() function happens t
be say "B2:B5" with the "criteria" argument is say "A3:A11", th
countif() generates 3 sets of 9 element arrays one for each of the
values contained in the range "B2:B5", why are the correspondin
elements of these 3 intermediate arrays from the countif() functio
"added" (and not mutiplied) to generate the final array which i
eventually passed on to the match() function?

Again, this is only for my academic understanding only - the intent i
not to question the correctness of your solution.


Best regards


Deepak Agarwa
 
Peo

Can you please help here?

I tried to use the above to extract unique values from a list. This
list at the moment is around 700 rows but can expand to 65000 rows. It
didn't seem to work for me.

I am attaching herewith the spreadsheet with some data there. Can you
please advise me as to where am I going wrong?


Best regards


Deepak Agarwal


+-------------------------------------------------------------------+
|Filename: For Forum.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4655 |
+-------------------------------------------------------------------+
 
I am not using excel forum so there are no file attachments, if indeed your
values can go as far as
650000 rows then it is probably not a good idea to use an array formula. You
would feel like an animal trapped in a tar pit. I am sure advanced filter
would be faster


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
 
Back
Top