Is there a better way to do this?

Microsoft Excel 2010 software
August 31, 2010 at 15:53:20
Specs: Windows 7
Currently I get data like this for each day:

6:00 7.08 63.17 96.24 9.27 1.84 65.64 13 5.05 3 1 3 62.64 12 5.22
7:00 10.8 94.29 13.54 64.49 10.46 696.51 113 6.16 243.71 40 6.09 452.8 73 6.2
8:00 14.22 124.31 12.9 67.77 9.78 963.45 139 6.93 471.19 73 6.45 492.26 66 7.46
9:00 19.08 163.58 12.34 69.46 9.22 1,325.57 176 7.53 686.95 104 6.61 638.62 72 8.87
10:00 21.13 178.28 15.32 55.07 7.38 1,163.75 156 7.46 568.06 94 6.04 595.69 62 9.61
11:00 21.33 183.44 27.91 30.81 4.83 657.36 103 6.38 229.1 46 4.98 428.26 57 7.51
12:00 25.78 219.82 13.18 64.7 8.38 1,668.12 216 7.72 1,023.62 144 7.11 644.5 72 8.95
13:00 25.43 216.51 13.95 61.04 8.06 1,552.38 205 7.57 930.2 124 7.5 622.18 81 7.68
14:00 19.27 162.68 23.04 36.64 5.55 705.93 107 6.6 254.49 44 5.78 451.44 63 7.17
15:00 15.63 131.67 26.36 31.95 5.18 499.54 81 6.17 137.74 30 4.59 361.8 51 7.09
16:00 15.28 128.63 24.34 34.58 5.17 528.54 79 6.69 157.79 29 5.44 370.75 50 7.42
17:00 9.53 80.33 17.24 48.87 7.13 465.91 68 6.85 170.3 30 5.68 295.61 38 7.78
18:00 9.93 86.08 11.44 75.73 9.06 752.28 90 8.36 367.96 47 7.83 384.32 43 8.94
19:00 9 77.76 11.43 75.59 8.11 680.29 73 9.32 275.26 31 8.88 405.03 42 9.64
20:00 7.38 62.69 13.23 64.17 7.04 473.76 52 9.11 184.47 22 8.39 289.29 30 9.64
21:00 6.22 52.69 8.26 102.57 10.78 637.67 67 9.52 317.45 32 9.92 320.22 35 9.15
22:00 7.33 62.45 13.05 65.24 7.09 478.44 52 9.2 151 19 7.95 327.44 33 9.92
23:00 7.22 61.09 61.44 13.78 1.39 99.43 10 9.94 0 1 0 99.43 9 11.05

A jumbled mess for you to see, I apologize. It's basically a string of data for the day; Columns A-O have different data for the day, while the rows are the hours of the day. I need the date extrapolated from this download and separated into it's hours. I have basically been cutting and pasting the 6-7 hour into a separate workbook under the page 6-7; then cutting and pasting the 7-8 into the page for 7-8, etc etc. This of course works, but is VERY time consuming and hard on the eyes and back after a while. I have years of data to sort by their time; the 6-7 page will include the 6-7 hour for each day for the entire year; then another for year 2 and so on. So these pages need to be separate for the hours. (5-6,6-7,7-8,8-9,9-10,10-11,11-12,12-13,13-14,14-15,15-16,16-17,17-18,18-19,19-20,20-21,21-22,22-23). Once the data is all loaded and copied, we table each column to be able to manipulate the data how we want it. ie: let's look at the data from 7-8 on mondays... or whatever. I thought maybe access would be better, but am not sure at this point if access will better automate the process. Any help our suggestions would be appreciated.

See More: Is there a better way to do this?

August 31, 2010 at 17:59:05

You say Columns A-O have different data for the day, while the rows are the hours of the day, but the data you posted has the hour in the first column.
Was this just for explanation, or does your data have the hour in column A.

I put your data into a worksheet and using Text to columns, I ended up with data in columns A to O with the hour in column A

A second issue:
You say I have basically been cutting and pasting the 6-7 hour into a separate workbook under the page 6-7; then cutting and pasting the 7-8 into the page for 7-8, etc etc.
Do you mean that you have been cutting the row with 6:00 in column A and pasting it to a new workbook with a worksheet named "6-7", or were you pasting it to a new worksheet in the same workbook.

What you need to do is to be specific about where your source data is stored.

Do you have a different workbook for each day.
Is the data for each day in a specific named worksheet.
Is data for different years in different workbooks.
etc., etc.

Without knowing exactly how your source data is stored, there is no way that a solution can be offered.

Also can you explain what you mean by we table each column in the phrase: Once the data is all loaded and copied, we table each column.

From what you have said so far I would guess that Excel is capable of moving and organizing your data.


Report •

August 31, 2010 at 21:49:02
1. The data does indeed have the hour in column A, but I don't need that column so just eliminate it when copying. Also, I don't need the data from J-O. I am basically using the data from columns B-I.

2. Because there is soo much copying and pasting, I have the data in a separate workbook. The completed data gets copied into a workbook with separate tabs (sheets) for each hour (5-6; 6-7; etc). I found it easier to copy from one workbook to another rather than putting the dump in the same workbook and having to constantly switch tabs. By using two separate workbooks, and I can have two windows open and just copy from one to the other. A little faster for me. It does not have to stay this way, it's just the only solution I have right now.

3. The source data is collected from an online software program that does not have the ability to auto-download to excel. But I can bring up the data for a day, then copy and paste that into an excel document to edit/manipulate. Right now, I am using two separate workbooks: "new TPMH data" (the source document), and "New TPMH" (the final document that has separate tabs at the bottom for each hour).

4. By tabling, I mean using the Table Function in Excel. For each hour that I copy/paste, I have 3 additional columns at the beginning "Month", "Day of Week", and "Date". Those, along with the Data headers for "Columns B-I" of the source, allows us to sort our data as we need. IE: All "xxx" for January; or all "xxx" for a Saturday; or all "xxx" for weekends... etc. Each time tab (6-7 for example) has the dates from 1 Jan - 31 Dec, or a years worth of data. We use this for other data sets that I can pull data for the entire year at a time, or an entire month at a time which is obviously easier. This particular data set can only be pulled one day at a time which makes it VERY arduous to complete. I was just looking for an easier way to strip the data source into the separate times that I need.

Report •

September 1, 2010 at 06:35:48

I am proposing a slightly different approach.

Have one workbook named "New TPMH.xlsm" (or .xls if using Excel 2003 or earlier)
The xlsm file type is a 'macro enabled' workbook

Have one worksheet named "New TPMH data"
Have 24 worksheets named 00 to 23
(or less if your data is only ever 06:00 to 23:00)

Paste each days data into the worksheet "New TPMH data" - delete previous data first or paste over old data if data always has same number of rows/columns.

Don't delete column A (times) and there is no need to remove unwanted data in columns J onwards.

I suggest running the following macro from a button embedded in the "New TPMH data" worksheet.

From the Ribbon select Developer (In Excel 2010 -> If it's not visible go to the File tab, select 'Options' at the bottom of the File menu list above 'Exit" and select the 'Customize Ribbon' tab in the 'Excel Options' Dialog box. In the 'Customize the Ribbon:' drop-down, select 'Main Tabs' and check the 'Developer' check box).

In Developer - Controls select Insert and choose the button icon (Button - Form control).
Draw the button on the worksheet
In the 'Assign Macro' dialog box select 'New'

In the code window that opens enter this:

Option Explicit

Sub Button1_Click()
Dim rngCell As Range
Dim rngEnd As Range
Dim rngSearch As Range
Dim strHour As String

On Error GoTo ErrHnd

'turn off screen updating to remove flicker and increase speed
Application.ScreenUpdating = False

'find end of data in column A on New TPMH data worksheet
Set rngEnd = ActiveWorkbook.Worksheets("New TPMH data") _
                .Range("A" & CStr(Application.Rows.Count)).End(xlUp)
'setup search range
Set rngSearch = ActiveWorkbook.Worksheets("New TPMH data") _
                .Range("A1", rngEnd.Address)

'loop through the hours data in column A
For Each rngCell In rngSearch
    'process only if there is data in column A cell
    If rngCell.Value <> "" Then
        'hour expressed as two characters e.g., "06"
        strHour = Format(CStr(Hour(rngCell.Text)), "00")
        'copy data in columns B to I (8 columns)
        rngCell.Offset(0, 1).Resize(1, 8).Copy
        'find row after end of existing data in appropriate worksheet
        'and paste the data
        ActiveWorkbook.Worksheets(strHour) _
                    .Range("A" & CStr(Application.Rows.Count)) _
                    .End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll
    End If
Next rngCell
're-enable screen updating
Application.ScreenUpdating = True
Exit Sub

'error handler
're-enable screen updating
Application.ScreenUpdating = True
MsgBox "Error in macro " & vbCrLf & "Please check data"
End Sub

Note that Sub Button1_Click() and End sub will already be present, so don't duplicate them.
Option explicit goes before Sub Button1_Click().

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.

Right click the button and Edit the name to something meaningful

As changes made by Macros cannot be undone with the Undo button, test this macro on a copy of your data.
Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'

After selecting any cell, the new command button should now respond to a click and run the macro.

Each new set of data pasted into the New TPMH data worksheet can be added by clicking the button, but if you click the button a second time, the data will be added a second time to each 'Hour' worksheet.

Note that this macro requires that there are worksheets named "06" etc. If they are not in this exact format (don't include the double quotes), then the macro will fail.

I have included a simple message to warn if the macro fails - there isn't anything fancy to help tell what went wrong - just a warning, so that you can check your source data and see what was /wasn't copied.


Report •
Related Solutions

Ask Question