Help with Converting Rows to Column

[Kyguy922]

I have Excel 97 and have a huge text list of 1000 or so companies tha
need to look like a professional spreadsheet: Here is an example of th
problem I have, when I paste the large text file in to Excel, this i
what I get:
A B
Name: Target
Contact Names: Greg, Pam
Established: 1997

Name: CISCO
Contact Name: Fred
Established: 1941

Now how could I make this list, look something like
A B C
Name Contact Names Established
Target Greg, Pam 1997
CISCO Fred 1941


I am having difficulty, and am guessing it is something to do with tex
to columns, but when I do that, it will just make the list really lon
horizontally. So can I use all the data and consolidate it into nea
column headings? If anyone could help me, I would greatly appreciat
it

 

[Kyguy922]

Paste Special does not do the job I need...

This is a great idea which is not going to solve my problem, whic
other people have posted earlier. The tranpose leaves a long lis
horizontally...and I need the fields not only horizontal, but als
organized in the proper field.

Thanks for the help guys

 

[Trevor Shuttleworth]

One way:

Assuming your data is in Sheet 2; this routine creates a new table in Sheet
3

Sub MakeTable()
Dim LastRow As Long
Dim i As Long
Dim NewRow As Long
Application.ScreenUpdating = False
NewRow = 2
With Sheets("Sheet2")
LastRow = .Range("A65536").End(xlUp).Row
End With
With Sheets("Sheet3")
.Range("A1") = "Name"
.Range("B1") = "Contact Name"
.Range("C1") = "Established"
End With
For i = 1 To LastRow Step 4
With Sheets("Sheet3")
.Range("A" & NewRow) = Sheets("Sheet2").Range("B" & i)
.Range("B" & NewRow) = Sheets("Sheet2").Range("B" & i + 1)
.Range("C" & NewRow) = Sheets("Sheet2").Range("B" & i + 2)
End With
NewRow = NewRow + 1
Next i
End Sub

Regards

Trevor

 

[Kyguy922]

Thanks Trevor for your hard work, I thought there was an easy way, bu
the more I look at it, I have a very difficult problem, let me attac
the actual excel documet I am working with, since it is not as easy a
I originally posted.
There are 10 catagories, the contacts varying from 1 to 8 or so people
the addresses are sometimes 1, 2 or even 3 lines, the SAMPLE Grant
would cause headaches to anyone...there are so many variables, I thin
it would take a genius to solve this problem....

If someone smart enough, can just tell me there is no way, then I woul
feel better, but if an even smarter person could figure it out, yo
would probably receive a blessing from God

 

[Trevor Shuttleworth]

Mail me at tms.excel at ntlworld.com

No promises !

Regards

Trevor

 

[Trevor Shuttleworth]

Kyle

I have replied to your email addresses with a converted workbook. The code
is listed below:

Sub MakeTable()
Dim LastRow As Long
Dim i As Long
Dim NewRow As Long
Dim NewColumn As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With Sheets("Sheet1")
LastRow = .Range("A65536").End(xlUp).Row
' LastRow = 444 ' for testing
End With

With Sheets("Sheet1")
For i = 1 To LastRow
' discard blank rows
If .Range("A" & i) = "" And .Range("B" & i) = "" Then GoTo BlankRow
Select Case Len(.Range("A" & i))
Case 27 ' Profile Number
NewRow = --WorksheetFunction.Substitute( _
Mid(.Range("A" & i), 20, 8), "*", "") + 1
Case 0 ' No Field Description - add to previous
Sheets("Sheet2").Cells(NewRow, NewColumn) = _
Sheets("Sheet2").Cells(NewRow, NewColumn) & _
" | " & Left(.Range("B" & i), 255)
Case Else ' must be a Field Description
NewColumn = --WorksheetFunction.Match(.Range("A" & i), _
Sheets("Sheet2").Range("A1:Z1"), 0)
.Range("B" & i).Copy _
Sheets("Sheet2").Cells(NewRow, NewColumn)
End Select

BlankRow:
Next i
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

You'll need to set up Sheet2 with the following headings in cells A1 through
to Z1:

Address:
Application Procedure:
Areas of Interest:
Assets:
Contact Person:
Deadline:
Donors:
E-mail Address:
Established:
Fiscal Year Ending:
Foundation Name:
Future Approved Grants:
Geographic Focus:
Gifts Received:
Grants Paid:
Internet:
Largest Grant:
Limitations:
Median Grant:
Number of Grants:
Officers/Directors:
Other Information:
Phone:
Purpose:
Sample Grants:
Smallest Grant:

You might want to adjust the order of the columns once you've converted the
data. However, it doesn't touch the original data so you can always rerun
it.

The email may not have got through to you because it has an attachment or
because of its size. Contact me again if you still haven't got the original
workbook

Regards

Trevor