+ Reply to Thread
Results 1 to 8 of 8

Arrange columns of names & addresses into rows

  1. #1
    Registered User
    Join Date
    03-24-2010
    Location
    USA
    MS-Off Ver
    MS Office Professional 2007
    Posts
    15

    Arrange columns of names & addresses into rows

    I've got a list of names and addresses that I'm trying to organize for a mail merge. These list of names are in a CSV format, I've attached a file as an example. (I had to upload it as a xlsx file...Please change the file extension back to *.csv after you've downloaded it, as the as the forum only allow for excel & picture files). What I am trying to achieve is the final product which looks like the 2nd screenshot. As you can see from the screenshot, there is no consistency where the addresses are. Some are in one cell, some are on 2 cells... to make things worse, the list of names & addresses are in a 3 column format.

    What is the best way to achieve this? Is VBA script the most appropriate solution or is there an Excel function that would ado the job?

    Original File layout:
    \1

    Would like the result to look like this:
    \1
    Attached Files Attached Files
    Last edited by SKMaverick; 05-04-2010 at 02:17 AM.

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Arrange columns of names & addresses into rows

    SKMaverick,

    it would be a lot easier to advise or provide a solution if you posted the workbook from which you took your screenshots. After all, you want help with Excel, not with pictures, right?

    Edit: I see you attached a workbook. Unfortunately, my Excel tells me it's an invalid file format and I can't open it.

  3. #3
    Registered User
    Join Date
    03-24-2010
    Location
    USA
    MS-Off Ver
    MS Office Professional 2007
    Posts
    15

    Re: Arrange columns of names & addresses into rows

    Quote Originally Posted by teylyn View Post
    SKMaverick,
    Edit: I see you attached a workbook. Unfortunately, my Excel tells me it's an invalid file format and I can't open it.
    As I said in my original post, the file name needs to be changed. So after you've downloaded the attachment, change the file name
    from: sample.csv.xlsx
    to: sample.csv

    Afterwards you should be able to open it just fine.

  4. #4
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

    Re: Arrange columns of names & addresses into rows

    I think with five and a half thousand posts teylyn knows how to open a csv file. There is a problem with the attachment.

  5. #5
    Registered User
    Join Date
    03-24-2010
    Location
    USA
    MS-Off Ver
    MS Office Professional 2007
    Posts
    15

    Re: Arrange columns of names & addresses into rows

    Quote Originally Posted by StephenR View Post
    I think with five and a half thousand posts teylyn knows how to open a csv file. There is a problem with the attachment.
    While teylyn may have over 5000 posts, we are all human and it's possible she overlooked something. There is nothing wrong with the file attachment. I've downloaded it and tested it on two different computers and it opens just fine in CSV format. Either the file extension was not changed or there was a step she/he missed.

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Arrange columns of names & addresses into rows

    Try this on data after you've opened it Excel:
    Please Login or Register  to view this content.
    You can fix the one name that puts JR. alone as last name.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  7. #7
    Registered User
    Join Date
    03-24-2010
    Location
    USA
    MS-Off Ver
    MS Office Professional 2007
    Posts
    15

    Re: Arrange columns of names & addresses into rows

    JBeaucaire,

    It works beautifully. Just one small thing however -- what line of code do I need to insert so that the results appear in a new workbook? The reason for this is because I'd like to save the results in a new file *.xlsx format.

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Arrange columns of names & addresses into rows

    Just do a SAVE AS afterwards.

    =========
    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1