Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting dates
Hi , I have a spread sheet that I use to indicate when a two monthly job
needs doing, a date is put into cell B3 and dates in two monthly gaps are put into B4 B5 B6 for twelve columns and for ten rows, each row starts with a different date so they are quite mixed up in the columns , if I name the columns Jan/Feb/ March and so on, is there any way that I can get all the dates that are in January to populate the column marked Jan and all the dates that are in February to populate the Feb column . The dates read ( 12 march 2008 ) with the cell formatted to read that way. Thanks in advance, Tommy |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting dates
You need a macro. The macro assumes the data is in cells B4:M15 (change as
required) and that Jan - Dec is in Row 1 and the months are in columns where no other data is located. Sub ArrangeDates() Set DateRange = Range("B4:M15") For Each cell In DateRange MyMonth = Format(cell, "mmm") Set c = Rows(1).Find(what:=MyMonth, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If c Is Nothing Then MsgBox ("Cannot find month : " & MyMonth) Else LastRow = Cells(Rows.Count, c.Column).End(xlUp).Row Newrow = LastRow + 1 Cells(Newrow, c.Column) = cell End If Next cell 'sort column Data For MonthNum = 1 To 12 MyMonth = Format(MonthNum & "/1/" & Year(Date), "mmm") Set c = Rows(1).Find(what:=MyMonth, _ LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If c Is Nothing Then MsgBox ("Cannot find month : " & MyMonth) Else LastRow = Cells(Rows.Count, c.Column).End(xlUp).Row Range(c, Cells(LastRow, c.Column)).Sort _ key1:=c, _ order1:=xlAscending, _ header:=xlYes End If Next MonthNum End Sub "tommy" wrote: Hi , I have a spread sheet that I use to indicate when a two monthly job needs doing, a date is put into cell B3 and dates in two monthly gaps are put into B4 B5 B6 for twelve columns and for ten rows, each row starts with a different date so they are quite mixed up in the columns , if I name the columns Jan/Feb/ March and so on, is there any way that I can get all the dates that are in January to populate the column marked Jan and all the dates that are in February to populate the Feb column . The dates read ( 12 march 2008 ) with the cell formatted to read that way. Thanks in advance, Tommy |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting dates
Thanks where do I put the Macro, is it right click on XL and view code?
"tommy" wrote: Hi , I have a spread sheet that I use to indicate when a two monthly job needs doing, a date is put into cell B3 and dates in two monthly gaps are put into B4 B5 B6 for twelve columns and for ten rows, each row starts with a different date so they are quite mixed up in the columns , if I name the columns Jan/Feb/ March and so on, is there any way that I can get all the dates that are in January to populate the column marked Jan and all the dates that are in February to populate the Feb column . The dates read ( 12 march 2008 ) with the cell formatted to read that way. Thanks in advance, Tommy |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting dates
Sorry joel I have lead you astray B4 B5 B6 should read C3 D3 E3 for twelve
columns and ten rows from B3 to B13. "tommy" wrote: Hi , I have a spread sheet that I use to indicate when a two monthly job needs doing, a date is put into cell B3 and dates in two monthly gaps are put into B4 B5 B6 for twelve columns and for ten rows, each row starts with a different date so they are quite mixed up in the columns , if I name the columns Jan/Feb/ March and so on, is there any way that I can get all the dates that are in January to populate the column marked Jan and all the dates that are in February to populate the Feb column . The dates read ( 12 march 2008 ) with the cell formatted to read that way. Thanks in advance, Tommy |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting dates
Your description wasn't consistent with the number of rows and columns that
is why I made it easy to change from: Set DateRange = Range("B4:M15") to Set DateRange = Range("B3:N15") You put the code in the VBA page. there are a number of way to get to the VBA. the easiest is to type Alt-F11. for the code to work on every page it is best to put into a MODULE page. Add a module into the VBA window using the menu in VBA Insert - Module. then copy the code from the posting to the VBA window. "tommy" wrote: Sorry joel I have lead you astray B4 B5 B6 should read C3 D3 E3 for twelve columns and ten rows from B3 to B13. "tommy" wrote: Hi , I have a spread sheet that I use to indicate when a two monthly job needs doing, a date is put into cell B3 and dates in two monthly gaps are put into B4 B5 B6 for twelve columns and for ten rows, each row starts with a different date so they are quite mixed up in the columns , if I name the columns Jan/Feb/ March and so on, is there any way that I can get all the dates that are in January to populate the column marked Jan and all the dates that are in February to populate the Feb column . The dates read ( 12 march 2008 ) with the cell formatted to read that way. Thanks in advance, Tommy |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting dates
Thank you Joel.
"tommy" wrote: Hi , I have a spread sheet that I use to indicate when a two monthly job needs doing, a date is put into cell B3 and dates in two monthly gaps are put into B4 B5 B6 for twelve columns and for ten rows, each row starts with a different date so they are quite mixed up in the columns , if I name the columns Jan/Feb/ March and so on, is there any way that I can get all the dates that are in January to populate the column marked Jan and all the dates that are in February to populate the Feb column . The dates read ( 12 march 2008 ) with the cell formatted to read that way. Thanks in advance, Tommy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Dates | New Users to Excel | |||
Need help sorting DATES please.. | Excel Worksheet Functions | |||
Sorting dates | Excel Worksheet Functions | |||
Sorting Dates | Excel Worksheet Functions | |||
Sorting dates | Excel Worksheet Functions |