Not exactly a transpose problem

[Wibs]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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!

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.

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

 

[Roger Govier]

Hi

In cell B1 enter
=INDEX($A$1:$A$1000,COLUMN(A:A)+(ROW(1:1)-1)*4)
Copy across through C11 then copy B11 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.

 

[Wibs]

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

Wib