How do I get the unique values from a range?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to build a results sheet for some work that we do.

1 column has the month in which the work was done. ie.

01/01/04
01/01/04
01/02/04
01/02/04
01/04/04
01/09/04
01/09/04
01/09/04

etc... In the example above, as you can see, some months have multiple jobs
done, others only 1. Also there are some months when no work was done at all.
The dates are always the 1st of the month, so no problems in looking at
specific dates, just the month values.

So if I wished to show the last 4 month's when work was done, what function
would I need to use to get Excel to produce a list like this...

01/01/04
01/02/04
01/04/04
01/09/04

TIA.
 
One way using non-array formulas ..

Assuming source dates are in A1 down

Put in C1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",ROW()))

Put in D1:
=IF(ISERROR(SMALL(C:C,ROW(A1))),"",
INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))

Select C1:D1, fill down until the last row of source data
Format col D as dates in the desired format
Col D returns the required results
 
Thanks Max, works a treat!

Max said:
One way using non-array formulas ..

Assuming source dates are in A1 down

Put in C1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",ROW()))

Put in D1:
=IF(ISERROR(SMALL(C:C,ROW(A1))),"",
INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))

Select C1:D1, fill down until the last row of source data
Format col D as dates in the desired format
Col D returns the required results
Click to expand...
 
An alternative array solution

B1: = A1
B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""),0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$2
0&""),0)))

and copy down

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
You're welcome, Dave !

Try also Bob's array solution. It's much neater but more complex <g>, and it
assumes a "hard-coded" source data extent of $A$1:$A$20 (just adapt the
range to suit)
 
Nice array, Bob ! Noticed the existence of intermediate empty cell(s) within
the source range do seem to cause a problem to the neat results pull-out. Is
there a way to incorporate this possibility ?
--
Max, Singapore GMT+8, xl97
Samples archive at:
http://savefile.com/projects/236895
xdemechanik
--
 
Hi Max,

I hear what you say (that is I understand it), but I don't see that effect
myself. Could you give an example to get it through my dullness <vbg>

Thanks

Bob
 
I'm trying to build a results sheet for some work that we do.

1 column has the month in which the work was done. ie.

01/01/04
01/01/04
01/02/04
01/02/04
01/04/04
01/09/04
01/09/04
01/09/04

etc... In the example above, as you can see, some months have multiple jobs
done, others only 1. Also there are some months when no work was done at all.
The dates are always the 1st of the month, so no problems in looking at
specific dates, just the month values.

So if I wished to show the last 4 month's when work was done, what function
would I need to use to get Excel to produce a list like this...

01/01/04
01/02/04
01/04/04
01/09/04

TIA.
Click to expand...

Here's another method that will also give you access to a number of other
useful functions. Download and install Longre's free morefunc.xll add-in from

Then use this formula:

=INDEX(UNIQUEVALUES(rng,1),ROWS($1:1))

Drag down as far as necessary, although if you go too far, it will return
"blanks".

The "Rows" function is merely to set an incremental counter into the array
generated by the function.
--ron
 
Ron Rosenfeld said:
.. Did you try my suggestion?
It seems to work with you data, even with blank rows.
Click to expand...

Thanks for response, Ron.

No, I'm afraid I haven't tested (but think I can rely on your word for it).
I was awaiting Bob's response since he says that he don't see the effect
over there and asked for an example which I've provided. I was looking to
see whether the anomaly could be taken care of via a revision to the array
formula which Bob suggested, which uses standard worksheet functions
available in Excel. I usually try to work things out within this "use
standard functions" ambit, where possible.

---
 
Thanks for response, Ron.

No, I'm afraid I haven't tested (but think I can rely on your word for it).
I was awaiting Bob's response since he says that he don't see the effect
over there and asked for an example which I've provided. I was looking to
see whether the anomaly could be taken care of via a revision to the array
formula which Bob suggested, which uses standard worksheet functions
available in Excel. I usually try to work things out within this "use
standard functions" ambit, where possible.
Click to expand...

I can understand the desire to use standard functions.

By the way, with blank rows in the data, I, too, got blanks when using Bob's
formula.


--ron
 
Thanks for the comments & closure, Ron. Perhaps I might have missed the
fine subtleness behind Bob's last response <g>. Then again, I'd love to be
surprised. I'll continue to monitor this thread for awhile.

---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

separating date and time 5
Sum value between two dates and copy to new cell 6
Receiving valid Multicast address failed on mini port driver 0
Transferring CERTAIN Data from one worksheet to another 1
counting items within a date range 1
Formulea 4
Formulas with dates 3
Lookup and count all cells that fall between 2 dates 3

Back
Top