Sorting Year & Month in Pivot table

S

SHAJEE

New Member
Joined
Sep 12, 2013
Messages
9
how to sort Year and Month in Pivot table?
the formula i applied on the date field on database for consolidating Year & Month is
=YEAR(B2)&(CHOOSE(MONTH(B2)," JAN"," FEB"," MAR"," APR"," MAY"," JUN"," JUL"," AUG"," SEP"," OCT"," NOV"," DEC")
kindly find the result , whcih is not in an order.
Iam expecting an ascending order result, please help.........
[TABLE="width: 92"]
<TBODY>[TR]
[TD]Row Labels
[/TD]
[/TR]
[TR]
[TD]2011 APR</SPAN>
[/TD]
[/TR]
[TR]
[TD]2011 DEC</SPAN>
[/TD]
[/TR]
[TR]
[TD]2011 MAY</SPAN>
[/TD]
[/TR]
[TR]
[TD]2011 NOV</SPAN>
[/TD]
[/TR]
[TR]
[TD]2011 OCT</SPAN>
[/TD]
[/TR]
[TR]
[TD]2011 SEP</SPAN>
[/TD]
[/TR]
[TR]
[TD]2012 APR</SPAN>
[/TD]
[/TR]
[TR]
[TD]2012 DEC</SPAN>
[/TD]
[/TR]
[TR]
[TD]2012 FEB</SPAN>
[/TD]
[/TR]
[TR]
[TD]2012 JAN


[/TD]
[/TR]
[TR]
[TD]</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
 
Sort by date Sort by votes
thanks, but custom sort only takes 255 ch , hence this will not SUPPORT even three year (ie from2011 jan to 2013 dec )
kindly suggest any other option ............:confused:
 
Upvote 0
[TABLE="width: 729"]
<colgroup><col><col span="10"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2011 APR[/TD]
[TD="align: right"]1[/TD]
[TD]********[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2011 DEC[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2011 MAY[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]>>>>>>>[/TD]
[TD]2011 APR[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2011 NOV[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]2011 MAY[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="colspan: 3"]this table has all the dates[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2011 OCT[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]2011 SEP[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="colspan: 3"]in calendar order, ranked[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2011 SEP[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]2011 OCT[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]1 to 10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2012 APR[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]2011 NOV[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2012 DEC[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]2011 DEC[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="colspan: 3"]cell marked >>>>>>>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2012 FEB[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]2012 JAN[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD]is E4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2012 JAN[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]2012 FEB[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2012 APR[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2012 DEC[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]the table above is your dates[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]in your order, the second[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]column finds the ranking number[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]from the table to the right[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]2011 APR[/TD]
[TD]#######[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="colspan: 2"]2011 MAY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]2011 SEP[/TD]
[TD][/TD]
[TD="colspan: 4"]this table FINDS the ranking number[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]2011 OCT[/TD]
[TD][/TD]
[TD="colspan: 2"]in the first column IN[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="colspan: 2"]2011 NOV[/TD]
[TD="colspan: 4"]the second column of top left table[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD]2011 DEC[/TD]
[TD][/TD]
[TD="colspan: 3"]and brings back the date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD]2012 JAN[/TD]
[TD][/TD]
[TD="colspan: 2"]to the left of it[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD]2012 FEB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD]2012 APR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD]2012 DEC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]cell marked ******* (B2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]=OFFSET($E$3,MATCH(A2,$E$4:$E$13,0),1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]cell marked ##### (F23)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]=OFFSET($B$1,MATCH(E23,$B$2:$B$11,0),-1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]this approach will work for any number of dates[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
PM me with your Email address and I will send the spreadsheet to you - it is difficult to explain when the formatting goes mad :-)
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Select current month in combobox on userform for English language
2 3
Replies
29
Views
688
Ali M
A
S
  • Question Question
AVERAGEIF TROUBLES
Replies
3
Views
165
AhoyNC
A
M
  • Question Question
Find 1st instance in column A, paste corresponding value from column B into another cell and continue down until end of data
Replies
2
Views
163
MrsFraser07
M
G
  • Question Question
Copy some specific columns whose header is merge
2 3
Replies
26
Views
740
Joe4
Joe4
J
  • Question Question
auto filling cells based on preset format
Replies
1
Views
77
HighAndWilder
H

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top