Unique Values Only
 

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

Unique Values Only
 

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

Unique Values Only
 

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

Unique Values Only
 

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

Unique Values Only
 

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

Unique Values Only
 

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

Unique Values Only
 

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.

Unique Values Only
 

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

Unique Values Only
 

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

Unique Values Only
 

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


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

Unique Values Only
 

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


"agarwaldvk" wrote
in message ...

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


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

Unique Values Only
 

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 array
entered, when the "range" argument of the countif() function happens to
be say "B2:B5" with the "criteria" argument is say "A3:A11", the
countif() generates 3 sets of 9 element arrays one for each of the 3
values contained in the range "B2:B5", why are the corresponding
elements of these 3 intermediate arrays from the countif() function
"added" (and not mutiplied) to generate the final array which is
eventually passed on to the match() function?

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


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

Unique Values Only
 

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 |
+-------------------------------------------------------------------+

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

Unique Values Only
 

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



"agarwaldvk" wrote
in message ...

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 |
+-------------------------------------------------------------------+

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