Transpose or Macro?

[Buyone]

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

 

[Ms-Exl-Learner]

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 Click>Paste Special…>Check the Transpose Field.

If this post helps, Click Yes!

 

[Pete_UK]

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

 

[Buyone]

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

 

[joel]

Sub FixDates()


With Sheets("Sheet1")
RowCount = 1
Do While .Range("A" & RowCount) <> ""
LastRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1

MyDate = .Range("A" & RowCount)
Set LastCol = .Cells(RowCount, Columns.Count).End(xlToLeft)
Set CopyRange = .Range(.Range("B" & RowCount), LastCol)
CopyRange.Copy
Sheets("Sheet2").Range("B" & NewRow).PasteSpecia
Transpose:=True
LastRow = Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row
Sheets("Sheet2").Range("A" & NewRow & ":A" & LastRow) = MyDate
Sheets("Sheet2").Range("A" & NewRow & ":A" & LastRow) _
.NumberFormat = "DD-MMM"
RowCount = RowCount + 1
Loop
End With

End Su

 

[Jacob Skaria]

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

 

[Buyone]

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

 

[joel]

I don't think the worksheet functions are going to work. Try my macro.

 

[Herbert Seidenberg]

Excel 2007 Pivot Table
Pivot array to column.
No formulas, no code.
http://www.mediafire.com/file/mmtm22td3m2/11_05_09.xlsx

 

[Gord Dibben]

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