+ Reply to Thread
Results 1 to 5 of 5

rearrange data by day

  1. #1
    Registered User
    Join Date
    07-30-2005
    Posts
    2

    Exclamation rearrange data by day

    Hi I have daily data for some ten years listed downwards, and I would now like to know if there is possible to rearrange them so that I can get all the data for each day alone I.e. one group of just Monday data and another group and just Tuesday data as one group, all the numbers are currently listed from A1 to A2000. I only Monday to Friday as my days
    All help is greatly appreciated!!!

    If my explanation weren’t good enough, this is what my data looks like now
    Tuesday 12/31/2002 279,73
    Monday 12/30/2002 279,73
    Friday 12/27/2002 281,982
    Thursday 12/26/2002 278,829
    Wednesday 12/25/2002 278,829
    Tuesday 12/24/2002 278,829
    Monday 12/23/2002 278,829
    Friday 12/20/2002 277,477
    Thursday 12/19/2002 275,676
    Wednesday 12/18/2002 271,171

    All the Tuesdays to come fist then the Mondays etc, it is the actual numbers I want to rearrange.

  2. #2
    Andrea Jones
    Guest

    RE: rearrange data by day

    Perhaps you could sort it by day or use a filter to select only the data for
    that day.

    Andrea Jones
    http://www.allaboutoffice.co.uk
    http://www.stratatraining.co.uk
    http://www.allaboutclait.com

    "Adent" wrote:

    >
    > Hi I have daily data for some ten years listed downwards, and I would
    > now like to know if there is possible to rearrange them so that I can
    > get all the data for each day alone I.e. one group of just Monday data
    > and another group and just Tuesday data as one group, all the numbers
    > are currently listed from A1 to A2000. I only Monday to Friday as my
    > days
    > All help is greatly appreciated!!!
    >
    > If my explanation weren’t good enough, this is what my data looks
    > like now
    > Tuesday 12/31/2002 279,73
    > Monday 12/30/2002 279,73
    > Friday 12/27/2002 281,982
    > Thursday 12/26/2002 278,829
    > Wednesday 12/25/2002 278,829
    > Tuesday 12/24/2002 278,829
    > Monday 12/23/2002 278,829
    > Friday 12/20/2002 277,477
    > Thursday 12/19/2002 275,676
    > Wednesday 12/18/2002 271,171
    >
    > All the Tuesdays to come fist then the Mondays etc, it is the actual
    > numbers I want to rearrange.
    >
    >
    > --
    > Adent
    > ------------------------------------------------------------------------
    > Adent's Profile: http://www.excelforum.com/member.php...o&userid=25743
    > View this thread: http://www.excelforum.com/showthread...hreadid=391539
    >
    >


  3. #3
    Debra Dalgleish
    Guest

    Re: rearrange data by day

    If you don't have them already, add a row of headings to your table.

    Then, you can use and AutoFilter to view the data for a specific
    weekday. There are instructions in Excel's Help, and here:

    http://www.contextures.com/xlautofilter01.html

    Adent wrote:
    > Hi I have daily data for some ten years listed downwards, and I would
    > now like to know if there is possible to rearrange them so that I can
    > get all the data for each day alone I.e. one group of just Monday data
    > and another group and just Tuesday data as one group, all the numbers
    > are currently listed from A1 to A2000. I only Monday to Friday as my
    > days
    > All help is greatly appreciated!!!
    >
    > If my explanation weren’t good enough, this is what my data looks
    > like now
    > Tuesday 12/31/2002 279,73
    > Monday 12/30/2002 279,73
    > Friday 12/27/2002 281,982
    > Thursday 12/26/2002 278,829
    > Wednesday 12/25/2002 278,829
    > Tuesday 12/24/2002 278,829
    > Monday 12/23/2002 278,829
    > Friday 12/20/2002 277,477
    > Thursday 12/19/2002 275,676
    > Wednesday 12/18/2002 271,171
    >
    > All the Tuesdays to come fist then the Mondays etc, it is the actual
    > numbers I want to rearrange.
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  4. #4
    David McRitchie
    Guest

    Re: rearrange data by day

    Hi Adent,
    I am confused with the data layout, since it was stated all numbers are
    in column A. Don't know if you have Excel dates (numbers) formatted
    to show dddd mm/dd/yyyy and an additional column with an amout.

    If that is all one formatted date, you would
    need to create a helper column to assign a day of week value to.
    C2: =WEEKDAY(A2,1)

    You could then sort the entire sheet on the helper column
    and use Data, Subtotal for the report.

    Since you want Tuesday to come before Monday, and have not said
    what you want to do with the other days I can't help you with a
    substitution for the WEEKDAY number. The 1 is going to start
    the week on Sunday, which is a rather safe assumption since you
    have a US date format. But you really should check HELP anyway
    if not familiar with something.

    If everything is in Column a then everything is text and you can split
    apart with Data, Text to Columns.
    ---
    HTH,
    David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
    My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
    Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

    "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message news:42EBACB6.9070409@contexturesXSPAM.com...
    > If you don't have them already, add a row of headings to your table.
    >
    > Then, you can use and AutoFilter to view the data for a specific
    > weekday. There are instructions in Excel's Help, and here:
    >
    > http://www.contextures.com/xlautofilter01.html
    >
    > Adent wrote:
    > > Hi I have daily data for some ten years listed downwards, and I would
    > > now like to know if there is possible to rearrange them so that I can
    > > get all the data for each day alone I.e. one group of just Monday data
    > > and another group and just Tuesday data as one group, all the numbers
    > > are currently listed from A1 to A2000. I only Monday to Friday as my
    > > days
    > > All help is greatly appreciated!!!
    > >
    > > If my explanation weren’t good enough, this is what my data looks
    > > like now
    > > Tuesday 12/31/2002 279,73
    > > Monday 12/30/2002 279,73
    > > Friday 12/27/2002 281,982
    > > Thursday 12/26/2002 278,829
    > > Wednesday 12/25/2002 278,829
    > > Tuesday 12/24/2002 278,829
    > > Monday 12/23/2002 278,829
    > > Friday 12/20/2002 277,477
    > > Thursday 12/19/2002 275,676
    > > Wednesday 12/18/2002 271,171
    > >
    > > All the Tuesdays to come fist then the Mondays etc, it is the actual
    > > numbers I want to rearrange.
    > >
    > >

    >
    >
    > --
    > Debra Dalgleish
    > Excel FAQ, Tips & Book List
    > http://www.contextures.com/tiptech.html
    >




  5. #5
    Registered User
    Join Date
    07-30-2005
    Posts
    2
    Well the auto filter thing worked fine, thanks guys you saved me a bunch of work I actually started doing in manually which probably would have taken me about 4 months, buy your self an ice cream from me, I will thank you all in the beginning of this master thesis!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1