Transpose + Skip Columns

[laurawqf]

Hello!

I would like to transpose a column of data from one sheet to the row of a second sheet, but have the data skip every 4th column.

So from 'Sheet1' I'd like to transpose E2:E325 to 'Sheet2" starting at N4, then R4, V4, Z4, etc.

I can get the transpose function to work just fine, but I cannot figure out how to skip columns in the target sheet.

Thanks!

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG06Sep36
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
c = 14
[COLOR="Navy"]Set[/COLOR] Rng = Sheets("Sheet1").Range("E2:E325")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Sheets("Sheet2").Cells(4, c).Value = Dn.Value
    c = c + 4
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Rick Rothstein]

Assuming no data on Row 4, Sheet2 starting at Column N, give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub TransposeAndSkip()
  Dim Arr As Variant
  Arr = Split(Join([TRANSPOSE(Sheet1!E2:E6)], Chr(1) & Chr(1) & Chr(1)) & Chr(1), Chr(1))
  Sheets("Sheet2").Range("N4").Resize(, UBound(Arr) + 1) = Arr
End Sub[/td]
[/tr]
[/table]

 

[laurawqf]

Assuming no data on Row 4, Sheet2 starting at Column N, give this macro a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub TransposeAndSkip()
  Dim Arr As Variant
  Arr = Split(Join([TRANSPOSE(Sheet1!E2:E6)], Chr(1) & Chr(1) & Chr(1)) & Chr(1), Chr(1))
  Sheets("Sheet2").Range("N4").Resize(, UBound(Arr) + 1) = Arr
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

This worked great thank you!

I'm a bit scared to ask on an excel forum, but my team informed me they need this data re-done in google sheets. I don't suppose you'd be able to write the same macro in google apps script/javascript?

 

[Rick Rothstein]

This worked great thank you!

You are welcome.

I'm a bit scared to ask on an excel forum, but my team informed me they need this data re-done in google sheets. I don't suppose you'd be able to write the same macro in google apps script/javascript?

Sorry, but I know absolutely nothing about Google Sheets... never used it. Maybe someone who knows about it will visit this thread and help you out.