Unique values in whole sheet

I

illur81

Hi all,
My requirement is to find unique values in the whole excel sheet,i.e
across all columns. I am aware of finding unique values in one column,
but not in the whole sheet. My data would be like this
col 1 col2 col 3
sam andy sam
andy mark kathy
tim kim liz
kim liz mark

The output should be only sam,andy,mark,kathy,tim,kim,liz. I tried so
many ways and searching in net. I am terribly sorry if this kind of
question is already answered here. CAn someone help me in this regard.

Thanks
Sandeep
 
R

Ron Rosenfeld

Hi all,
My requirement is to find unique values in the whole excel sheet,i.e
across all columns. I am aware of finding unique values in one column,
but not in the whole sheet. My data would be like this
col 1 col2 col 3
sam andy sam
andy mark kathy
tim kim liz
kim liz mark

The output should be only sam,andy,mark,kathy,tim,kim,liz. I tried so
many ways and searching in net. I am terribly sorry if this kind of
question is already answered here. CAn someone help me in this regard.

Thanks
Sandeep
Click to expand...

=UNIQUEVALUES(rng) will accept a multi-column, mult-row array and return the
results in a single column.

This function is part of Longre's free morefunc.xll add-in, available at
http://xcell05.free.fr/


--ron
 
I

illur81

Hi Ron,
thx a lot for the reply. I am not able to get the desired output which
i require through his add in.When I give, " =UNIQUEVALUES(A1:B100)", it
jsut gives me one unique value.I am not sure why I am getting such an
output. Let me be clearer, as per my example I am getting only "Andy".
No other value is getting displayed. I even tried through Insert -->
Function --> More function. When i give my range, i still get the same
output. Can you please help me, your help is very required here ron,as
i have to complte this assignment as soon as possible. Fingers
crossed.
Thanks in Advance
Sandeep

PS : Ron, If I ahve missed out something very basic, I am extremely
sorry. Please help me out.
 
P

Peo Sjoblom

Did you select a range when applying the function and did you enter it with
ctrl + shift & enter
?

--
Regards,

Peo Sjoblom

(No private emails please)
 
R

Ron Rosenfeld

Hi Ron,
thx a lot for the reply. I am not able to get the desired output which
i require through his add in.When I give, " =UNIQUEVALUES(A1:B100)", it
jsut gives me one unique value.I am not sure why I am getting such an
output. Let me be clearer, as per my example I am getting only "Andy".
No other value is getting displayed. I even tried through Insert -->
Function --> More function. When i give my range, i still get the same
output. Can you please help me, your help is very required here ron,as
i have to complte this assignment as soon as possible. Fingers
crossed.
Thanks in Advance
Sandeep

PS : Ron, If I ahve missed out something very basic, I am extremely
sorry. Please help me out.
Click to expand...

Sure.

The UNIQUEVALUES function returns an array, so it needs to be entered as an
array.

For example

1. Select C1:C100 (with C1 being the active cell).

2. In the formula bar, type in =UNIQUEVALUES(A1:B100) **THEN**

3. Hold down <ctrl><shift> while hitting <enter>.

The formula will be entered as an array and will return an ordered list of
unique values.

However, entering it this way makes it difficult to edit, as you cannot edit
just one formula; rather you would have to select the entire array.

Another method of extracting the individual array elements would be to enter
(normally--just with <enter>) the following formula in C1:

=INDEX(UNIQUEVALUES($A$1:$B$100),ROW())

The ROW() argument, in row 1, will return a number '1'; in row 2 it will return
a '2', etc.

Of you wanted to start this in row 5, you could change the first entry to:

=INDEX(UNIQUEVALUES($A$1:$B$100),ROW()-4)

Then copy/drag that formula down as far as there are unique entries.

You can combine tests for errors and blanks with this formula so as not to get
them displayed, if you need to.


--ron
 
Top