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...

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

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.

"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?

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.

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.

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.

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.

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.