Using a Macro to Reorder Data - tough one

  • Thread starter Thread starter Kesey
  • Start date Start date
K

Kesey

Having a tough time with this one. I get data from an SQL query in one
format, and I need the data sorted differently for use in excel. I end
up taking the raw data and I manually reorder it to suit my needs. I
have some VBA experience and can follow/modify code, I'm just not sure
where to start on this one.

Here's my question. I have the data in tab1 and I need it in the
format that is shown on tab2. (See attachment)

I've tried using a pivot table and that got me really close, but it was
a little cumbersome. I'm thinking I should be able to read the data
into an array and then loop through it to spit it out in the format I
want it in, but I'm not sure where to start. I am not the end user, so
I want the user to just be able to run the macro and not have to do any
other manipulation (that's where the pivot table failed).

Any thoughts? Thanks in advance.


+-------------------------------------------------------------------+
|Filename: rawdata.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5036 |
+-------------------------------------------------------------------+
 
Kesey said:
Here's my question. I have the data in tab1 and I need it in the
format that is shown on tab2. (See attachment)
Click to expand...

I'm reading this on Usenet, and this being a text only newsgroup all
attachments are striped out of the news feed.
|Filename: rawdata.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5036 |
Click to expand...

I tried that link and I get "Invalid Attachment specified." on the web page
that comes up.

If you have something to show us, I think best to use Alt-Print Screen and
paste it into Microsoft Paint, save as a jpg, and then toss up on the web
site your ISP gave you. Or some other site.

Don <www.donwiss.com> (e-mail link at home page bottom).
 
Posted the example file here for ppl on usenet:
http://www.nodrm.com/rawdata.zip
Click to expand...

Here's a real quick macro. It's late. I'm sure if I thought about it I
could simplify it some. Don.

Sub ReArrange()

Dim LastOutRow As Integer, SourceRow As Integer, i As Integer
Application.ScreenUpdating = False

LastOutRow = 2
MoveOne 2, LastOutRow

For SourceRow = 3 To Sheets("tab1").Range("A50000").End(xlUp).Row
For i = 2 To LastOutRow
If Sheets("tab3").Cells(i, 1).Value = Sheets("tab1").Cells(SourceRow, 5).Value And Sheets("tab3").Cells(i, 2).Value = Sheets("tab1").Cells(SourceRow, 4).Value Then
MoveOne SourceRow, i
GoTo NextRow
End If
Next i
LastOutRow = LastOutRow + 1
MoveOne SourceRow, LastOutRow
NextRow:
Next SourceRow

End Sub

Sub MoveOne(SourceRow As Integer, OutRow As Integer)
Dim C As Integer
Sheets("tab3").Cells(OutRow, 1).Value = Sheets("tab1").Cells(SourceRow, 5).Value
Sheets("tab3").Cells(OutRow, 2).Value = Sheets("tab1").Cells(SourceRow, 4).Value
C = 2 + Month(Sheets("tab1").Cells(SourceRow, 2).Value)
Sheets("tab3").Cells(OutRow, C).Value = Sheets("tab3").Cells(OutRow, C).Value + Sheets("tab1").Cells(SourceRow, 1).Value
End Sub
 
Don - ran the macro and with the given data it works great. Years are
ascending instead of descending, but nothing a simple sort cannot fix.

I'm going to have to read through the code now and see how this thing
works. Thanks for your time!
 
Kesey,
I'm no expert at pivot tables, but I managed to get the desired results *
quickly.
It probably helps to format the startDate column as data (mmmm) to get month
names.
Then follow the pivot table wizard.

NickHK
* I assume there are a couple of errors in you desired results for 2006/45.
Otherwise I do not understand your requirements.
 
Back
Top