I have copied content from a source that has resulted in the following formatting in a spreadsheet:

Title
Description
Blank Line
Title
Description
Blank Line

I need to get the data formatted like this:

Title Description
Title Description

Is there an easy way to get this done?

Thanks in advance for any advice!!

Hi,

Am Tue, 17 Jun 2014 16:23:40 +0100 schrieb TimR1316:

Title
Description
Blank Line
Title
Description
Blank Line

your data in Sheet1. then in Sheet2 A1:
=INDEX(Sheet1!$A:$A,(ROWS($1:1)-1)*3+1)
and in B1:
=INDEX(Sheet1!$A:$A,(ROWS($1:1)-1)*3+2)
Copy both formulas down till you get zeros


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thanks, Claus! That definitely works. I failed to mention that the title was hyperlinked. Sorry about that oversight. When I copy the title to Sheet2, the hyperlink doesn't copy. Is there a way to modify this so that they hyperlink will copy?

I'll brush up on the index function so I can try to understand exactly what this formula is doing!!

Thanks!

hi Tim,

Am Tue, 17 Jun 2014 18:51:27 +0100 schrieb TimR1316:

Thanks, Claus! That definitely works. I failed to mention that the title
was hyperlinked. Sorry about that oversight. When I copy the title to
Sheet2, the hyperlink doesn't copy. Is there a way to modify this so
that they hyperlink will copy?

put the formula into HYPERLINK formula:
=HYPERLINK(INDEX(Sheet1!$A:$A,(ROWS($1:1)-1)*3+1))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi again,

Am Tue, 17 Jun 2014 18:51:27 +0100 schrieb TimR1316:

Thanks, Claus! That definitely works. I failed to mention that the title
was hyperlinked. Sorry about that oversight. When I copy the title to
Sheet2, the hyperlink doesn't copy. Is there a way to modify this so
that they hyperlink will copy?

or try it with VBA:

Sub Transpose()
Dim LRow As Long
Dim i As Long, j As Long

j = 1
With Sheets("Sheet1")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LRow Step 3
.Cells(i, 1).Copy Sheets("Sheet2").Cells(j, 1)
.Cells(i + 1, 1).Copy Sheets("Sheet2").Cells(j, 2)
j = j + 1
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Claus - the hyperlink= option didn't work for me. The VBA script works like a charm. I have no idea what it's doing - but it works like a charm. Thanks again!!