| 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?
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! |
|
#5
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. |
|
#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 |
| 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) | |||
Hybrid Mode
