| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get the unique values from a range?
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I get the unique values from a range?
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 -- Max, Singapore GMT+8, xl97 Samples archive at: http://savefile.com/projects/236895 xdemechanik -- "DaveO" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I get the unique values from a range?
Thanks Max, works a treat!
"Max" wrote: 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 -- Max, Singapore GMT+8, xl97 Samples archive at: http://savefile.com/projects/236895 xdemechanik -- "DaveO" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I get the unique values from a range?
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) "Max" wrote in message ... 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 -- Max, Singapore GMT+8, xl97 Samples archive at: http://savefile.com/projects/236895 xdemechanik -- "DaveO" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I get the unique values from a range?
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) -- Max, Singapore GMT+8, xl97 Samples archive at: http://savefile.com/projects/236895 xdemechanik -- "DaveO" wrote in message ... Thanks Max, works a treat! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I get the unique values from a range?
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 -- |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I get the unique values from a range?
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 "Max" wrote in message ... 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 -- |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I get the unique values from a range?
"Bob Phillips" wrote:
.. 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 No prob ! <g, here's a quick sample: http://cjoint.com/?blm57gwlus DaveO_wks.xls -- Max, Singapore GMT+8, xl97 Samples archive at: http://savefile.com/projects/236895 xdemechanik -- |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I get the unique values from a range?
On Wed, 11 Jan 2006 00:42:02 -0800, "DaveO"
wrote: 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. 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I get the unique values from a range?
|
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I get the unique values from a range?
On Thu, 12 Jan 2006 18:14:02 +0800, "Max" wrote:
Any updates, Bob ? Max, Did you try my suggestion? It seems to work with you data, even with blank rows. --ron |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I get the unique values from a range?
"Ron Rosenfeld" wrote:
.. Did you try my suggestion? It seems to work with you data, even with blank rows. 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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I get the unique values from a range?
On Thu, 12 Jan 2006 19:52:34 +0800, "Max" wrote:
"Ron Rosenfeld" wrote: .. Did you try my suggestion? It seems to work with you data, even with blank rows. 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. 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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I get the unique values from a range?
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| counting unique values and matching to quantities | Excel Worksheet Functions | |||
| Match formula that pulls unique values from another column? | Excel Discussion (Misc queries) | |||
| Unique Values | Excel Worksheet Functions | |||
| Count Unique Values | Excel Worksheet Functions | |||
| MIN with zero values in the range | Excel Discussion (Misc queries) | |||
Linear Mode
