Transpose or Macro?

B

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
 
M

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!
 
P

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
 
B

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
 
J

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
 
J

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
 
B

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top