transposing contact info

[Guest]

Hi,

I'm weak w/ excel, so someone will have to work me through this slowly if
I'm going to figure it out.

I have a master list a couple hundred pages long in word that has the
contact info for various organizations on it. The entries are all different
sizes and some inlcude discriptions. There are also many, many repeat
entries. I'm assuming the best way for me to get rid of the repeat entries
will be to make a list out of it in the end and just manually chose the best
entry for the repeats.

I want to transpose it from the one column it currently occupies in excel to
one row with several columns for each organization without have to paste
special|transpose for each entry.

The enties are sperated by a blank cell.

It would be awesome if the final list could output everything in the same
order. E.x. Organization|contact|email|phone|address|description
And the entries that didn't have a phone number would just have a blank
there. The data isn't uniform, however, so I don't think that it would be
worth the effort to figure out how to do this.

Can someone help me create a macro that will do this?

Here is an example of the data:

Davidson Films, Inc. ¨
668 Marsh St.
San Luis Obispo, CA 93401
Phone: (805) 594-0422
(e-mail address removed)
http://www.davidsonfilms.com/
Notes: Since 1955 producing quality videos for colleges, school districts
and eductors concerned with Human Development, Cognitive Psychology, Teacher
Training and Rhetoric.

DeBeck Educational Productions
3873 Airport Way, Box 9754
Bellingham, WA 98227-9754
Phone: (604) 739-7696
www.debeck.com
<blank cell>
William Greaves Productions Inc. ¨
230 W. 55th St.
New York, NY 10019
Phone: (212) 265-6150, (800) 874-8314
Fax: (212) 315-0027
www.williamgreaves.com
(e-mail address removed)
<blank cell>
James Agee Film Project ¨
909 West Main St. 2B
Charlottesville, VA 22903
Phone: 800-971-2921
www.ageefilms.org
<blank cell>

 

[Otto Moehrbach]

Ryan
I'll try to work up something for you but I need some clarification from
you.
You say the entries are separated by a blank row.
Is that ONE blank row?
Always ONE and never more than ONE?
Are all phone numbers preceded by "Phone"?
Ditto for FAX numbers?
Do you want all of the Note put into one cell or put in a number of cells?
In all entries, is the order of the individual items always the same?
Nevermind that there may not be, say, a phone number or a FAX number or a
Note. Is what is there always in the same order (i.e. Name, Street, City
state zip, etc)?
If there is an email address, is it always no more than one?
Same for the internet site?
Is the name AND address always in 3 rows?
Is the Note always at the end?
Otto

 

[Guest]

The only thing that is consistant is that the entries are always separated by
one blank row.

I've pretty much given up on being able to output all the info in the same
order. Nothing is consistant. Many entries will have just a website. Many
have the phone and email in the same cell. It's pretty much just a mess.

I'd still like to be able to have all the info for one company in one row
spanning columns, but otherwiese I think it's too messy to deal with.

 

[Otto Moehrbach]

Ryan
I'll work up something for you. If you wish, contact me direct and give
me your email address so that I can ask questions as I go. Otherwise, I'll
work through this newsgroup. My email address is (e-mail address removed).
Remove the "nop" from this address. Otto

 

[Otto Moehrbach]

Ryan

Here is a macro that will do what you want.

I wrote this macro with certain assumptions. They are:

The original data is in Column A of the active sheet.

The original data is arranged in groups.

The groups are separated by blank cells.

Each group is to be copied and transposed and pasted into the first blank
cell in Column A in another sheet named "Shuffled Data".

No attempt is made to shuffle or sort data within each group.



If you send me, direct, your email address I will send you a small file with
this macro properly placed. My email address is (e-mail address removed).
Remove the "nop" from this address. HTH Otto



Sub ShuffleData()
Dim EndCell As Range 'Last cell in Column A
Dim FirstCell As Range 'First cell of a group
Dim LastCell As Range 'Last cell of a group
Dim Dest As Range 'First blank cell in Col A of the "Shuffle Data"
sheet
Set EndCell = Range("A" & Rows.Count).End(xlUp)
Set FirstCell = [A1]
With Sheets("Shuffled Data")
If .[A1] = "" Then
Set Dest = .Range("A1")
Else
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End With
Application.ScreenUpdating = False
Do
Set LastCell = FirstCell.End(xlDown)
Range(FirstCell, LastCell).Copy
Dest.PasteSpecial Transpose:=True
Set Dest = Dest.Offset(1)
Set FirstCell = LastCell.End(xlDown)
Loop Until FirstCell.Row > EndCell.Row
Application.CutCopyMode = False
Application.ScreenUpdating = True

MsgBox "Copying has been completed."
End Sub

 

[Guest]

Thanks so much for doing that for me.

Unfortunately, it didn't work well enough for me to use it.

The organization name, which is always the first heading, didn't always come
out in column A. In fact, it got quite jumbled for many entries.

I'm not sure why it would do this. There is no inconsistancy in the blank
cell between data groups.

Ryan

Here is a macro that will do what you want.

I wrote this macro with certain assumptions. They are:

The original data is in Column A of the active sheet.

The original data is arranged in groups.

The groups are separated by blank cells.

Each group is to be copied and transposed and pasted into the first blank
cell in Column A in another sheet named "Shuffled Data".

No attempt is made to shuffle or sort data within each group.



If you send me, direct, your email address I will send you a small file with
this macro properly placed. My email address is (e-mail address removed).
Remove the "nop" from this address. HTH Otto



Sub ShuffleData()
Dim EndCell As Range 'Last cell in Column A
Dim FirstCell As Range 'First cell of a group
Dim LastCell As Range 'Last cell of a group
Dim Dest As Range 'First blank cell in Col A of the "Shuffle Data"
sheet
Set EndCell = Range("A" & Rows.Count).End(xlUp)
Set FirstCell = [A1]
With Sheets("Shuffled Data")
If .[A1] = "" Then
Set Dest = .Range("A1")
Else
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End With
Application.ScreenUpdating = False
Do
Set LastCell = FirstCell.End(xlDown)
Range(FirstCell, LastCell).Copy
Dest.PasteSpecial Transpose:=True
Set Dest = Dest.Offset(1)
Set FirstCell = LastCell.End(xlDown)
Loop Until FirstCell.Row > EndCell.Row
Application.CutCopyMode = False
Application.ScreenUpdating = True

MsgBox "Copying has been completed."
End Sub

The only thing that is consistant is that the entries are always separated
by
one blank row.

I've pretty much given up on being able to output all the info in the same
order. Nothing is consistant. Many entries will have just a website.
Many
have the phone and email in the same cell. It's pretty much just a mess.

I'd still like to be able to have all the info for one company in one row
spanning columns, but otherwiese I think it's too messy to deal with.

 

[Otto Moehrbach]

Ryan
You posted that the macro I wrote for you didn't work well. That may be
due to a number of things. The data I used was what you had posted and I
cleared the cells you had listed as <blank cell>. That made those cells
truly blank. If your actual data is imported those cells may not be truly
blank and that would cause problems. It would be best if you would send me
a small file with data representative of what you actually have. That way I
can find out why my macro is working and add code to take care of whatever
the problem is.
As you saw in the file I sent you, everything works as it should. Otto

Thanks so much for doing that for me.

Unfortunately, it didn't work well enough for me to use it.

The organization name, which is always the first heading, didn't always
come
out in column A. In fact, it got quite jumbled for many entries.

I'm not sure why it would do this. There is no inconsistancy in the blank
cell between data groups.

Ryan

Here is a macro that will do what you want.

I wrote this macro with certain assumptions. They are:

The original data is in Column A of the active sheet.

The original data is arranged in groups.

The groups are separated by blank cells.

Each group is to be copied and transposed and pasted into the first blank
cell in Column A in another sheet named "Shuffled Data".

No attempt is made to shuffle or sort data within each group.



If you send me, direct, your email address I will send you a small file
with
this macro properly placed. My email address is (e-mail address removed).
Remove the "nop" from this address. HTH Otto



Sub ShuffleData()
Dim EndCell As Range 'Last cell in Column A
Dim FirstCell As Range 'First cell of a group
Dim LastCell As Range 'Last cell of a group
Dim Dest As Range 'First blank cell in Col A of the "Shuffle Data"
sheet
Set EndCell = Range("A" & Rows.Count).End(xlUp)
Set FirstCell = [A1]
With Sheets("Shuffled Data")
If .[A1] = "" Then
Set Dest = .Range("A1")
Else
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End With
Application.ScreenUpdating = False
Do
Set LastCell = FirstCell.End(xlDown)
Range(FirstCell, LastCell).Copy
Dest.PasteSpecial Transpose:=True
Set Dest = Dest.Offset(1)
Set FirstCell = LastCell.End(xlDown)
Loop Until FirstCell.Row > EndCell.Row
Application.CutCopyMode = False
Application.ScreenUpdating = True

MsgBox "Copying has been completed."
End Sub

The only thing that is consistant is that the entries are always
separated
by
one blank row.

I've pretty much given up on being able to output all the info in the
same
order. Nothing is consistant. Many entries will have just a website.
Many
have the phone and email in the same cell. It's pretty much just a
mess.

I'd still like to be able to have all the info for one company in one
row
spanning columns, but otherwiese I think it's too messy to deal with.

:

Ryan
I'll try to work up something for you but I need some
clarification
from
you.
You say the entries are separated by a blank row.
Is that ONE blank row?
Always ONE and never more than ONE?
Are all phone numbers preceded by "Phone"?
Ditto for FAX numbers?
Do you want all of the Note put into one cell or put in a number of
cells?
In all entries, is the order of the individual items always the same?
Nevermind that there may not be, say, a phone number or a FAX number
or a
Note. Is what is there always in the same order (i.e. Name, Street,
City
state zip, etc)?
If there is an email address, is it always no more than one?
Same for the internet site?
Is the name AND address always in 3 rows?
Is the Note always at the end?
Otto
Hi,

I'm weak w/ excel, so someone will have to work me through this
slowly
if
I'm going to figure it out.

I have a master list a couple hundred pages long in word that has
the
contact info for various organizations on it. The entries are all
different
sizes and some inlcude discriptions. There are also many, many
repeat
entries. I'm assuming the best way for me to get rid of the repeat
entries
will be to make a list out of it in the end and just manually chose
the
best
entry for the repeats.

I want to transpose it from the one column it currently occupies in
excel
to
one row with several columns for each organization without have to
paste
special|transpose for each entry.

The enties are sperated by a blank cell.

It would be awesome if the final list could output everything in the
same
order. E.x. Organization|contact|email|phone|address|description
And the entries that didn't have a phone number would just have a
blank
there. The data isn't uniform, however, so I don't think that it
would
be
worth the effort to figure out how to do this.

Can someone help me create a macro that will do this?

Here is an example of the data:

Davidson Films, Inc. ¨
668 Marsh St.
San Luis Obispo, CA 93401
Phone: (805) 594-0422
(e-mail address removed)
http://www.davidsonfilms.com/
Notes: Since 1955 producing quality videos for colleges, school
districts
and eductors concerned with Human Development, Cognitive Psychology,
Teacher
Training and Rhetoric.

DeBeck Educational Productions
3873 Airport Way, Box 9754
Bellingham, WA 98227-9754
Phone: (604) 739-7696
www.debeck.com
<blank cell>
William Greaves Productions Inc. ¨
230 W. 55th St.
New York, NY 10019
Phone: (212) 265-6150, (800) 874-8314
Fax: (212) 315-0027
www.williamgreaves.com
(e-mail address removed)
<blank cell>
James Agee Film Project ¨
909 West Main St. 2B
Charlottesville, VA 22903
Phone: 800-971-2921
www.ageefilms.org
<blank cell>

 

[Guest]

It actually does work.

It can't handle "groups" that are only one cell, so I just deleted all of my
entries that were 1 cell.

Thanks!

Ryan
You posted that the macro I wrote for you didn't work well. That may be
due to a number of things. The data I used was what you had posted and I
cleared the cells you had listed as <blank cell>. That made those cells
truly blank. If your actual data is imported those cells may not be truly
blank and that would cause problems. It would be best if you would send me
a small file with data representative of what you actually have. That way I
can find out why my macro is working and add code to take care of whatever
the problem is.
As you saw in the file I sent you, everything works as it should. Otto

Thanks so much for doing that for me.

Unfortunately, it didn't work well enough for me to use it.

The organization name, which is always the first heading, didn't always
come
out in column A. In fact, it got quite jumbled for many entries.

I'm not sure why it would do this. There is no inconsistancy in the blank
cell between data groups.

Ryan

Here is a macro that will do what you want.

I wrote this macro with certain assumptions. They are:

The original data is in Column A of the active sheet.

The original data is arranged in groups.

The groups are separated by blank cells.

Each group is to be copied and transposed and pasted into the first blank
cell in Column A in another sheet named "Shuffled Data".

No attempt is made to shuffle or sort data within each group.



If you send me, direct, your email address I will send you a small file
with
this macro properly placed. My email address is (e-mail address removed).
Remove the "nop" from this address. HTH Otto



Sub ShuffleData()
Dim EndCell As Range 'Last cell in Column A
Dim FirstCell As Range 'First cell of a group
Dim LastCell As Range 'Last cell of a group
Dim Dest As Range 'First blank cell in Col A of the "Shuffle Data"
sheet
Set EndCell = Range("A" & Rows.Count).End(xlUp)
Set FirstCell = [A1]
With Sheets("Shuffled Data")
If .[A1] = "" Then
Set Dest = .Range("A1")
Else
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End With
Application.ScreenUpdating = False
Do
Set LastCell = FirstCell.End(xlDown)
Range(FirstCell, LastCell).Copy
Dest.PasteSpecial Transpose:=True
Set Dest = Dest.Offset(1)
Set FirstCell = LastCell.End(xlDown)
Loop Until FirstCell.Row > EndCell.Row
Application.CutCopyMode = False
Application.ScreenUpdating = True

MsgBox "Copying has been completed."
End Sub

The only thing that is consistant is that the entries are always
separated
by
one blank row.

I've pretty much given up on being able to output all the info in the
same
order. Nothing is consistant. Many entries will have just a website.
Many
have the phone and email in the same cell. It's pretty much just a
mess.

I'd still like to be able to have all the info for one company in one
row
spanning columns, but otherwiese I think it's too messy to deal with.

:

Ryan
I'll try to work up something for you but I need some
clarification
from
you.
You say the entries are separated by a blank row.
Is that ONE blank row?
Always ONE and never more than ONE?
Are all phone numbers preceded by "Phone"?
Ditto for FAX numbers?
Do you want all of the Note put into one cell or put in a number of
cells?
In all entries, is the order of the individual items always the same?
Nevermind that there may not be, say, a phone number or a FAX number
or a
Note. Is what is there always in the same order (i.e. Name, Street,
City
state zip, etc)?
If there is an email address, is it always no more than one?
Same for the internet site?
Is the name AND address always in 3 rows?
Is the Note always at the end?
Otto
Hi,

I'm weak w/ excel, so someone will have to work me through this
slowly
if
I'm going to figure it out.

I have a master list a couple hundred pages long in word that has
the
contact info for various organizations on it. The entries are all
different
sizes and some inlcude discriptions. There are also many, many
repeat
entries. I'm assuming the best way for me to get rid of the repeat
entries
will be to make a list out of it in the end and just manually chose
the
best
entry for the repeats.

I want to transpose it from the one column it currently occupies in
excel
to
one row with several columns for each organization without have to
paste
special|transpose for each entry.

The enties are sperated by a blank cell.

It would be awesome if the final list could output everything in the
same
order. E.x. Organization|contact|email|phone|address|description
And the entries that didn't have a phone number would just have a
blank
there. The data isn't uniform, however, so I don't think that it
would
be
worth the effort to figure out how to do this.

Can someone help me create a macro that will do this?

Here is an example of the data:

Davidson Films, Inc. ¨
668 Marsh St.
San Luis Obispo, CA 93401
Phone: (805) 594-0422
(e-mail address removed)
http://www.davidsonfilms.com/
Notes: Since 1955 producing quality videos for colleges, school
districts
and eductors concerned with Human Development, Cognitive Psychology,
Teacher
Training and Rhetoric.

DeBeck Educational Productions
3873 Airport Way, Box 9754
Bellingham, WA 98227-9754
Phone: (604) 739-7696
www.debeck.com
<blank cell>
William Greaves Productions Inc. ¨
230 W. 55th St.
New York, NY 10019
Phone: (212) 265-6150, (800) 874-8314
Fax: (212) 315-0027
www.williamgreaves.com
(e-mail address removed)
<blank cell>
James Agee Film Project ¨
909 West Main St. 2B
Charlottesville, VA 22903
Phone: 800-971-2921
www.ageefilms.org
<blank cell>