Sort Data from one column to Separate Columns

If your Names & Age are all seperated by one row,
try this:

Your Data in column A

A
1) First Hugh
2) Last Jass
3) Age 24
4)
5) First Amanda
6) Last HugnKis
7) Age 41


In cell B1 enter the formula: =RIGHT(A1,LEN(A1)-FIND(" ",A1))
In cell C1 enter the formula: =RIGHT(A2,LEN(A2)-FIND(" ",A2))
In cell D1 enter the formula: =RIGHT(A3,LEN(A3)-FIND(" ",A3))

Now select the range of cells B1 through D4

In the lower right corner of your selected range is a small black square, grap this square and drag down, this will copy the formulas and the three blank lines.

This should get you what you want.

Thank xenixman

Unfortuantly my initial post doesn't really show the layout properly and after trying your formula I couldn't get it to work in this case.

The top 2/3 of the attachment is an example of the current layout information in seperate columns. It repeats the same format continuously down to about 800 (stupid and annoying).

The bottom 3 columns is how I would like it to look. Removing the first column all together (making it the column headings) and just having an easy to use table, that I can use as I wish with (vlookups, filters,pivot tables etc)

There is more information invloved but provided a formula to work in this case, should mean I can apply it to the full table. I am not a total novice like I implied, normally I can work these things out but this has me stumped. Everything I try returns jumbled columns, #ref or #value as results.

could a Macro be more effective in this instance?

Thanks

Actually I guess my main problem is to skip cells that do not relate to the columns I have created. So say the First column relates to every 6th row in the initial date set.

Something in regard to IF A1 is First name return B1? However because the the rows alternate I get this to work but in my new table the First name column has data in rows 1,6,12, the Last name column has data in rows 2,7,13 etc.. How can I get it to line up?

I'm not sure what it is you want, but from the looks of your picture, the same logic applies, just modify the formulas a bit:

H I
3) First Hugh
4) Last Jass
5) Age 24
6)
7) First Amanda
8) Last HugnKis
9) Age 41

In cell J3 enter the formula: =I3
In cell K3 enter the formula: =I4
In cell L3 enter the formula: =I5

Now select the range of cells J3 through L6
or if there are two spaces between sections, J3 through L7
In the lower right corner of your selected range is a small black square, grap this square and drag down, this will copy the formulas and the three or four blank lines.
Now you can do a copy & past special, values and delete the original configuration.

thanks mate, I wasn't selecting the blank cells in between and therefore it was putting the values out of sync.

I was hoping to avoid doing this way though, so I didn't have to delete all the blank cells in between. Really wanted to have the formula to select every cell that related to the first name without a gap of 7 or 8 cells in between. But all good I can fix it with a filter and work out a better way of doing it at a later stage.

Thanks.

Alright my thoughts over night have provided this

In the first column (J3): =IF(H3="First",I3,"")
Next column (K3): =IF(H3="First",I4,"").

So on across the columns. The once it is dragged down the page the formula only returns values when there is information. Filtering out the blanks leaves me with the data I want. Saves you having to select from J3 to L6.

Any Ideas on how to get it to disregard the blank cells so there is no need for a filter would be nice, because I have other sheets with the similar situations.

Cheers for the help

Sweet

Thanks