Really Awkward List

Posted by Marcel Lindsay on March 28, 2001 1:42 AM

I have a long list of names with personal details all in one column with each person having a different amount of lines of data ie the list starts with a persons name then their details which can take up the next 20 to 25 cells directly underneath depending on what fields they filled out.

I need to put the names into columns without having to transpose them manually (there are thousands of the feckers)

The only things that seperates the list is the persons name is the starting point and that has a space before the name

Does anyone have any suggestions

Cheers

Posted by mseyf on March 28, 2001 5:33 AM

Marcel:

if the data starts in A1, put the following formula into B1:

=IF(LEFT(A1,1)=" ",A1,"")

and copy down. This should pull over all the cells that start with a space. You can tne Copy>Paste Special>Values the new range and sort to get rid of the blank cells.

If you want to get rid of the leading space at the same time, change the formula to:

=IF(LEFT(A1,1)=" ",TRIM(A1),"")

HTH

Mark

Posted by Marcel Lindsay on March 28, 2001 6:21 AM

Cheers mark for that but that only gets me the name, I need the rest of the information to fill the other cells and then go to a new line when it hits a new first name and fill right again so I am left with a spread sheet that has Name address1 date of bith etc

Hope this is clearer !!!

Posted by Dave Hawley on March 28, 2001 1:29 PM

Hi Marcel

You are going to be needing a macro for this. Try this one below. It assumes your list is in Column A and will Transpose each block into columns, starting from C1 of the same sheet.

Sub AutoTranspose()
Dim MyData As Range, i As Integer

Set MyData = Columns(1).SpecialCells(xlCellTypeConstants)

For i = 1 To MyData.Areas.Count
MyData.Areas(i).Copy
Cells(i, 3).PasteSpecial Transpose:=True
Next i

End Sub

If you need any further help, just shout.

Dave

OzGrid Business Applications

Posted by Ian on March 28, 2001 2:30 PM

Text to Columns?

I may not be getting fully, as often is my way. But if you have a bunch of data all in one cell, you could try highlighting the column and selecting "text to columns" off the data menu. Select Deliminated and click next. For deliminaters, click space. Click next and finally finish.
One of perhaps a few downsides of this will be address like 123 W Maple will appear in three columns. You could concecate this after the fact. It also would have a much better shot of working if all the data is the same order if each person.