scooby_simon Posted March 20, 2009 Report Share Posted March 20, 2009 Chaps and chapesses I'm sure I have done this before, but I cannot remember how.... I have a single column of data that looks simething like this: Fred 31 Jim 12 Sally 32 erin 31 peter 11 I would like to display this as 2 cols (name and age) Fred 31 Jim 12 sally 32 Erin 31 Peter 11 etc I cannot for th life of me remember how to do it easily! Link to comment Share on other sites More sharing options...
Hopsta Posted March 20, 2009 Report Share Posted March 20, 2009 Check concantenate, i know that splits but may have a related link Link to comment Share on other sites More sharing options...
Busby Posted March 20, 2009 Report Share Posted March 20, 2009 I think concatenate splits things in a single cell. Might be easier to do it with an "IF" formula to recognise any number and place it accordingly. Link to comment Share on other sites More sharing options...
Pincher Posted March 20, 2009 Report Share Posted March 20, 2009 (edited) I think you want to transpose? To get two columns though, you may need to do it bit by bit i.e. highlight Fred & 31, then copy, paste special and tick the transpose box and repeat until bored. Edited March 20, 2009 by Pincher Link to comment Share on other sites More sharing options...
scooby_simon Posted March 20, 2009 Author Report Share Posted March 20, 2009 concatinate works, but it's not ocopy/pasteable =CONCATENATE(A1,"",A2) gives Fred 31 But When you copy it and paste you get =CONCATENATE(A2,"",A3) which gives 31 Jim which is not quite right!!!! I'll keep looking Link to comment Share on other sites More sharing options...
Mook Posted March 20, 2009 Report Share Posted March 20, 2009 Copy both cells with the formula in, then move down two cells and paste, surely? I know, don't call you Shirley... Link to comment Share on other sites More sharing options...
scooby_simon Posted March 20, 2009 Author Report Share Posted March 20, 2009 Copy both cells with the formula in, then move down two cells and paste, surely?I know, don't call you Shirley... But then I have to get rid of the blank lines. I would like to do it all in one hit. Link to comment Share on other sites More sharing options...
Mook Posted March 20, 2009 Report Share Posted March 20, 2009 Don't worry about blank lines - once you've extracted all the data, just do a column sort and all the blanks will be chucked to the bottom. Or better still, use the Autofilter feature. Job done Link to comment Share on other sites More sharing options...
scooby_simon Posted March 20, 2009 Author Report Share Posted March 20, 2009 Don't worry about blank lines - once you've extracted all the data, just do a column sort and all the blanks will be chucked to the bottom. Or better still, use the Autofilter feature.Job done Yep; I'm just lazy!!! Link to comment Share on other sites More sharing options...
T_Rifles Posted March 20, 2009 Report Share Posted March 20, 2009 (edited) If you put those values in column A, an index of numbers from 0-whatever you need in B and in C use =OFFSET(A1,B1,0) and in D =OFFSET(A1,B2,0). Select and drag this down until you reach the bottom of your list. Edited March 20, 2009 by T_Rifles Link to comment Share on other sites More sharing options...
Mook Posted March 23, 2009 Report Share Posted March 23, 2009 Holy smoke, T Rifles, that properly works :D Top post Link to comment Share on other sites More sharing options...
scooby_simon Posted March 23, 2009 Author Report Share Posted March 23, 2009 If you put those values in column A, an index of numbers from 0-whatever you need in B and in C use =OFFSET(A1,B1,0) and in D =OFFSET(A1,B2,0). Select and drag this down until you reach the bottom of your list. Excellent; thanks Link to comment Share on other sites More sharing options...
Recommended Posts
Please sign in to comment
You will be able to leave a comment after signing in
Sign In Now