excel 2007 macro to sort data to new sheet

February 4, 2011 at 07:02:15
Specs: Windows 7
Ok, first of all I'm new to your site and WOW! I am incredibly impressed with the wealth of knowledge I've come across while searching through various forums! I've already found all kinds of great macros and ideas that will make my workload a lot easier.

That being said, I have a problem with efficiently sorting some data and I hope one of you macro gurus can assist :)

I've found several macros that do something similar to what I'm looking for, so hoping to get something a little more specific. Basically, I have some weather equipment that monitors up to 5 things in the air and then transmits data to my computer via radio, and spits out text files that I then copy into excel. It creates one text file for each thing I'm monitoring. The format is:

Date (mm:dd:yy:hh:mm:ss), WhatIsMonitored, Unit#, Concentration

So for example if I'm using this equipment to monitor the concentration of oxygen in the air over the course of a couple minutes using just one unit (and by unit I mean the machine that is doing the monitoring), it will give me something like:

09/01/2010 17:02:55,O2,U_10_O2,20.900000
09/01/2010 17:03:09,O2,U_10_O2,20.900000
09/01/2010 17:03:25,O2,U_10_O2,20.900000
09/01/2010 17:03:40,O2,U_10_O2,20.900000
09/01/2010 17:03:55,O2,U_10_O2,20.900000
09/01/2010 17:04:10,O2,U_10_O2,20.900000
09/01/2010 17:04:25,O2,U_10_O2,20.900000

So for in this case it's not too much trouble to just copy the text into Excel, then use the "text to columns" feature to put it in columns.

The problem I'm running into is when there are multiple units running at the same time, each monitoring up to 5 different things... and they might run continuously for a week or more at a time. The text files are generated based on whatever you're monitoring, so again using the same example with checking JUST for oxygen I'd get one text file that would include one long column of data with the aforementioned data for each unit. So something like:

09/01/2010 17:02:55,O2,U_10_O2.txt,20.900000
09/01/2010 17:03:09,O2,U_11_O2.txt,20.900000
09/01/2010 17:03:25,O2,U_11_O2.txt,20.900000
09/01/2010 17:03:40,O2,U_11_O2.txt,20.900000
09/01/2010 17:03:55,O2,U_12_O2.txt,20.900000
09/01/2010 17:04:10,O2,U_12_O2.txt,20.900000
09/01/2010 17:04:25,O2,U_13_O2.txt,20.900000

Like I said it generates one text file per thing you're monitoring, so I'm assuming if anyone can come up with a macro to sort one thing, it will work just as well on another. As it is, I have to go through these worksheets line by line cutting and pasting the data from each unit to it's own separate sheet...

So in a nutshell, what I'm looking for is a macro to sort this data into separate sheets by date and Unit# (i.e. from the previous example text file data it would be put into 4 sheets: 09-01-2010 Unit 10, 09-01-2010 Unit 11, 09-01-2010 Unit 12, and 09-01-2010 Unit 13.

This seems very confusing and hopefully I've explained the problem adequately for one of you excel brainiacs to help me out with a solution. And please let me know if I can make things clearer or you require more information. Thanks a million!

See More: excel 2007 macro to sort data to new sheet

February 5, 2011 at 17:14:03
After you use the text to columns feature, I assume your data looks like this:

       A           B            C             D           E 
1  09/01/10	17:02:55	O2	U_10_O2.txt	20.9
2  09/01/10	17:03:09	O2	U_11_O2.txt	20.9
3  09/01/10	17:03:25	O2	U_11_O2.txt	20.9
4  09/01/10	17:03:40	O2	U_11_O2.txt	20.9
5  09/01/10	17:03:55	O2	U_12_O2.txt	20.9
6  09/01/10	17:04:10	O2	U_12_O2.txt	20.9
7  09/01/10	17:04:25	O2	U_13_O2.txt	20.9

This will probably need some tweaking, but it might get you started:

Sub SplitSheets()
Dim ws As Worksheet
Dim lastDate, srcRw, dstRow As Integer
Dim newdate, wsName As String
'Determine last row with data
 lastDate = Range("A" & Rows.Count).End(xlUp).Row
'Loop through data
  For srcRw = 1 To lastDate
'Remove slashes from dates
    newdate = Month(Cells(srcRw, 1)) _
              & "-" & Day(Cells(srcRw, 1)) _
              & "-" & Year(Cells(srcRw, 1))
'Build worksheet name
     wsName = newdate & " Unit " & Mid(Cells(srcRw, 4), 3, 2)
'Create sheet if it doesn't exist
     On Error Resume Next
      Set ws = Sheets(wsName)
     On Error GoTo 0
      If ws Is Nothing Then
        Sheets.Add after:=Sheets(Sheets.Count)
        ActiveSheet.Name = wsName
      End If
'Copy row to next open row in corresponding sheet
       dstRow = Sheets(wsName).Cells(Rows.Count, 1).End(xlUp).Row + 1
        Sheets(1).Cells(srcRw, 1).EntireRow.Copy _
         Destination:=Sheets(wsName).Cells(dstRow, 1)
         Set ws = Nothing
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

February 5, 2011 at 23:00:01
Thanks for the assist! Won't be able to try it out til Monday, but fingers are crossed! :)

Report •

February 14, 2011 at 07:27:46
Ok, Derby, first just wanted to say thanks again for taking a crack at this. Been away from the office for a while working on a project out of state, but I just got back and tried out your macro. It started out looking like it was working, but then I got an error message that said "Run-time error '6': Overflow" and when I hit Debug it highlights line 27:

" dstRow = Sheets(wsName).Cells(Rows.Count, 1).End(xlUp).Row + 1"

The data I tried it on had 6 units running for 3 days. There were a couple sheets output before getting the error message, though. However, it only showed the first couple hour's worth of data for Unit 1 on Day 1. Then a sheet was generated for Unit 2 for Day 1 but it was blank. And then a third sheet was generated saying Unit 2 from the year 1899 and had pieces from Units 1-4.

So basically, it seems like this is getting very close to doing what I need it to do, but still needs some debugging. I'm unfortunately pretty lost in general when it comes to writing and debugging these macros (hence asking for your expertise here haha), so if there's anything you can do I'd appreciate it. And let me know if you need any additional information. Thanks!

Report •

Related Solutions

February 14, 2011 at 07:41:17
How many pieces of data are we dealing with?

Dim lastDate, srcRw, dstRow As Integer

means that the code can handle up to 32,767 rows.

If you change that line to:

Dim lastDate, srcRw, dstRow As Long

you should be able to work with 2,147,483,647 rows

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

February 14, 2011 at 12:34:20
Hey Derby, thanks for the quick response! In the case of my current data I'm dealing with about 50,000 rows. I tried changing the line to "Dim lastDate, srcRw, dstRow As Long" as you suggested, and this took care of the error message. However, the sheets that are output still aren't quite right. I'll try to describe the problem (and I can send you an example of some of the data I'm working with if that would be helpful) and hopefully you can work some more magic on it. Like I said the data I tried it on just now was from 6 different units. So on each row I have a column with the date, the name of what is being monitored, the unit #, and the actual reading and needs to be sorted into individual pages as referenced in my original post. This macro starts out seeming to work correctly, but the first page generated only has 29 rows. Like I said I'm working with about 50,000 rows of data, and of that data the first separate sheet should have over 700 rows. So as to why it stops at 29 I have no idea. Then it spits out a 2nd page that has the header at the top of the columns, but no actual data. Then a third page is generated with the year 1899 containing basically the original sheet minus the 29 rows that made it onto the first generated sheet. Any ideas?

Report •

February 14, 2011 at 13:28:54
Keep in mind that the code is working on data that I *assume* looks exactly like what I have posted in Response #1.

Specifically, for line 1 of your example data you should have:

Column A 09/01/10
Column B 17:02:55
Column C O2
Column D U_10_O2.txt
Column E 20.9

If the data doesn't look exactly like that, it's very possible that the code will not work correctly.

You didn't say anything about a header row when you posted your example data, so the code assumes that Row 1 contains:

09/01/2010 17:02:55,O2,U_10_O2,20.900000

I just replicated the 7 lines in your example 7,135 time to get a file that contains 49,945 rows. When I ran the code, it created 4 worksheets and copy split the data as follows:

9-1-2010 Unit 10 - 7,136 rows
9-1-2010 Unit 11 - 21,406 rows
9-1-2010 Unit 12 - 14,271 rows
9-1-2010 Unit 13 - 7,136 rows

Row 1 of each sheet is blank

If you have a header row that needs to be copied to each sheet, then some modifications to the code will be required.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

February 14, 2011 at 15:12:24
Ah ok. No, there is not actually a header row (I just meant that's what the actual data IS, but there is no actual header stating that). Although I think I see part of the problem. I missed this in your original reply, but the columns I end up with are actually in the format of:

1 09/01/10 17:02:55 | O2 | U_10_O2.txt | 20.9
2 09/01/10 17:03:09 | O2 | U_11_O2.txt | 20.9
3 09/01/10 17:03:25 | O2 | U_11_O2.txt | 20.9
4 09/01/10 17:03:40 | O2 | U_11_O2.txt | 20.9
5 09/01/10 17:03:55 | O2 | U_12_O2.txt | 20.9
6 09/01/10 17:04:10 | O2 | U_12_O2.txt | 20.9
7 09/01/10 17:04:25 | O2 | U_13_O2.txt | 20.9

with the date and time in the same column. I tried sorting the data with the format you had in your original reply (with 2 separate columns for date and time) and it worked perfectly for Unit 1 on the first day showing all 700-ish readings from that unit for that day. I'm still running into a problem after that, however, where the only other pages generated are a blank page and a third page with all the remaining data and showing the year 1899 haha. So we're definitely on the home stretch! I'm going to put these files on the laptop and take them home tonight and see if I can tweak the code or the data to get it to work. But as aforementioned I'm pretty hopeless with this stuff so I may be back here tomorrow to pick your brain some more. Thanks again for all the effort! If I don't have any luck, would it be possible to attach a sample file to these posts to show you exactly what I'm looking at?

Report •

February 14, 2011 at 15:38:03
You can't attach a file, but if need be I can supply an email address via Private Message.

Try it on your own first. It'll be a good learning experience.

Size your VBA editor so that you can see your worksheet behind it.

Place your cursor anywhere in the code and press F8 to single step through the code. Obviously you can't do that for 50,000 rows x the number of lines in the code, but maybe a few time through the loops will give you a better idea of what is happening.

BTW...perhaps your Text To Column results are the issue. Are you sure that the feature is separating your data consistently for all the rows?

Or maybe your Units have different numbers of characters. The code is very specific as to how it parses the Unit description and is only based in the examples given.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

February 15, 2011 at 11:03:35
"Try it on your own first. It'll be a good learning experience."

Oh man I have to learn things? Noooooo!

I have to admit, this has been a lot of fun getting more familiar with macros (I'm dangerously close to revealing my inner geek aren't I?). I tried as you suggested to single step through the code to kind of see what each individual line was doing, but I'm still running into the same error I mentioned earlier where it will complete one sheet successfully then create 2 more sheets (one blank and one with all the remaining data with the year 1899 listed as the date).

If you don't mind me taking up even ore of your time I'd love to email you a sample spreadsheet to look at. Let me know and thanks again for your invaluable help!

Report •

Ask Question