1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Moving rows to columns

Discussion in 'Business Applications' started by finspa, Aug 15, 2014.

Thread Status:
Not open for further replies.
Advertisement
  1. finspa

    finspa Thread Starter

    Joined:
    Aug 14, 2014
    Messages:
    9
    Hi all. First post so be kind! Bit of a novice at VBA, but i am sure this is possible..
    basically, I have a contact list in Excel that I need to format 'correctly'. It is currently formatted with the address on multiple rows, with phone number and email address in another column ( i have attached a dummy file). I would like to 'move' data so it is in correct columns...
    Name Address1 Address2 Address3 Address4 Address5 Phone Email

    To make matters a bit trickier, the number of address rows varies, so it might be 5 rows or sometimes 3; and not all contacts have an email address (although they would always be in row "2" of a contact address.

    Many thanks in advance for any advice

    Finspa
     

    Attached Files:

  2. texasbullet

    texasbullet

    Joined:
    Jun 11, 2014
    Messages:
    2,352
    First Name:
    Ramon
    What version of Microsoft Office are you using?
     
  3. finspa

    finspa Thread Starter

    Joined:
    Aug 14, 2014
    Messages:
    9
    Sorry, should have said, its 2010
     
  4. texasbullet

    texasbullet

    Joined:
    Jun 11, 2014
    Messages:
    2,352
    First Name:
    Ramon
    Have you tried the Microsoft Access. There are several things you can use including contacts.
     
  5. finspa

    finspa Thread Starter

    Joined:
    Aug 14, 2014
    Messages:
    9
    Sadly, i dont have MS Access. I have loaded the file to SQL Express to see if there was anything clever i could do there but came to the conclusion that the solution probably lies in Excel, possibly using (forgive my ignorance) offset, or checking if COL A was blank. For example, i have tried to put a label next to each address row using formula such as IF(A2<>"","Address1","") and IF(AND(B3<>"",A4<>"","Postcode","Address4") but then i got a bit stuck!
     
  6. texasbullet

    texasbullet

    Joined:
    Jun 11, 2014
    Messages:
    2,352
    First Name:
    Ramon
    The only thing I can think of is to use one line at a time for the information on each person.
    Use row 1 for name, 2 for address...etc. up to number 7 or whatever number you end up using.
    Row 2....same as above
     
  7. finspa

    finspa Thread Starter

    Joined:
    Aug 14, 2014
    Messages:
    9
    I think I have a way to do it! I have now put an address 'label' in for each row. All i need to do now is find a way to copy down the company name to fill in the blanks so that each address row has the company name in. Does that make sense? And does anyone know how i can do it!
    Then i can vlookup the address label against the company name and I have done it!
     
  8. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    Have a look at the attached

    I did as you suggested and added some helper columns (and formulae) for the Source address sheet and then looked up from the Addresses sheet using index / match (like vlookup)

    It's just a case of pasting your data into the source sheet and copying the formulas down on the source sheet and addresses sheet as required
     

    Attached Files:

  9. finspa

    finspa Thread Starter

    Joined:
    Aug 14, 2014
    Messages:
    9
    Thank you David. That is absolute genius!! I am encouraged that my thinking was (obviously with a little help from Google) at least on the right lines, although, i think it would have taken me a very long time to come up with such an efficient and perfect solution!
    Thanks again. How do i go about assigning this to "Solved"?
     
  10. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    I think there is something under thread tools for solving it . :)


    Happy to help. Hope you find time to come back to the forum again. :)
     
  11. thefasninja

    thefasninja

    Joined:
    Nov 30, 2013
    Messages:
    37
    I will suggest to use MS access.
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1131620

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice