Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Macro to Reorder Data - tough one
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Macro to Reorder Data - tough one
Kesey 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). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Macro to Reorder Data - tough one
Posted the example file here for ppl on usenet: http://www.nodrm.com/rawdata.zip -- Kesey ------------------------------------------------------------------------ Kesey's Profile: http://www.excelforum.com/member.php...o&userid=36402 View this thread: http://www.excelforum.com/showthread...hreadid=561796 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Macro to Reorder Data - tough one
On Sat, 15 Jul 2006 23:07:18 -0400, Kesey 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Macro to Reorder Data - tough one
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 ------------------------------------------------------------------------ Kesey's Profile: http://www.excelforum.com/member.php...o&userid=36402 View this thread: http://www.excelforum.com/showthread...hreadid=561796 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Macro to Reorder Data - tough one
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do u reorder data in a column so last cell entry is first? | Excel Discussion (Misc queries) | |||
How to reorder data points on a graph? | Charts and Charting in Excel | |||
Tough question moving data (may require macro?) | Excel Worksheet Functions | |||
reorder data | Excel Worksheet Functions | |||
is there anyway in an excel macro to reorder the sheet tabs from left to right? | Excel Worksheet Functions |