Transpose Column to Rows 1

davidmo · Sep 8, 2006

hey everyone:
I've searched through the Office Forum regarding the issue of transposing data in one column to various columns/rows. Can't seem to find a thread that can answer what I'm looking for. Most seem to deal with static data and not dynamic.

I have company mailing information address in Column A and want to move the data into rows so all company names are in column A, all street info in Column B, and so forth. Problem is that some company info has 3 lines, some 4 lines and some 5. See data below:

Current Data Format:
Column A
1st In Service Mortgages Inc.
340 Jericho Turnpike
Metro, ST 00000

1st Option Financial, LLC
Option 1st Financial
1504 East
Smallville, ST 00000

1st Security
233 Route 17
Village, ST 00000

Would like:
Column A Column B Column C
1st In Service Mortgages Inc. 340 Jericho Turnpike Metro, ST
1st Option Financial, LLC Option 1st Financial 1504 East
1st Security 233 Route 17 Village, ST

I've tried Offset but that doesn't work neither does transpose funtction.

Willing to do VBA if necessary.

Thanks.
DMo

CautionMP · Sep 8, 2006

davidmo,
Since your willing to do it in VBA and you tried [tt]Transpose()[/tt]:

Code:

Sub TransposeMailingInformation()
Dim wksOutput As Worksheet
Dim rngInput As Range
Dim rngRow As Range
Dim lngRowOut As Long, lngColumnOut

'Get the data from the input sheet, assumes Sheet1
Set rngInput = Worksheets("Sheet1").UsedRange
'Define the output sheet
Set wksOutput = Worksheets("Sheet2")
'Output starts at row 2 (headings in row 1)
lngRowOut = 2

For Each rngRow In rngInput.Rows
  'Just in case there is 'junk' data in other columns
  'use column 1
  If Trim(rngRow.Columns(1).Text) = "" Then
   'This cell has no info so must be new record
   'index the row and reset the column pointer
    lngRowOut = lngRowOut + 1
    lngColumnOut = 0
  Else
    'This cell has data so index the column
    lngColumnOut = lngColumnOut + 1
    'write the data
    wksOutput.Cells(lngRowOut, lngColumnOut) = rngRow.Columns(1).Text
  End If
Next rngRow
Set rngRow = Nothing
Set rngInput = Nothing
Set wksOutput = Nothing
End Sub

If all else fails, write a macro.
CMP

DirkStruan · Sep 9, 2006

Straight off , I admit to liking macros & VBA in general so listen to CMP if you are more comforatble but, there is another way using the in-built functions and an extra few columns, possibly. It depends on the format of your data

It all hinges on whether there is anything that marks the start or end of an address. If they are separated by blank rows or whether they all have ST 00000 in the final line - you just need something.

If such a marker exists, you can then use an IF function referring to that marker so that in column B you get a series of integers that indicate that are still on the same address. Assuming blank rows separate the data, in B3 say, you can use

=IF(A4="",B3+1,B3)

If there is no blank row but all last lines have ", ST 0000" or something in should refer to that (probably using the wilcard character *. In B4 have something like
"=IF(A3="*" & ", ST" & "*",B3+1,B3)"

Drag that formula to the end of the addresses in column A and note the max value in B (we'll call it n)

In Col C, list the numbers 1 to n.

In Col D, have "=match(C3,B:B,0)" [if row 3 is the first row containing an address] - this gives you the first row in col B where the number occurs.

In column E have "=countif(B:B,"=" & C3)" . Copy that down for 1 to n.

Not the max value in Col E.

Now, in F1, G1, H1 etc for as many columns as equals the max of column D, have an increasing string of integers.

In cols F onwards (again assuming row 3 to be the top row of data), using colname as a variable denoting row 1 of the column in question (i.e. the integer in row 1), you can use the function "=If(colname<=E3,index($B:$B,$D3+colname-1),""). Lock the columns using the dollar sign to allow you to drag the formula across and down.

That should do it, you can then copy, paste special or what ever you need to isolate the data, if required.

Dirk

davidmo · Sep 11, 2006

CMP & Dirk:
Thanks for the recommendations. Will try and let you know how it goes.

Have a great day.

DMO

davidmo · Sep 11, 2006

CMP:
The code worked great. Thanks.

Would like to give you star but not sure how.

DMO

StuckInTheMiddle · Sep 11, 2006

Davidmo you click the ' Thank CautionMP for this valuable post!' link at the bottom of CautionMP post to thank him

"If you can stay calm, while all around you is chaos...then you probably haven't completely understood the seriousness of the situation.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Transpose Column to Rows 1

davidmo

Technical User

CautionMP

Programmer

DirkStruan

Technical User

davidmo

Technical User

davidmo

Technical User

StuckInTheMiddle

Programmer

Similar threads

Part and Inventory Search

Sponsor