Group dates by month

[jigsaw2]

Does anyone know how to group dates to by their months using
formulae/vba code. i do not want duplicate outputs for each row.

For example
01-Jan-03
05-Jan-03
14-Jan-03
01-Feb-03
01-Mar-03
01-Apr-03

output exactly:
jan-03
feb-03
mar-03
apr-03
etc

Thanks
Lai

 

[Sofia Helena Vasconcellos]

You can use the following formula to change all dates to first day in
the month

= date(year(A2),month(A2),1)

Then you can use advanced filter to create the output as you want (use
the check box to create unique records)

Sofia Vasconcellos

 

[Alan Pong]

what i did before, in excel 97, is:
Step 1: highlight column A and then Format->Cells...-> mmm-yy
date
Jan-03
Jan-03
Jan-03
Feb-03
Mar-03
Apr-03

Step 2: Data->Subtotal...
date
Jan-03
Jan-03
Jan-03
Jan-03 subtotal Jan-09
Feb-03
Feb-03 subtotal Feb-03
Mar-03
Mar-03 subtotal Mar-03
Apr-03
Apr-03 subtotal Apr-03

Step 3: click the "2" to show sub-level 2 only.
date
Jan-03 subtotal Jan-09
Feb-03 subtotal Feb-03
Mar-03 subtotal Mar-03
Apr-03 subtotal Apr-03

Step 4: highlight the four cells only
(Jan-03 subtotal,Feb-03 subtotal,Mar-03 subtotal,Apr-03 subtotal)

Step 5: Edit -> Goto...-> Special...->Visiable cells

Step 6: Copy

Step 7: Paste on some cell

Step 8: remove the words "subtotal" by Edit -> Replace...

Here is the final result:
Jan-03
Feb-03
Mar-03
Apr-03

ok?

rgds.
alan
--END