transpose

[AbrahamGluck]

How can i use the transpose function from rows to columns, but should skip every 2 columns and fill in the third, (or is there a better way to do it with an other formula?)


exp. for the rows looks like


Apple
Orange
Peach

exp. for the columns should look like these


Apple blank blank Orange blank blank Peach

 

[Joe4]

You will probably need Excel formulas or VBA (don't think you can tell Transpose to do that).

Are you copying the data to the same sheet or a different sheet?
Where does the data initially reside (rows/columns) and where do you want it pasted (sheets/rows/columns)?

 

[alansidman]

Assume you wish data on sheet1 transposed to sheet2 row1. This VBA solution may work for you

Option Explicit


Sub skip2()
Dim lr As Long, i As Long
Dim lc As Long
Dim w1 As Worksheet, w2 As Worksheet
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
lr = w1.Range("A" & Rows.Count).End(xlUp).Row




Application.ScreenUpdating = False
w1.Range("A1").Copy w2.Range("A1")
For i = 2 To lr
lc = w2.Cells(1, Columns.Count).End(xlToLeft).Column
w1.Range("A" & i).Copy w2.Cells(1, lc + 3)
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True




End Sub

 

[AbrahamGluck]

You will probably need Excel formulas or VBA (don't think you can tell Transpose to do that).

Are you copying the data to the same sheet or a different sheet?
Where does the data initially reside (rows/columns) and where do you want it pasted (sheets/rows/columns)?

Thanks so much for you reply
i want to take the information from column A5:A from sheet 1 and put it to columns in sheet 2 every third column

 

[AbrahamGluck]

Assume you wish data on sheet1 transposed to sheet2 row1. This VBA solution may work for you

Option Explicit


Sub skip2()
Dim lr As Long, i As Long
Dim lc As Long
Dim w1 As Worksheet, w2 As Worksheet
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
lr = w1.Range("A" & Rows.Count).End(xlUp).Row




Application.ScreenUpdating = False
w1.Range("A1").Copy w2.Range("A1")
For i = 2 To lr
lc = w2.Cells(1, Columns.Count).End(xlToLeft).Column
w1.Range("A" & i).Copy w2.Cells(1, lc + 3)
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True




End Sub

Thank you Alensiman
I'll try to use it and let you know
I'm using Google spreadsheet I don't ever use this code I'll try to figure out how to play with it

thanks

 

[alansidman]

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

 

[Joe4]

I'm using Google spreadsheet I don't ever use this code I'll try to figure out how to play with it

Please be sure to mention that up-front. That makes a big difference!
I am moving this question to the "Other Questions" forum since it is not really an Excel Question.

 

[AbrahamGluck]

Thank you Alensiman
I'll try to use it and let you know
I'm using Google spreadsheet I don't ever use this code I'll try to figure out how to play with it

thanks

Thank you very much for your time and help
I'm using Google spreadsheet and i have difficulty to convert your VBA formula to Java
VBA to JavaScript Translation Tool
are you familiar how to do that in Java please?

 

[alansidman]

It is not an area of expertise for me. I don't use Google Spreadsheets nor Java. Hopefully, someone else in this forum does. You may wish to address this issue within a Google Spreadsheet forum and ask for a conversion from VBA to Google Script. Here is a link that you may find helpful

From VBA to Google Apps Script - Desktop Liberation