hey guys, i cant seem to figure out how to re-arrange my data automatically.
Currently i pull data from a DB to analysis and need to rearrage it to read it and make an analysis.
This is how the data is outputted.
Column A Has the name of server and a date
Column B has a data as well
Column C has the Value (a number) which corresponds with the Date
there are multiple servers. Once the data is pulled for the one server it will make a space then input the next server under the first. data reaches thousands of lines. What i want to do is take each server with its data and place it side by side with a column inbetween each server.
Server 1 Server 2 etc.,..
Data data data Data Data Data etc...
Is there a way to do this without doing it manually? If anyone could help that would be great. Thanks
Last edited by mikes88; 01-13-2012 at 10:12 AM.
It will be good if you attach a sample workbook so we can understand your data better.
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Example:
Column A - Column B - Column C
Server 1
Jan 13 2011 - Jan 13 2011 - 44
Jan 13 2011 - Jan 13 2011 - 44
Jan 13 2011 - Jan 13 2011 - 44
Jan 13 2011 - Jan 13 2011 - 44
Server 2
Jan 13 2011 - Jan 13 2011 - 44
Jan 13 2011 - Jan 13 2011 - 44
Jan 13 2011 - Jan 13 2011 - 44
Jan 13 2011 - Jan 13 2011 - 44
That is what it looks like but with thousands of more lines. Here is what i would like to have it look like.
Column A - Column B - Column C - Column D - Column E - Column F - Column G
Server 1 - - - - Server 2
Jan 13 2012 - Jan 13 2012 - 44 - Empty - Jan 13 2012 - Jan 13 2012 - 44
Jan 13 2012 - Jan 13 2012 - 44 - Empty - Jan 13 2012 - Jan 13 2012 - 44
Jan 13 2012 - Jan 13 2012 - 44 - Empty - Jan 13 2012 - Jan 13 2012 - 44
Jan 13 2012 - Jan 13 2012 - 44 - Empty - Jan 13 2012 - Jan 13 2012 - 44
and so forth.
For some reason its not keeping the spaces when i reply.
Last edited by mikes88; 01-13-2012 at 11:14 AM.
attached file
So far, what has been the maximum number of servers that you have in the file? Is there a chance of the data crossing the maximum number of columns we have in excel 2003?
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
there are only about 20 servers. I have manually placed them across no problem but its very time consuming. There are enough columns.
You can use this code -Option Explicit Dim lrow As Long Dim lcol As Long Dim i As Long Dim strow As Long Sub arrange_data() lrow = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row lcol = 4 strow = Range("A1").End(xlDown).Row + 2 For i = strow To lrow If Worksheets(1).Range("A" & i).Value Like "Server*" And i <> 1 Then strow = i ElseIf Worksheets(1).Range("A" & i + 1).Value = "" Then Range("A" & strow & ":C" & i).Cut Cells(1, lcol + 1) lcol = lcol + 4 End If Next i End Sub
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
I placed that code into a macro but it doesnt seem to be doing anything.
This is whats in the macro:
Option Explicit Dim lrow As Long Dim lcol As Long Dim i As Long Dim strow As Long Sub arrange_data() lrow = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row lcol = 4 strow = Range("A1").End(xlDown).Row + 2 For i = strow To lrow If Worksheets(1).Range("A" & i).Value Like "Server*" And i <> 1 Then strow = i ElseIf Worksheets(1).Range("A" & i + 1).Value = "" Then Range("A" & strow & ":C" & i).Cut Cells(1, lcol + 1) lcol = lcol + 4 End If Next i End Sub
You need to come back to your excel page. Go to View -> Macros and run the macro named arrange_data.
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
macro wasnt under view.
Had to goto
tools -> macro -> macros
Macros window came up with arrange_data in ther and i hti run. Nothing happened. will it not work for 2003?
It will surely work for 2003. Where have you copied the code? You should not copy the code in the worksheet code window. Instead, click on Alt+F11 (Function key 11) on your keyboard. You will get a blank code window on the right. On the left side, right click on Microsoft Excel Objects and Insert -> Module. Copy the code here.
Then go to Tools -> Macro -> Macros and run it. (Sorry i didnt realise you were using 2003).
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
I also made a mistake as well. I have to code in there. just realized that the way the data is pulled, columns a and b even with that space, some how thinks there isnt. But if i go into column C and to a ctrl + down, i get to teh end of that server section. If i do ctrl + down i goto the last line with data.
so really i need to run backwards from Column C to A and then do the cut and paste.
Hi,
In macros I know how to record macros but I am interested in learning to write VBA codes. Would you please guide me on this ?. I wanted to know basic things on how to write code, like for what Dim stands for.
It would be very much helpful to me
Regards,
Shweta
This is the 2nd time.....
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks