Make Mailings Addresses go Across in Excel instead of down...

[aprunkard]

My addresses are listed like this...
Mcdonald's Restaurants
826 West Walnut Street
Johnson City, TN 37604


423-928-8838
Email:
business profile | phone phone | map & details

http://www.mcdonalds.com/usa.html

--------------------------------------------------------------------------------



Mcdonald's Restaurants
1710 West Market Street
Johnson City, TN 37604


423-929-2291
Email:
business profile | phone phone | map & details

http://www.mcdonalds.com/usa.html

--------------------------------------------------------------------------------
Some are 16 lines and some are 17...
Is there anyway without using complicated macros and without writing a formula for each address seperately b/c there are 200+ to make them....

Column 1:Mcdonald's Restaurants
Column 2:1710 West Market Street
Column 3:Johnson City, TN 37604

 

[SkipVought]

Hi,

"Is there anyway without using complicated macros and without writing a formula for each address seperately."

There probably is, if you can find out who is generating this REPORT in this format and have THEM generate a TABLE instead.

Short of that, is a coding job of some kind.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[aprunkard]

No way to generate the report differently... I'm getting it from a website.

Is there a formula that can be created that looks for the "---------" after each entry and knows that that is the end of the entry and to start another one? Then I could just copy and paste that formula down.

 

[SkipVought]

Is there ALWAYS, WITHOUT FAIL, ALL THE TIME, the same number of rows between the rows with -------?

Is there ALWAYS, WITHOUT FAIL, ALL THE TIME, three rows for each addressee?

You will have to write formulas.

So there's ALWAYS the same number of rows AFTER the dashes. The Compan Name is FOUR rows after the dashes, street address FIVE, City State, ZIP SIX.

Try working on that and post back when you have a problem.


Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[aprunkard]

Try working on that and post back when you have a problem."
Geesh this place has always been a friendly place to post messages for help.

I guess it's not anymore.

For those of you who do not believe they are above everyone else...

No, there are either 16 or 17 rows between the dashes but every entry ends with the dashes and every entry begins with three blank lines.

And thank to those who enjoy helping others with technical issues (or situations that they are having problems with).

 

[SkipVought]

This is Tek-Tips not Tek-we-give-you-free-help-desk-support-so-you-don't-have-to-do-any-work-yourself

What have you tried so far?

Did you try doing ANYTHING with the tip I passed to you?

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[petrosky]

Hey,

If there are "only" 200 odd and it is a one off job...you may want to look at the following macro.

Sub TransposeAddressDetails()
'
'
'

    Dim lastRow
    
    
    
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("CleanData").Select
    lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    Range("A" & lastRow).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Sheets("RawData").Select    
        
End Sub

This assumes you have highlighted the address before running the macro. I would assign this to a shortcut key to save time. eg CTR-S.
Then it is just a matter of scrolling down the sheet highlighing the data you wish transposed.

If you have to do this regularly, you may wish to utilize some of the above in your code once you work out the logic of the varied line breaks etc.

HTH.

Peter.

Remember- It's nice to be important,
but it's important to be nice

 

[SkipVought]

Peter,

The OP explicitly stated, "...Is there anyway without using complicated macros ..." and this is the Office forum. VBA in Forum707.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[petrosky]

Hi Skip,

Oops...then in that case. The answer is no.

Peter.

Remember- It's nice to be important,
but it's important to be nice

 

[SkipVought]

TIP:

You can use a simple formula like this
[tt]
B2: =IF(LEFT($A2,3)="---",A6,B1)
[/tt]
Then once all your values are assembled in columns B, C & D, (you will notice that there are multiples) use Data > Advanced Filter - UNIQUE VALUES to generate a unique list with all addressees.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]