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. Would
like to extract unique values ... 1, 2, 3, 4, 5 ... and put them in
another column. Thanks.


--
Cecil
------------------------------------------------------------------------
Cecil's Profile: http://www.excelforum.com/member.php...o&userid=29035
View this thread: http://www.excelforum.com/showthread...hreadid=514051

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

"Cecil" wrote in
message ...

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. Would
like to extract unique values ... 1, 2, 3, 4, 5 ... and put them in
another column. Thanks.


--
Cecil
------------------------------------------------------------------------
Cecil's Profile:
http://www.excelforum.com/member.php...o&userid=29035
View this thread: http://www.excelforum.com/showthread...hreadid=514051

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,A 3)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


Regards,


"Cecil" wrote:


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. Would
like to extract unique values ... 1, 2, 3, 4, 5 ... and put them in
another column. Thanks.


--
Cecil
------------------------------------------------------------------------
Cecil's Profile: http://www.excelforum.com/member.php...o&userid=29035
View this thread: http://www.excelforum.com/showthread...hreadid=514051

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




"Ashish Mathur" wrote in message
...
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,A 3)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


Regards,


"Cecil" wrote:


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. Would
like to extract unique values ... 1, 2, 3, 4, 5 ... and put them in
another column. Thanks.


--
Cecil
------------------------------------------------------------------------
Cecil's Profile:
http://www.excelforum.com/member.php...o&userid=29035
View this thread:
http://www.excelforum.com/showthread...hreadid=514051

Bill, Ashish, Peo Thanks for your help. I'm "all set". Thank you,
Cecil. ;)


--
Cecil
------------------------------------------------------------------------
Cecil's Profile: http://www.excelforum.com/member.php...o&userid=29035
View this thread: http://www.excelforum.com/showthread...hreadid=514051

Hi

Is there a way to handle empty cells within the A3:A11 range? Using the
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 the
problem but it fells like a workaround.

BR
Anders.


--
akullen
------------------------------------------------------------------------
akullen's Profile: http://www.excelforum.com/member.php...o&userid=32513
View this thread: http://www.excelforum.com/showthread...hreadid=514051

Try...

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

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

Hope this helps!

In article ,
akullen wrote:

Hi

Is there a way to handle empty cells within the A3:A11 range? Using the
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 the
problem but it fells like a workaround.

BR
Anders.

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


--
agarwaldvk
------------------------------------------------------------------------
agarwaldvk's Profile: http://www.excelforum.com/member.php...o&userid=11345
View this thread: http://www.excelforum.com/showthread...hreadid=514051

No, it's better if there are no blank rows between the first data and the
header

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"agarwaldvk" wrote
in message ...

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


--
agarwaldvk
------------------------------------------------------------------------
agarwaldvk's Profile:
http://www.excelforum.com/member.php...o&userid=11345
View this thread: http://www.excelforum.com/showthread...hreadid=514051