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.

[Don Wiss]

Kesey <[email protected]> wrote:

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

On Sat, 15 Jul 2006 23:07:18 -0400, Kesey <[email protected]> wrote:

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

"Kesey" <[email protected]> wrote in
message news:[email protected]...
>
> 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
> ------------------------------------------------------------------------
> Kesey's Profile:
http://www.excelforum.com/member.php...o&userid=36402
> View this thread: http://www.excelforum.com/showthread...hreadid=561796
>