Sorting months and data from large set of data

**dimuthumme** · 03-16-2014, 07:36 PM

Hello,

I've got irradiance data from 2007 to 2010 in sequential order as per the sheet 1(Attachment). Firstly I want to sort the date in a new column(F) in the order of 2007 January, then 2008 January, 2009 January, and finally 2010 January. Then another new column 2007 February, 2008 February, etc. and so on for remaining months. Secondly, I want to sort the irradiance data in the similar manner with respect to relevant dates. Kindly help me with the relevant formulas for one month. Thank You very much.

**Bernie Deitrick** · 03-16-2014, 10:58 PM

Use a column of formulas - if your dates are in column A, starting in row 2, in cell F2 use the formula

=MONTH(A2) + YEAR(A2)/10000

IF your dates are actually in column F, use this in another column, in row 2:

=MONTH(F2) + YEAR(F2)/10000

Then copy down to match your data, and sort your entire table based on that column.

**dimuthumme** · 03-16-2014, 11:21 PM

Hi Bernie,

Thank you for the reply. However,much to my regret, it doesn't work.
You can see it in the attachment. Thank you very much again.

**dimuthumme** · 03-17-2014, 07:51 AM

I've got dates from 2007 to 2010 in sequential order(Attachment). I want to sort the date in a new column(F) in the order of 2007 January, then 2008 January, 2009 January, and finally 2010 January.

Then another new column 2007 February, 2008 February, etc. and so on for remaining months. Secondly I want to sort the data in similar manner with respect to relevant dates. Kindly help me with the relevant formulas for one month.

I am trying to sort by month... as all January months together and all Feb. months together for years 2007 to 2010.Thank You very much.

**Kaper** · 03-17-2014, 07:57 AM

But do you plan to add (or average) all values from Jan 01 2007, Jan 1 2008, Jan 1 2009 and so on, or want to have this just sorted
JAn 1 2007
...
Jan 31 2007
Jan 1 2008
...
Jan 31 2008
etc

And how about hours within days ? summed? averaged? listed all?

For me it looks like pivot table task, may be with some pre-processing (by formula) of data.

**dimuthumme** · 03-17-2014, 08:02 AM

Hi Kaper,
Thank you very much. Yes, first I want to sort the dates similar to
JAn 1 2007
...
Jan 31 2007
Jan 1 2008
...
Jan 31 2008
etc.

Hope I can generate the hours also in next column. Then I want to match the Irradiance data in column D in similar way. All in all, sorting data for relevant date month wise.

**dimuthumme** · 03-17-2014, 08:03 AM

It is not required to get average or addition. Just sorting is required.

**Fotis1991** · 03-17-2014, 08:05 AM

In F3 and drag to G3 and then drag both down, this ARRAY formula will gives you the results but probably will kills your computer..

Formula:

Please Login or Register  to view this content.

Confirm using CTR+SHIFT+ENTER.

**Kaper** · 03-17-2014, 08:46 AM

How about reasonably quick macro - wont kill your computer ;-)
Fill the headers in rows 1 and 2 for next months before using:

Please Login or Register  to view this content.

**dimuthumme** · 03-17-2014, 08:56 AM

Hi
Thank you very much.
But it doesn't work. I get #NUM! error starting from B4 row. I works only for B3 row.
I can't attach the sheet. It exceeds 1 MB.

**Fotis1991** · 03-17-2014, 08:58 AM

Did you confirmed as an ARRAY formula?

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

@ Kaper

...- wont kill your computer ;-)

**dimuthumme** · 03-17-2014, 09:49 AM

Hi,
Tusen takk. Oh.. not Norsk

Thank you very much.

It seems, it is working. My computer is almost killed. I hv to go to laboratory to run the rest.
You, people are great. It is a great effort.

**Bernie Deitrick** · 03-17-2014, 09:59 AM

Sorting months_reply1.xlsx

Actually, it did - just format column E as a number with 4 decimals instead of as dates. You will see

1.2007

in the first cell, and the sort will work perfectly. You can convert the column of formulas to values if you want to reduce the overhead, or delete it after the sort is finished.

**Fotis1991** · 03-17-2014, 10:26 AM

Can you pls explain the difference between this thread and this one?

http://www.excelforum.com/excel-gene...ml#post3625435

**dimuthumme** · 03-17-2014, 10:30 AM

I apologize for my being fault. Both threads are almost same, Replies of both of them were useful to solve out different issues I had.

**Fotis1991** · 03-17-2014, 10:34 AM

I suggest to take some minutes to read forum rules. In this case in 2 different threads different people offered their time trying to helps you. Is not correct, to spend our time posting duplicate threads.

Only for this time i just merg the 2 threads. I hope to don't do it again because in that case you'll force me to give you an infraction.

**dimuthumme** · 03-17-2014, 10:38 AM

Yes, I do understand. I will never repeat the same mistake.
I humbly beg most kind apologies from all the forum members.

**Fotis1991** · 03-17-2014, 10:49 AM

Your understanding is appreciated!

As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED,as per Forum Rule #9. Thank you.

Sorting months and data from large set of data

LinkBack

Thread Tools

Rate This Thread

Display

Sorting months and data from large set of data

Re: Sorting months and data from large set of data

Re: Sorting months and data from large set of data

Sorting same type of months together from different years of data

Re: Sorting same type of months together from different years of data

Re: Sorting same type of months together from different years of data

Re: Sorting same type of months together from different years of data

Re: Sorting same type of months together from different years of data

Re: Sorting same type of months together from different years of data

Re: Sorting same type of months together from different years of data

Re: Sorting same type of months together from different years of data

Re: Sorting same type of months together from different years of data

Re: Sorting months and data from large set of data

Re: Sorting months and data from large set of data

Re: Sorting months and data from large set of data

Re: Sorting months and data from large set of data

Re: Sorting months and data from large set of data

Re: Sorting months and data from large set of data

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Sorting Large Data Set with Mixed Data

Sorting large groups of data..

Sorting large data set

Multilookup alongwith SORTING for only MONTHS plus CLUBBING LIKE Data

Sorting problem with large data set

Bookmarks

Bookmarks

Posting Permissions