Converting List into Grouped Columns

[Kyguy922]

I am amazed at the incredible brain power in this forum, and
appreciate all help.

I am taking a text file of a list of 1000 or so Foundations, with thei
addresses, contact names, and other fields, in a list form.
For example

Foundation Name: Boys and Girls Club
Address: PO Box 181
Contact: Herb Walters

Foundation Name: United Way
Address: 123 Cherry Lane
Contact: Homer Simpson

The list continues for 1000 foundations, and I would like to take thi
list, (which I already split into two columns, into a chart where unde
column A, I have 1000 foundations listed, under column B, all of th
Address, C, all the contacts.

The copy, and paste special; tranpose--> does not work because i
simply makes this really long vertical list into a long horizonta
list. Is there any way to convert this list into various colum
headings?

Thank you

 

[bj]

What I have done in this type of situation is (assuming
the data is in column A starting in row 1) in cell B1
enter "=A2" and in Cell C1 enter "=A3"
Copy B1 and C1 down to the end ot the active column.
select Columns B and C copy and paste special values on
top of the old entries.
in Column D enter 1,2 and 3 into Cells D1,D2 and D3 and
copy D1, D2 and D3 down to the end of the needed length in
column D, making sure that it stays as 1s, 2s and 3s.
check that the "1"s are associated with the data rows in
the order you want it.

 

[ElsiePOA]

This is a little primative, but it will work. First, I assume that you
can put all of your data into a single column and that each
organization has an equal number of catagories (ie: name, address and
contact) and that they are uniformly spaced (ie:each org has a the same
number of spaces seperating it from the one below). If this is the
case, select the first cell of the first organization, and then select
"record macro", making sure that you push the "relative reference"
button. Drag the address to the same row in column B, drag the contact
to the same row in column C. Select the Name cell in the second
organization and then stop recording.

Next, edit the macro and before the first line insert "For x = 1 to
1000" (or whatever the total number of organizations is.) After the
last line insert "Next x"

When you run the macro, it should accomplish what you want, except that
there will be several empty rows between each entry. You can resolve
this by selecting the entire list and doing a data sort. This will get
rid of all the empty rows.

As I said it is a little primative and I'm sure there is a more high
tech way to do it, but it should work fine.

I suggest you run a copy of the work sheet and practice on it.

Good luck

 

[Kyguy922]

I see what you are doing with this way, and it seems pretty simple,
just wish my Excel file is that simple, here is a real example of m
text, I just simplified it, assuming it would be easy, but the more
look at it, the more I think it is impossible.


Let me know if it is impossible, unless you are a genius, I don't thin
you have the time or brian power to solve this computing nightmare...I
order to get these all lined up into columns, I don't even know if it
possible, other than manually copying and pasting all 1048 profile
into a vertical spreadsheet.

Thank you all for your thoughts, and I am going to continue to lear
more about Excel as I work on some cool Macros. This is my first tim
using them, and I can see how useful this is!

********** PROFILE 1 **********

Foundation Name: 786 Foundation

Address: P.O. Box 8988
Madison, WI 53708

Phone: (608) 232-2056

Established: 1990

Contact Person: Blank, Sharon

Officers/Directors: ACS Financial Group
Blank, Sharon
M & I Trust Co. (tt)
US Trust Co.

Fiscal Year Ending: 12/31/2002

Assets: $4,196,830

Grants Paid: $32,420

Number of Grants: 8

Smallest Grant: $2,000

Largest Grant: $12,420

Median Grant: $2,000

Purpose: Distributions will be made to entities whic
are considered charitable, educational, religious, literary, o
scientific, whether an organization or an individual.

Application Procedure: None.

Deadline: None.

Sample Grants: 1. PETA ; $2,000
2. Freedom Place, Inc.; $2,000
3. Omega School Inc.; $2,000
4. Camp Manitowish; $5,000
5. Madison Metro School Dist.; $12,420
6. Int'l. Crane Foundation; $5,000
7. Michael Fields Agricultural Inst.
$2,000

Areas of Interest: Education, Higher, Elementary or Secondar
Schools, Environment, Human Services (General), Other Education
Recreation and Sports, Wildlif

 

[bj]

If the Term "Foundation Name:" is at the beginning of each
section and the other labels for each section are there
when appropriate in each segment, you could use a macro to
go down and effectively paste into the same row
iteratively until the next "Foundation Name:" is found:
the macro might get somwhat complicated depending on the
differences in segment lengths, but is not complex (again
if there is a label at the different sections of each
segment.)