Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rearrange Data (Excel 97)

    I have a Excel Worksheet with aprox 10,000 rows. I need to rearrange the Data per the attached workbook. Any ideas?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rearrange Data (Excel 97)

    I'd advise against rearranging the data like this, because with the old setup you can very easily use Data, Pivot table to analyse your data. The new arrangement prevents that.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Rearrange Data (Excel 97)

    I agree with Jan, that rearranging could be more problematic in the future.

    What are you trying to accomplish with the data? There might be a better way to set it up.

    To get the "Want" directly from the data could be done with a little more manipulation, but still allow you to have a dataset that you can work with. Instead of 10,000 rows of date, room, start, finish, I would have 20,000 rows of:
    Date, room, "type" (= start or finish), Number (1,2,3), and "time"

    Then you could get your output directly from the data to get a pivot table with what you want.
    I created the data manually, by inserting 2 columns before 'Start', Labeling the first 'Type', and second 'Num", and relabeling 'Start" to TIME.
    I filled in Type with "START"
    I then copied A/B column info (Date/Room) below that set of data, and then copied the "finish" data below the start data in 'time'. I filled in Type with 'FINISH'
    To get the value of 'Num',
    I used the ARRAY formula in D2 (confirm sith ctrl-shift-enter):
    <pre>=SUM(IF(($A$2:A2=A2)*($B$2:B2=B2)*($C$2:C2=C2 ),1))</pre>


    and copied it down the 'Num Col' (this counts the num of previous "Duplicates".
    I then copied and PasteSpecial - Values the num col.

    I created a pivot table from this data (right click and look at wizard for the setup.
    Num col will "automatically" count as high as neccessary, and the pivot will adjust with Refresh.

    This setup will allow filtering and other features.
    Hope this helps,
    Steve

  4. #4
    New Lounger
    Join Date
    Jan 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rearrange Data (Excel 97)

    Jan & Steve,

    First, Thank you for looking at my problem.

    What I'm trying to do is arrange the data for review and for a chart. I tried to send a small sample chart but the file is to big.

    This data is start & stop times for surgeries on various dates in various operating rooms. I need to visually show the start & stop times of the surgeries and the time between each surgery. Each line "Record" has to have the surgeries that happen that day in that operating room in the order that they occurred.

    Hope this helps and thanks for your time.

    Richard

Posting Permissions

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