[SOLVED] Sorting Macro

[123wc]

Hello,

I have done few Excel macros before, but I need help with this one. I need a sorting macro/solution. Here is the problem.

I have a very large data base (over 52,000 rows) that include various data under columns A-T, and I need to sort the data by weeks and days (Mon-Sun) according to the date displayed in column I. Right now the rows are randomly entered, but the rows need to be sorted as this example:

The data has to be sorted by the order of week and days--oldest to latest, e.g.:

12/1/2010 ;week 1, Mon-Sun
12/4/2010
12/5/2010
1/7/2011 ;week 2, Mon-Sun
1/9/2011
1/10/2011
1/10/2011
1/17/2011 ;week 3, Mon-Sun
1/17/2011
1/17/2011
1/18/2011
...
...

At the same time, I need the macro to add the number of days (the number of days corresponds to the number of applications) in the weeks. E.g. on a second sheet it should display something like this:

A1(week) B1(total applications)

1 5
2 12
3 2
4 7
5 20
...
...

Any idea how to do this? If column I needs to be broken into 3 columns (month, day, year) that's fine.

I have attached a sample Excel file.

Thanks a lot in advance!

MACRO_SORTING.xls

[Leith Ross]

Hello 123wc,

You can manually sort the data by date in ascending order (oldest to newest). If your starting date is always December 1 then you don't need to do a Custom sort using Criteria. To get the data over to another worksheet you do a Filter/Copy as well. Have you tried any of these methods?

[123wc]

Thank you Leith for the feedback, but I don't know how to do that

[123wc]

Pump, please!

[jaslake]

Hi 123wc

You're profile shows Excel 2007 yet you posted an .xls file. Does this need to work in pre Excel 2007 or only Excel 2007 plus?

[123wc]

At work we use Office 2007, and I would say it is nice if it could work in earlier versions but not needed though.

[jaslake]

Hi 123wc
The attached has code that appears to do as you require. Please note, due to the sort routine it will work only in Excel 2007 or greater. Let me know of issues.

[123wc]

Hello John,

Thank you very much for the amazing macro. But I have one more request: could you make it count a week from Monday to Sunday, instead of Friday to Saturday?

Thanks a lot :D

[jaslake]

Hi 123wc
We may need to play with this since I don't know what results you expect. Try changing this line of code from

.Range("J6").Formula = "=INT((I6-DATE(YEAR(I6),1,1))/7)+1"

to

.Range("J6").Formula = "=INT((I6-DATE(YEAR(I6),1,1)-WEEKDAY(I6))/7)+2"

[123wc]

Hi John,

What I want the macro to do is group all applications from Mon-Fri into weekw. For example, Week # whatever, March 19-25 may have 47 applications. I want the week to start on Monday to Sunday, and not say Saturday to Sunday, or Friday to Saturday, etc. Could you please make the macro do this?

Thanks a lot

[jaslake]

Hi 123wc
I'm attaching two files, my testing file that has two extra columns; they are there for testing only and can be deleted. After you run the procedure on this file, you will have an additional extra column...so you'll have Columns J,K and L with data you didn't have before. These columns are for demonstration purposes so you can examine how the weeks are being grouped.

The second file has what I'll call the production code and does not have these three extra columns.

Let me know of issues.

[123wc]

Thanks John, I modified the test macro for my Excel file because I liked the info in the extra columns. Your help is very much appreciated

[jaslake]

Hi 123wc
Are we getting the correct counts now?

[123wc]

Yes, all is fine now. Thank you very much