Transpose??

  • Thread starter Thread starter Johan Snyder
  • Start date Start date
J

Johan Snyder

I have a lot of data in Column A.
They are in groups of 5 that must go together into 1 row.

This is how it looks now:
GUID
Player
Address
Type
Reason

I want it all arraged like this:

Column A all Guid's
Column B all Players
Column C all Address
Column D all Type
Column E all Reason

So that the first 5 rows in Column A are all moved to the 1st row and spread over 5 Columns



Please I'm totally at a lost and spend hours doing it manually.
 
Enter this in B1

=INDEX($A:$A,(ROWS($1:1)-1)*5+COLUMNS($A:B)-1)

Drag across to F1

Select B1:F1 and drag down as far as you need.

Gord
 
Briljiant....Thank you sooooo much. and it is a lot easier than VBA.

You'll need to delete colA after you copy/PasteValues if you want the
data in A:E as asked!

Actually, a VBA solution is as easy as this...


Sub TransposeData()
Dim vData, n&, j&, k&, r&
vData = ActiveSheet.UsedRange
ReDim vDataout(1 To UBound(vData) / 5, 1 To 5)
r = 1
For n = LBound(vData) To UBound(vData) Step 5
j = 1
For k = 0 To 4
vDataout(r, j) = vData(n + k, 1): j = j + 1
Next 'k
r = r + 1
Next 'n
ActiveSheet.UsedRange.ClearContents
Cells(1, 1).Resize(UBound(vDataout), UBound(vDataout, 2)) = vDataout
ActiveSheet.UsedRange.EntireColumn.AutoFit
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Thanks for the macro Gord, i'll use that as well. I love it because it goes unnoticed most of the time and there is no accidental wipe-outs of formulae.
 
I have a lot of data in Column A.
They are in groups of 5 that must go together into 1 row.

This is how it looks now:
GUID
Player
Address
Type
Reason

I want it all arraged like this:

Column A all Guid's
Column B all Players
Column C all Address
Column D all Type
Column E all Reason

So that the first 5 rows in Column A are all moved to the 1st row and spread over 5 Columns
Click to expand...

=TRANSPOSE(Sheet1!A1..Ennn) entered as an array formula with
Ctrl-Shift-Enter in Sheet2 columns A:E

You will need to put it on another page if you want the new version to
start in A1. Might want to hide the #N/A as well.
 
Back
Top