$$Excel-Macros$$ Cut Data from Rows and Paste into Columns
I have a series of spreadsheets that have data in them that needs to be formatted into 3 columns instead of one column. The data in Row 1 needs to go in row 1, column 1 Row 2 needs to go in row 1, column 2 Row 3 = Row 1, Col 3 Row 4 = Row 2, Col 1 Row 5 = Row 2, Col 2 Row 6 = Row 2 Col 3 Row 7 = Row 3, Col 1, etc to the end of the file. What would be the macro that I need to use? I have been tasked with loading this data into a program but am unsure how to get it into the correct format. Any help would be greatly appreciated. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Re: Cut Data from Rows and Paste into Columns
Transpose would not work because every 4th record (or row) would need to go back to the first column, next row. original data could be in either an excel spreadsheet or a text file, whichever works best. thus 1 2 3 4 5 6 7 8 9 would look like 1 2 3 4 5 6 7 8 9 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Reorder last and first names
I have a column that has the data formatted like this: (This is a different column than previous post) Smith, John M Smith, Alex Jones, Mary Jackson Harris, Greg P Diaz-Garcia, Brenda Maria T etc I want it to look like this (Note these are separate columns and those having two last names have no spaces instead of a space of hyphen, I can fix this in find and replace if needed) John Smith M Alex Smith Mary Jones Greg JacksonHarris P Brenda DiazGarcia Maria T Thanks you for any help. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Re: Cut Data from Rows and Paste into Columns
This worked like a charm. Thank you.
On Aug 26, 6:53 am, ashish koul wrote:
> Sub test()
> Dim i, interval, j As Long
> interval = 3
> j = 2
> For i = 1 To Sheets(1).Range("a1").End(xlDown).Row
>
> Sheets(1).Range("A" & i & ":A" & i + interval - 1).Copy
>
> Range("D" & j).Select
> Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
> SkipBlanks:= _
> False, Transpose:=True
>
> i = i + interval - 1
> j = j + 1
> Next
>
> End Sub
>
> On Fri, Aug 26, 2011 at 7:15 PM, GreenBriar
> wrote:
>
>
>
> > Transpose would not work because every 4th record (or row) would need to go
> > back to the first column, next row.
>
> > original data could be in either an excel spreadsheet or a text file,
> > whichever works best.
>
> > thus
>
> > 1
> > 2
> > 3
> > 4
> > 5
> > 6
> > 7
> > 8
> > 9
>
> > would look like
>
> > 1 2 3
> > 4 5 6
> > 7 8 9
>
> > --
>
> > --
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to excel-macros@googlegroups.com
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > Like our page on facebook , Just follow below link
> >http://www.facebook.com/discussexcel
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *akoul*.*blogspot*.com <http://akoul.blogspot.com/>http://akoul.posterous.com/
> *akoul*.wordpress.com <http://akoul.wordpress.com/>
> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
>
> P Before printing, think about the environment.
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
$$Excel-Macros$$ $$Reformatting First, Last and middle names$$
I have the following code which splits data from Column A so that
column B has last name(s) and column C has first names and middle
initial. How would I further code in excel to go only to Column C and
put everything from the space on into a new column. Once done, I would
want to delete all spaces and dashes in the worksheet and concatenate
Columns B, C, and G.
Smith,John A SmithJohn A
Jones,Henry Peter JonesHenry Peter
Jackson-Smith,Emily Marie Jackson-SmithEmily Marie
Martinez Diaz,Juan Martinez DiazJuan
Davies,David DaviesDavid
Sub ReverseAndSplit3()
Dim LastRow As Long
Application.DisplayAlerts = False
With Columns(1)
LastRow = .Cells(65536, 1).End(xlUp).Row
.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False,
_
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1))
End With
Application.DisplayAlerts = True
End Sub
Thanks for any and all help
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
