Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose??
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose??
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 On Sun, 13 Jul 2014 16:26:48 -0700 (PDT), Johan Snyder wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose??
Briljiant....Thank you sooooo much. and it is a lot easier than VBA.
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose??
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose??
Thanks for the feedback.
Gord On Sun, 13 Jul 2014 17:36:51 -0700 (PDT), Johan Snyder wrote: Briljiant....Thank you sooooo much. and it is a lot easier than VBA. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose??
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.
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose??
On 14/07/2014 00:26, Johan Snyder wrote:
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 =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. Please I'm totally at a lost and spend hours doing it manually. -- Regards, Martin Brown |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose code has ceased to transpose | Excel Programming | |||
Transpose a col to a row? | Excel Discussion (Misc queries) | |||
Transpose Row by Row | Excel Programming | |||
transpose | Excel Worksheet Functions | |||
I WANT TO TRANSPOSE LINKS, AS WE TRANSPOSE VALUES | Excel Worksheet Functions |