• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

need to flip rows in spreadsheet

W

wnorrick

Member
I have a fairly standard worksheet that records data by week ending date. it is currently in date order starting with oldest date first going to current date. I need to flip the spreadsheet so it is in date order going from current date down to oldest date. there are 52 weeks listed and some of the cells going across have formulas.


How can I flip the worksheet upside down?


Thank you
 
Wnorrick


Select all your data including a header row

Goto Data Sort and select the appropriate fields to sort by, probably Date and Descending

you can also use a secondary / tertiary sort field if required


Then Check your formulas


As always work on a backup copy first to ensure that your data is safe
 
thank you Hui. However I apologize that I didn't fully explain my dilemma. My dates are in weekly ranges and look like this:

August 27, 2012 - September 2, 2012

September 3, 2012 - September 9, 2012

September 10, 2012 - September 16, 2012

September 17, 2012 - September 23, 2012

To add to my problem there are some blank rows due to some of the formatting (I inherited the spreadsheet).

The date ranges above are in column b, column a is blank and c through AW contains data with some formulas.

Any suggestions?

Thank you
 
Hi Norrick ,


If you are sure that the existing setup is in a strictly ascending order of weeks , then a simple complete flip of the worksheet may be what you want ; in such a case , this macro may help :


http://stackoverflow.com/questions/524844/how-to-reverse-the-order-of-a-selected-range-of-excel-columns-using-vba


Narayan
 
I would add a helper Column


In Row 1 of that column use a Formula like

Code: 
=DATE(MID(A1,FIND(", ",A1)+2,4),MONTH(LEFT(A1,FIND(" ",A1)-1)&1),MID(A1,FIND(" ",A1)+1,FIND(", ",A1)-FIND(" ",A1)-1))

Change Column A to the column with your dates

This formula extracts the date of the first entry in each row Column A


Copy down


Copy / Paste the whole column as Values


Then Sort the whole data areas by that column


That will put all the blanks and other strange rows at the Top or Bottom and you can then deal with them


Delete the helper column when/if your finished with it
 
If you just want to flip the Rows as Narayan suggested


Add a helper Column


In Row 1 add a formula =Row()


Copy down


Copy/paste the column as values


Then sort the whole data area by that Column Descending


Delete the helper column when/if your finished with it
 
You must log in or register to reply here.
Back
Top