Not exactly a transpose problem

Hi,

I have a table that consists of just one column, and consists of data of the sort:

Forename
Surname
Telephone
<space>
Forename
Surname
Telephone
<space>

What I would like is three column headings, with the data under them, i.e
Forename Surname Telephone
Fred Smith 01243-78935
John Brown 01229-321546

Is there an easy way to accomplish this?

Wibs

Not Exacltly a Transpose Solution
but one that I think will work for you and is relatively easy to set up.

Make sure that your first Forename entry starts on row 2 - if it's on row 1
now, just insert a new row.

Assuming data is in column A and we can use columns B, C, D and E.

Put your column headings in B1, C1, D1 and -3 in E1

Formula for B2: =OFFSET(A1,ROW(B2)-ROW(A1)+E2,0)
Formula for C2: =OFFSET(A1,ROW(B2)-ROW(A1)+1+E2,0)
Formula for D2: =OFFSET(A1,ROW(B2)-ROW(A1)+2+E2,0)
Formula for E2: =E1+3

Now simply select those 4 cells (B2:E2) and drag down the page until all of
your data has been moved.

To get rid of the need to keep the original data:
Select all of the new entries in columns B, C and D and [ctr]+[c] copy them
and then use Edit | Paste Special and choose the [Values] option to get rid
of the formulas and replace them with the actual data. You can now delete
columns A and E since they aren't needed any longer.

"Wibs" wrote:

>
> Hi,
>
> I have a table that consists of just one column, and consists of data
> of the sort:
>
> Forename
> Surname
> Telephone
> <space>
> Forename
> Surname
> Telephone
> <space>
>
> What I would like is three column headings, with the data under them,
> i.e
> Forename Surname Telephone
> Fred Smith 01243-78935
> John Brown 01229-321546
>
> Is there an easy way to accomplish this?
>
> Wibs
>
>
> --
> Wibs
> ------------------------------------------------------------------------
> Wibs's Profile: http://www.excelforum.com/member.php...o&userid=28922
> View this thread: http://www.excelforum.com/showthread...hreadid=551982
>
>

Having done something similar recently, I modifed the code to fit your
request. It test drove just fine. The macro assumes that the data is in
column A, starting at row one, and is located on Sheet1.

Sub ParseNames()

'==============================================================
' wb = current workbook
' ws = Sheet1
' intPostRow is the row that is receiving the data
' intParseRow is the row that contains the name Or phone
' intColOffset is the number of columns to the right of A
' is receiving the data
' intBlankCounter is the loop control. If 10 blank rows
' in a row are encountered the loop is exited
' strVal is the value of the cell in column A being
' evaluated
' intvalLen is the length or the strVal value, if 0
' a blank row has been encountered and the
' intBlankCounter is incremented by 1
'==============================================================

Dim wb As Workbook
Dim ws As Worksheet
Dim intPostRow
Dim intParseRow
Dim intColOffset As Integer
Dim intBlankCounter
Dim strVal As String
Dim intValLen As Integer

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Sheet1")

ws.Activate
Range("A1").Select

Do Until intBlankCounter = 10
strVal = ActiveCell.Offset(intParseRow).Value
intValLen = Len(strVal)
If intValLen = 0 Then
intBlankCounter = intBlankCounter + 1
intParseRow = intParseRow + 1
Else
intColOffset = intColOffset + 1
intBlankCounter = 0
ActiveCell.Offset(intPostRow, intColOffset).Value = strVal
intParseRow = intParseRow + 1
If intColOffset = 3 Then
intColOffset = 0
intPostRow = intPostRow + 1
End If
End If
Loop

Set wb = Nothing
Set ws = Nothing

End Sub
--
Kevin Backmann


"Wibs" wrote:

>
> Hi,
>
> I have a table that consists of just one column, and consists of data
> of the sort:
>
> Forename
> Surname
> Telephone
> <space>
> Forename
> Surname
> Telephone
> <space>
>
> What I would like is three column headings, with the data under them,
> i.e
> Forename Surname Telephone
> Fred Smith 01243-78935
> John Brown 01229-321546
>
> Is there an easy way to accomplish this?
>
> Wibs
>
>
> --
> Wibs
> ------------------------------------------------------------------------
> Wibs's Profile: http://www.excelforum.com/member.php...o&userid=28922
> View this thread: http://www.excelforum.com/showthread...hreadid=551982
>
>

I had some real fancy thoughts when I first started putting that together,
but I realize now that it can be simplified a lot!!

Formula for B2: =OFFSET(A1,1+E2,0)
Formula for C2: =OFFSET(A1,2+E2,0)
Formula for D2: =OFFSET(A1,3+E2,0)

all the rest remains the same. Since Row(n+1)-Row(n) always equals 1, why
not just make it part of the equation without all the complications! :-)

"JLatham" wrote:

> Not Exacltly a Transpose Solution
> but one that I think will work for you and is relatively easy to set up.
>
> Make sure that your first Forename entry starts on row 2 - if it's on row 1
> now, just insert a new row.
>
> Assuming data is in column A and we can use columns B, C, D and E.
>
> Put your column headings in B1, C1, D1 and -3 in E1
>
> Formula for B2: =OFFSET(A1,ROW(B2)-ROW(A1)+E2,0)
> Formula for C2: =OFFSET(A1,ROW(B2)-ROW(A1)+1+E2,0)
> Formula for D2: =OFFSET(A1,ROW(B2)-ROW(A1)+2+E2,0)
> Formula for E2: =E1+3
>
> Now simply select those 4 cells (B2:E2) and drag down the page until all of
> your data has been moved.
>
> To get rid of the need to keep the original data:
> Select all of the new entries in columns B, C and D and [ctr]+[c] copy them
> and then use Edit | Paste Special and choose the [Values] option to get rid
> of the formulas and replace them with the actual data. You can now delete
> columns A and E since they aren't needed any longer.
>
> "Wibs" wrote:
>
> >
> > Hi,
> >
> > I have a table that consists of just one column, and consists of data
> > of the sort:
> >
> > Forename
> > Surname
> > Telephone
> > <space>
> > Forename
> > Surname
> > Telephone
> > <space>
> >
> > What I would like is three column headings, with the data under them,
> > i.e
> > Forename Surname Telephone
> > Fred Smith 01243-78935
> > John Brown 01229-321546
> >
> > Is there an easy way to accomplish this?
> >
> > Wibs
> >
> >
> > --
> > Wibs
> > ------------------------------------------------------------------------
> > Wibs's Profile: http://www.excelforum.com/member.php...o&userid=28922
> > View this thread: http://www.excelforum.com/showthread...hreadid=551982
> >
> >

Hi

In cell B1 enter
=INDEX($A$1:$A$1000,COLUMN(A:A)+(ROW(1:1)-1)*4)
Copy across through C1:D1 then copy B1:D1 down until you see the first
row of 0's appearing.

Change the range $A$1:$A$1000 to encompass the full range of your
existing data.

--
Regards

Roger Govier


"Wibs" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> I have a table that consists of just one column, and consists of data
> of the sort:
>
> Forename
> Surname
> Telephone
> <space>
> Forename
> Surname
> Telephone
> <space>
>
> What I would like is three column headings, with the data under them,
> i.e
> Forename Surname Telephone
> Fred Smith 01243-78935
> John Brown 01229-321546
>
> Is there an easy way to accomplish this?
>
> Wibs
>
>
> --
> Wibs
> ------------------------------------------------------------------------
> Wibs's Profile:
> http://www.excelforum.com/member.php...o&userid=28922
> View this thread:
> http://www.excelforum.com/showthread...hreadid=551982
>

Many thanks to those who replied. I shall try all the suggestions.

Wibs