Macro to separate records and convert one row into multiple columns

[Des888]

Hi, Can you please help?, I need a macro for excel 2007
I have a spreadsheet that has only one row and about 850 columns containing names job titles and a further 9 columns of details)

I need to convert this:
name_1 jobtitle_1 email_1 phonenumber_1 fax_1 example _1 name_2 etc


to this:
one column for names one column for job titles one column for emails etc

Hope this makes sense. Thank you!

 

[SteveO59L]

Is there some sort of sequence/order to this?

 

[Des888]

Yes every column in this order,
It is:
Name/Qualification(sometimes blank)/JobTitle1/JobTitleLine2 (sometimes blank)/Direct Line(text)/Direct Line(number)/email/emaildomain/Fax(text)/Fax (number)/blank field

Thank you!

 

[SteveO59L]

So each record is made up of 11 columns?

i.e
record1 uses cols a:k
record2 uses cols l:v
etc

 

[Des888]

Spot on thats exactly it!

 

[SteveO59L]

Something along the lines of

Range("a1").Select

lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
Dim c As Range, rng
Dim LASTROW As Long


numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + 0, numColumns + lastcol - 1).Select

For Each c In Selection
If c.Column Mod 11 = 0 Then
c.Offset(0, 1).Select
Selection.Resize(1, 11).Select
Selection.Copy
LASTROW = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(LASTROW, 1).Select
ActiveSheet.Paste
End If
Next c

 

[EcomTom]

If there is a / between every item you want in a column all you have to do is goto data > text to colums > then in step two check the other box and type in a /

 

[Des888]

There are no / between items I was denoting the different columns, sorry for any confusion.

Many thanks for your effort on this Steve too but I was unable to make it work, I received an error in a window

"Compile error:
Invalid outside procedure"

and the "a1" at the beginning of the script was highlighted.

 

[SteveO59L]

what did you do with the code?

 

[SteveO59L]

I you send me a pm with your e-mail address. I'll send you the worked sample