Parsing Out Excel Data to a new Worksheet

**outthere** · 08-25-2009, 12:29 PM

OK, so I am starting to realize that what I am trying to do may be a bit over my head. I will describe it below and if anyone can help that would be great.

I have two worksheets "Gary Breakout" and "Macro List" in a workbook.

To help understand what I'm trying to do lets say "Gary Breakout" contains a list of names of students and there 100 meter sprint times. The students names are found in Column "C" There first 100m time is found in column "D" and there additional times are found in columns "F:S"

Some students have recorded up to 15 times, other students only 1 or 2. I am trying to create a macro the will copy the students name and there time(s) to a new worksheet. Of course there is the added issue of not all of them recording 15 different times, so some students will take up only 1 row in the new sheet, others 15.

In Column "B" I added a counta formula to count the number of times they have recorded a time "=COUNTA(E2:S2)"

With limited VBA knowledge I am trying to create a macro that will loop through all rows in "Gary Breakout" and based on the value in column "B" insert the appropriate number of rows into "Macro List" and copy the students name into column "A" of "Macro List" and there time(s) into column "B"

I hope this all make sense. If anyone can help it would be greatly appreciated.

**StephenR** · 08-25-2009, 12:38 PM

I suggest you attach a sample workbook, including desired results.

**outthere** · 08-25-2009, 01:23 PM

Attached is the sample sheet. The actual data I am dealing with is not a time value so I just put random letters in as the data. The worksheet titled "RESULTS NEEDED" is what I need to see in "Macro List"

Thanks again for the help.

**TonyS** · 08-25-2009, 03:10 PM

Hi,
Try this

Public Sub Test()
Dim lr
Dim i
Dim j
Dim rPtr
rPtr = 2
lr = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lr
    For j = 1 To Cells(i, 2)
        Sheets("Macro List").Cells(rPtr, 1) = Cells(i, 3)
        If j = 1 Then
            Sheets("Macro List").Cells(rPtr, 2) = Right(Cells(i, 4 + j), Len(Cells(i, 4 + j)) - Len(Cells(i, 3)) - 1)
        Else
            Sheets("Macro List").Cells(rPtr, 2) = Cells(i, 4 + j)
        End If
        rPtr = rPtr + 1
    Next j
Next i
End Sub

Tony

**outthere** · 08-25-2009, 04:08 PM

Wow, thanks Tony.

I had to remove the "-1" from the end of:

            Sheets("Macro List").Cells(rPtr, 2) = Right(Cells(i, 4 + j), Len(Cells(i, 4 + j)) - Len(Cells(i, 3)))

As it was dropping the first character of the data that was needed but other than that it works great. Thank you so much!!

I did notice that i only works if i run it from the "Gary Breakout" worksheet. Is there a way to change that? no big deal if not.

Thanks again!

**TonyS** · 08-25-2009, 05:15 PM

Hi,
You're welcome.
Just add the following line to the beginning of the sub

Sheets("Gary Breakout").Select

Tony

**JBeaucaire** · 08-25-2009, 06:34 PM

Gentlemen, a tip of the day to keep things operating optimally. Whenever you declare a variable without a category, Excel defaults to the most expensive category of all, VARIANT. Mostly, I suppose that's fine, but as a general rule you want to qualify your variables to the least expensive and accurate category.

For loops, rows, columns, the safest choice is LONG as it only allows whole numbers and up to 2.4 million of them in Excel 2003, so plenty of room without teh expense of VARIANT. So declare them fully at the top like so:

Public Sub Test()
Dim lr as Long, i as Long, j as Long, rPtr as Long

...etc...

========
OutThere, if that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

Also, be sure to fill out your profile so people will be able to see what version of Excel you're using without having to ask all the time.

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

**outthere** · 08-26-2009, 11:31 AM

Thank-you both so much for the help. I will change the post to solved and of course I added to both your reputations

Gary

Parsing Out Excel Data to a new Worksheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Parsing Out Excel Data to a new Worksheet

Re: Parsing Out Excel Data to a new Worksheet

Re: Parsing Out Excel Data to a new Worksheet

Re: Parsing Out Excel Data to a new Worksheet

Re: Parsing Out Excel Data to a new Worksheet

Re: Parsing Out Excel Data to a new Worksheet

Re: Parsing Out Excel Data to a new Worksheet

Re: Parsing Out Excel Data to a new Worksheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions