MsOf10 I can't re-arrange data from single-column to multi-column in EXCEL 2010-VBForums
Results 1 to 9 of 9

Thread: I can't re-arrange data from single-column to multi-column in EXCEL 2010

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2012
    Posts
    5

    Resolved I can't re-arrange data from single-column to multi-column in EXCEL 2010

    Hey guys,

    Thanks very much for taking the time to read this. Fist of all I need to clarify that I am not a developer (not even close) or anything really closely related to IT/Programming, so I'm guessing my efforts will be kind of funny to some of you (if not all), however I still need your help so please bear with me while I try to explain my situation.

    What I have is an Excel worksheet, with the vast majority of its data arranged on column A. This data is an array of names with their addresses right underneath and their telephone numbers two cells below that address. Then after a few rows follows the next name etc... Some of these phone numbers are landlines (numbers < 3,000,000) and some are mobiles (numbers > 3,000,000). When a person has more than one number then his/her name repeats itself a few rows underneath the person's first number followed by the address right underneath that and then the extra number on the column on the right (column B, same row with address).

    What I would like is to have: the name in column A, its address in column B (same row as A), its phone in column C if it's a landline (same row as A)/ in column D if it's a mobile (same row as A).

    And so What I'm trying to do is:
    1) Select the first cell (A1)
    2) Check if the contents are text
    3) If it is, move the address to the correct cell
    4) Check if the cell next to where the address WAS is empty
    5) If it has a number and it is: a landline move it to the correct cell/ a mobile to the correct cell
    6) If it's empty, move the landline number to the correct cell/ a mobile number to the correct cell
    7) Select the cell underneath the one where the phone WAS

    8) lf the cell (A1) does not contain text, select the one underneath and loop the steps from #2 for 40,000 cells

    However I can't get it to work. So please help. I've attached an excel file with some sample names and numbers as well as the Macro named "Main". Thank you very very much...
    Attached Files Attached Files
    Last edited by Joey_G; Nov 18th, 2012 at 04:52 AM.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    20,000

    Re: I can't re-arrange data from single-column to multi-column in EXCEL 2010

    i can not open your .xlsx, as i use a previous version of excel i can only open .xls
    if you wish me to look, saveAs .xls and reattatch
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Nov 2012
    Posts
    5

    Resolved Re: I can't re-arrange data from single-column to multi-column in EXCEL 2010

    Hey westconn1,

    Thanks a lot for the quick responce. I've reattached both files as I also added some minor clarifications to the txt file. Hope you can access it this time. If not please let me know again. Thanks again.
    Attached Files Attached Files
    Last edited by Joey_G; Nov 18th, 2012 at 04:53 AM.

  4. #4
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    2,471

    Re: I can't re-arrange data from single-column to multi-column in EXCEL 2010

    "2) Check if the contents are text"

    How do you want to do this? See if the first letter is alpha? The first two or three letters? Or the whole string?

  5. #5
    Fanatic Member
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,002

    Re: I can't re-arrange data from single-column to multi-column in EXCEL 2010

    Judging by the example Excel-sheet: Every Block of a Name is 7 Rows long, but the one entry with "extra" has me confused.
    1) i would delete all empty entries/rows first.
    2) When does a "new" Name-Block starts? When the cell above is a number (in your case a mobile-phone)
    3) When is the entry the Address and not a name? When the Cell above is not a number. (Text, Text, Number, Number, Number --> name, address, phone, phone, phone)
    3b) checking if the entry is an address judging if it starts with a number is buggy, since, depending in which country you are, addresses can start with Text, in other countries with numbers. Better to check if cell above is Text, since i wouldn't know a name which starts with a number.

    That's all i can think of right now.
    For health reasons i try to avoid reading unformatted Code

  6. #6

    Thread Starter
    New Member
    Join Date
    Nov 2012
    Posts
    5

    Resolved Re: I can't re-arrange data from single-column to multi-column in EXCEL 2010

    to vbfbryce:

    I just want to check whether the contents of that cell are text (and not numeric or empty) to then start the whole loop from a name, avoiding start from an empty cell or a number. the names vary greatly and so I would be able to check on specific letters or string. The sure thing is the font color but I have no idea if I could run a check on that.
    Last edited by Joey_G; Nov 18th, 2012 at 04:53 AM.

  7. #7
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    2,471

    Re: I can't re-arrange data from single-column to multi-column in EXCEL 2010

    Code:
    Range("a" & i).Font.Color
    gives you the font color of A1, so you could do something like that.

    If not...are there NEVER numbers in a name (ie. Bill the 3rd)?

    Are there alpha characters at the start of an address ever?

    What other things do you need to watch out for?

    If the font color is consistent, that might be your best option.

  8. #8

    Thread Starter
    New Member
    Join Date
    Nov 2012
    Posts
    5

    Re: I can't re-arrange data from single-column to multi-column in EXCEL 2010

    to vbfbryce:

    thanks a lot for the code. It sounds great. The font color is consistent so I'll try that out and let you guys know.

  9. #9

    Thread Starter
    New Member
    Join Date
    Nov 2012
    Posts
    5

    Re: I can't re-arrange data from single-column to multi-column in EXCEL 2010

    Hey guys. Just posting to let you know the problem was indeed with the "trigger" so it was solved the momment I introduced the new one examining the font.color!! Thanks again for all your input. Amazing.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.