# Thread: Rearrange Data (Excel 97)

1. ## 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. ## 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.

3. ## 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. ## 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
•