Re: $$Excel-Macros$$ Re: Cut Data from Rows and Paste into Columns
without macro solution Assuming 1,2,3,4,5 etc is stored in A1:A300 Put this formula in D1 *=OFFSET($A$1,ROW(A1)*3-(4-COLUMN(A1)),0)* copy right down 3 columns x 100 rows best regards, ~ siti On Fri, Aug 26, 2011 at 8:45 PM, GreenBriar impedimenta.st...@gmail.comwrote: 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 ctv_wrapped every 3 columns.xls Description: MS-Excel spreadsheet
$$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
Re: $$Excel-Macros$$ Re: Cut Data from Rows and Paste into Columns
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 impedimenta.st...@gmail.comwrote: 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 -- *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
Re: $$Excel-Macros$$ Re: Cut Data from Rows and Paste into Columns
alos try this link http://www.vbaexpress.com/kb/getarticle.php?kb_id=144 On Fri, Aug 26, 2011 at 7:15 PM, GreenBriar impedimenta.st...@gmail.comwrote: 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 -- . -- -- 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 koul.ash...@gmail.com 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 impedimenta.st...@gmail.comwrote: 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
Re: $$Excel-Macros$$ Re: Cut Data from Rows and Paste into Columns
Could have been more efficient without selections or incrementing i as in my offering I previously sent Option Explicit Sub transposerows() Dim i As Long For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row Step 3 Cells(i, 1).Resize(3).Copy Cells(i, 2).PasteSpecial Transpose:=True Next i Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete Columns(1).Delete End Sub -Original Message- From: GreenBriar Sent: Friday, August 26, 2011 12:59 PM To: MS EXCEL AND VBA MACROS Subject: 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 koul.ash...@gmail.com 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 impedimenta.st...@gmail.comwrote: 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 -- -- 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
Try this Option Base 1 Sub DoSomething() Dim SrcRng As Range Dim OptRng As Range Dim arr() As Variant Dim r As Integer Dim c As Integer Dim k As Integer Set SrcRng = Application.InputBox(Please Select the Source Range, , , , , , , 8) Set OptRng = Application.InputBox(Please Select the OutPut Cell, , , , , , , 8) r = (SrcRng.Rows.Count / 3) + 1 c = 3 ReDim arr(r, c) k = 1 For i = 1 To r For j = 1 To c arr(i, j) = SrcRng.Cells(k, 1).Value k = k + 1 Next Next OptRng.Resize(r, c).Select Selection.Value = arr End Sub From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of GreenBriar Sent: Friday, August 26, 2011 7:16 PM To: excel-macros@googlegroups.com Subject: $$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 -- -- 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
