Using a Macro to Reorder Data - tough one

[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 |
+-------------------------------------------------------------------+

 

[Don Wiss]

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'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 |

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).

 

[Kesey]

Posted the example file here for ppl on usenet:
http://www.nodrm.com/rawdata.zip

 

[Don Wiss]

Posted the example file here for ppl on usenet:
http://www.nodrm.com/rawdata.zip

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

 

[Kesey]

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!

 

[NickHK]

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.