Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose or Macro?
Hello,
I have a set of data that i need to modify. A B C D E ......AA 14-Oct 03:23 04:23 06:20 06:34 I need to transpose B:AA into a single column while keeping the date in A. So it should look like A B 14-Oct 03:23 14-Oct 04:23 14-Oct 06:20 This data goes on for a number of rows so doing it manually (as i have been doing) can take hrs. Can anyone save me from my excel hell? Thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose or Macro?
Yes use the Transpose option to do it.
Just insert a column in between A&B Column. Now your data will start from C column and end on column AB. Now select the data from Column C to Column AB and do copy. Place the cursor in B Column (i.e. newly inserted column) and do Right ClickPaste Special€¦Check the Transpose Field. If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "Buyone" wrote: Hello, I have a set of data that i need to modify. A B C D E ......AA 14-Oct 03:23 04:23 06:20 06:34 I need to transpose B:AA into a single column while keeping the date in A. So it should look like A B 14-Oct 03:23 14-Oct 04:23 14-Oct 06:20 This data goes on for a number of rows so doing it manually (as i have been doing) can take hrs. Can anyone save me from my excel hell? Thanks in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose or Macro?
Suppose your data is on Sheet1 starting on row 1. Insert a new sheet,
and put these formulae in the cells stated: A1: =INDEX(Sheet1!A:A,INT((ROW(A1)-1)/26)+1) B1: =INDEX(Sheet1!B:AA,INT((ROW(A1)-1)/26)+1,MOD(ROW(A1)-1,26)+1) Copy them down as far as required. You could then fix the values and delete the original sheet if you don't need it anymore. Hope this helps. Pete On Nov 5, 10:39*am, Buyone wrote: Hello, I have a set of data that i need to modify. * * A * * * * * *B * * * * * * * C * * * * * * *D * * * * * * *E ......AA 14-Oct * * 03:23 * * * *04:23 * 06:20 * 06:34 I need to transpose B:AA into a single column while keeping the date in A.. So it should look like * A * * * * * *B * * 14-Oct * * 03:23 14-Oct * * 04:23 14-Oct * * 06:20 This data goes on for a number of rows so doing it manually (as i have been doing) can take hrs. Can anyone save me from my excel hell? Thanks in advance |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose or Macro?
Hello,
Unfortunately that's the way i've been doing it, and takes me ages as I have to do it for each row individually. The data i get through comes for a number of dates and the times are not a consistant number, typically they range from 1-18 times per day. So i need a way of automating the number of rows to match the columns and move the next set of data down automatically so it stays in date order. Apologies, that may not be the clearest explanation but it's the best I have. Thanks "Ms-Exl-Learner" wrote: Yes use the Transpose option to do it. Just insert a column in between A&B Column. Now your data will start from C column and end on column AB. Now select the data from Column C to Column AB and do copy. Place the cursor in B Column (i.e. newly inserted column) and do Right ClickPaste Special€¦Check the Transpose Field. If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "Buyone" wrote: Hello, I have a set of data that i need to modify. A B C D E ......AA 14-Oct 03:23 04:23 06:20 06:34 I need to transpose B:AA into a single column while keeping the date in A. So it should look like A B 14-Oct 03:23 14-Oct 04:23 14-Oct 06:20 This data goes on for a number of rows so doing it manually (as i have been doing) can take hrs. Can anyone save me from my excel hell? Thanks in advance |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose or Macro?
With you data in Sheet1 ColA to ColAA; try the below formulas
In Sheet2 cell A1 enter the below formula and copy/drag down as required =INDEX(Sheet1!A:A,(FLOOR(ROW(B1)-1,26)/26)+1) In Sheet2 cell B1 enter the below formula and copy/drag down as required =OFFSET(Sheet1!$B$1,(FLOOR(ROW(A1)-1,26)/26),MOD(ROW(A1)-1,26)) If this post helps click Yes --------------- Jacob Skaria "Buyone" wrote: Hello, Unfortunately that's the way i've been doing it, and takes me ages as I have to do it for each row individually. The data i get through comes for a number of dates and the times are not a consistant number, typically they range from 1-18 times per day. So i need a way of automating the number of rows to match the columns and move the next set of data down automatically so it stays in date order. Apologies, that may not be the clearest explanation but it's the best I have. Thanks "Ms-Exl-Learner" wrote: Yes use the Transpose option to do it. Just insert a column in between A&B Column. Now your data will start from C column and end on column AB. Now select the data from Column C to Column AB and do copy. Place the cursor in B Column (i.e. newly inserted column) and do Right ClickPaste Special€¦Check the Transpose Field. If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "Buyone" wrote: Hello, I have a set of data that i need to modify. A B C D E ......AA 14-Oct 03:23 04:23 06:20 06:34 I need to transpose B:AA into a single column while keeping the date in A. So it should look like A B 14-Oct 03:23 14-Oct 04:23 14-Oct 06:20 This data goes on for a number of rows so doing it manually (as i have been doing) can take hrs. Can anyone save me from my excel hell? Thanks in advance |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose or Macro?
Hello,
Partial success. It works, but only for one row. I need it to start on the next row when a cell is blank. Is this possible? Cheers "Jacob Skaria" wrote: With you data in Sheet1 ColA to ColAA; try the below formulas In Sheet2 cell A1 enter the below formula and copy/drag down as required =INDEX(Sheet1!A:A,(FLOOR(ROW(B1)-1,26)/26)+1) In Sheet2 cell B1 enter the below formula and copy/drag down as required =OFFSET(Sheet1!$B$1,(FLOOR(ROW(A1)-1,26)/26),MOD(ROW(A1)-1,26)) If this post helps click Yes --------------- Jacob Skaria "Buyone" wrote: Hello, Unfortunately that's the way i've been doing it, and takes me ages as I have to do it for each row individually. The data i get through comes for a number of dates and the times are not a consistant number, typically they range from 1-18 times per day. So i need a way of automating the number of rows to match the columns and move the next set of data down automatically so it stays in date order. Apologies, that may not be the clearest explanation but it's the best I have. Thanks "Ms-Exl-Learner" wrote: Yes use the Transpose option to do it. Just insert a column in between A&B Column. Now your data will start from C column and end on column AB. Now select the data from Column C to Column AB and do copy. Place the cursor in B Column (i.e. newly inserted column) and do Right ClickPaste Special€¦Check the Transpose Field. If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "Buyone" wrote: Hello, I have a set of data that i need to modify. A B C D E ......AA 14-Oct 03:23 04:23 06:20 06:34 I need to transpose B:AA into a single column while keeping the date in A. So it should look like A B 14-Oct 03:23 14-Oct 04:23 14-Oct 06:20 This data goes on for a number of rows so doing it manually (as i have been doing) can take hrs. Can anyone save me from my excel hell? Thanks in advance |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose or Macro?
Excel 2007 Pivot Table
Pivot array to column. No formulas, no code. http://www.mediafire.com/file/mmtm22td3m2/11_05_09.xlsx |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose or Macro?
Sub ReOrganize()
'JBeaucaire (11/4/2009) 'Turns row data into columnar data Dim LR As Long, i As Long, r As Long, c As Long, v As Long Application.ScreenUpdating = False LR = Range("A" & Rows.Count).End(xlUp).Row i = 1 Do Until Range("A" & i) = "" If Range("C" & i) < "" Then c = Cells(i, Columns.Count).End(xlToLeft).Column v = i For r = 3 To c i = i + 1 Rows(i).Insert xlShiftDown Range("A" & i) = Range("A" & i - 1) Range("B" & i) = Cells(v, r) Next r End If i = i + 1 Loop Range("C1", Cells(Rows.Count, Columns.Count)).ClearContents Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP On Thu, 5 Nov 2009 02:39:07 -0800, Buyone wrote: Hello, I have a set of data that i need to modify. A B C D E ......AA 14-Oct 03:23 04:23 06:20 06:34 I need to transpose B:AA into a single column while keeping the date in A. So it should look like A B 14-Oct 03:23 14-Oct 04:23 14-Oct 06:20 This data goes on for a number of rows so doing it manually (as i have been doing) can take hrs. Can anyone save me from my excel hell? Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I group and transpose data - macro help needed. | Excel Worksheet Functions | |||
Do I need a Macro to do Transpose in this case? | Excel Discussion (Misc queries) | |||
I need a macro to transpose multiple columns A1-Z1, A2-X2 etc | Excel Discussion (Misc queries) | |||
Transpose Macro | Excel Worksheet Functions | |||
Transpose date macro | Excel Worksheet Functions |