transposing contact info

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
dfi@davidsonfilms.com
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
WmLGreaves@aol.com
<blank cell>
James Agee Film Project ¨
909 West Main St. 2B
Charlottesville, VA 22903
Phone: 800-971-2921
www.ageefilms.org
<blank cell>

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
"Ryan" <Ryan@discussions.microsoft.com> wrote in message
news:1EBAB26E-8AC8-4FD8-8387-EBC0B30B52D5@microsoft.com...
> 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
> dfi@davidsonfilms.com
> 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
> WmLGreaves@aol.com
> <blank cell>
> James Agee Film Project ¨
> 909 West Main St. 2B
> Charlottesville, VA 22903
> Phone: 800-971-2921
> www.ageefilms.org
> <blank cell>

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" wrote:

> 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
> "Ryan" <Ryan@discussions.microsoft.com> wrote in message
> news:1EBAB26E-8AC8-4FD8-8387-EBC0B30B52D5@microsoft.com...
> > 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
> > dfi@davidsonfilms.com
> > 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
> > WmLGreaves@aol.com
> > <blank cell>
> > James Agee Film Project ¨
> > 909 West Main St. 2B
> > Charlottesville, VA 22903
> > Phone: 800-971-2921
> > www.ageefilms.org
> > <blank cell>
>
>
>

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 ottokmnop@comcast.net.
Remove the "nop" from this address. Otto
"Ryan" <Ryan@discussions.microsoft.com> wrote in message
news:68B71657-1DC0-4C3D-946C-FB533FDA4A16@microsoft.com...
> 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" wrote:
>
>> 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
>> "Ryan" <Ryan@discussions.microsoft.com> wrote in message
>> news:1EBAB26E-8AC8-4FD8-8387-EBC0B30B52D5@microsoft.com...
>> > 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
>> > dfi@davidsonfilms.com
>> > 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
>> > WmLGreaves@aol.com
>> > <blank cell>
>> > James Agee Film Project ¨
>> > 909 West Main St. 2B
>> > Charlottesville, VA 22903
>> > Phone: 800-971-2921
>> > www.ageefilms.org
>> > <blank cell>
>>
>>
>>

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 ottokmnop@comcast.net.
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

"Ryan" <Ryan@discussions.microsoft.com> wrote in message
news:68B71657-1DC0-4C3D-946C-FB533FDA4A16@microsoft.com...
> 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" wrote:
>
>> 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
>> "Ryan" <Ryan@discussions.microsoft.com> wrote in message
>> news:1EBAB26E-8AC8-4FD8-8387-EBC0B30B52D5@microsoft.com...
>> > 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
>> > dfi@davidsonfilms.com
>> > 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
>> > WmLGreaves@aol.com
>> > <blank cell>
>> > James Agee Film Project ¨
>> > 909 West Main St. 2B
>> > Charlottesville, VA 22903
>> > Phone: 800-971-2921
>> > www.ageefilms.org
>> > <blank cell>
>>
>>
>>

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.

"Otto Moehrbach" wrote:

> 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 ottokmnop@comcast.net.
> 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
>
> "Ryan" <Ryan@discussions.microsoft.com> wrote in message
> news:68B71657-1DC0-4C3D-946C-FB533FDA4A16@microsoft.com...
> > 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" wrote:
> >
> >> 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
> >> "Ryan" <Ryan@discussions.microsoft.com> wrote in message
> >> news:1EBAB26E-8AC8-4FD8-8387-EBC0B30B52D5@microsoft.com...
> >> > 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
> >> > dfi@davidsonfilms.com
> >> > 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
> >> > WmLGreaves@aol.com
> >> > <blank cell>
> >> > James Agee Film Project ¨
> >> > 909 West Main St. 2B
> >> > Charlottesville, VA 22903
> >> > Phone: 800-971-2921
> >> > www.ageefilms.org
> >> > <blank cell>
> >>
> >>
> >>
>
>
>

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
"Ryan" <Ryan@discussions.microsoft.com> wrote in message
news:7DC416BF-3E13-44C1-8850-8F5E98065AC7@microsoft.com...
> 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.
>
> "Otto Moehrbach" wrote:
>
>> 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 ottokmnop@comcast.net.
>> 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
>>
>> "Ryan" <Ryan@discussions.microsoft.com> wrote in message
>> news:68B71657-1DC0-4C3D-946C-FB533FDA4A16@microsoft.com...
>> > 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" wrote:
>> >
>> >> 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
>> >> "Ryan" <Ryan@discussions.microsoft.com> wrote in message
>> >> news:1EBAB26E-8AC8-4FD8-8387-EBC0B30B52D5@microsoft.com...
>> >> > 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
>> >> > dfi@davidsonfilms.com
>> >> > 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
>> >> > WmLGreaves@aol.com
>> >> > <blank cell>
>> >> > James Agee Film Project ¨
>> >> > 909 West Main St. 2B
>> >> > Charlottesville, VA 22903
>> >> > Phone: 800-971-2921
>> >> > www.ageefilms.org
>> >> > <blank cell>
>> >>
>> >>
>> >>
>>
>>
>>