Anyone good with Excel?

[Efflixi Aduro]

Hey everyone. I have et another problem at work I was hoping someone here could solve my problem.

Okay so I have a list of thousands of names and addresses which are all in one collumn in different cells. For example:

Bob Smith
1000 Planeshift St.
Hydlaa, Ca 91201
John Smith
2000 Planeshift St.
Hydlaa, Ca 91201

And I need it split into 3 columns. Example:

Bob Smith | 1000 Planeshift St. | Hydlaa, CA 91201

Is there an automated way where it can take row 1 and keep it in place, move row 2 to column 2, row 3 to column 3 then just keep repeating that pattern?

Thanks for the help everyone.





And yes Hydlaa is located in California.

[Raa]

Maybe you should type the subjects in their correct columns...?

Dunno, everything's so complicated nowadays... Used to be so much easier.

[neko kyouran]

I've written simple programs that take the first line of a text file, add the second line of the text file to the first line, and make a new txt file with the two lines merged, and have it continue on so forth through the original text file.

(it's what i use to demonstrate simple use of files in writing programs to my students in the vb course I teach part time.)

I wouldn't think it'd be harder to do the same with an excel sheet.  just have to change a few things and what not.  unfortunately, my copy of visual studio 2k5 is for teaching purposes only, so I can't legally make you a copy of a program to do what you require. 

off the top of my head, I don't know of a way to move the lines just by using excel itself.

I do however offer you another solution to your problem.

if you have access installed, you can import the data into a db through access's wizard.  just signify to it that row 1 goes into field one of the new table, row 2 goes to field two, row three, to field three, and tell it it to repeat in that fashion, and whala. 

pretty sure that'll make it all organized.  afterwards, if you don't want an access db, you could export the db to excel through aces, again, telling it how you wanted the data formatted through the wizard and you have your excel sheet again.

but in the long run, i would think a db to hold that information would be much better suited for your needs.

oh, and sorry i never got back to you on the scanner software thing.  couldn't find it, and it kinda slipped my mind to reply to the thread til i was reading this thread. 

[Rizin]

Hey everyone. I have et another problem at work I was hoping someone here could solve my problem.

Okay so I have a list of thousands of names and addresses which are all in one collumn in different cells. For example:

Bob Smith
1000 Planeshift St.
Hydlaa, Ca 91201
John Smith
2000 Planeshift St.
Hydlaa, Ca 91201

And I need it split into 3 columns. Example:

Bob Smith | 1000 Planeshift St. | Hydlaa, CA 91201

Is there an automated way where it can take row 1 and keep it in place, move row 2 to column 2, row 3 to column 3 then just keep repeating that pattern?

Thanks for the help everyone.


And yes Hydlaa is located in California.

Let's say A1 is Bob Smith, in B1 type =A1, in C1 type =A2, in D1 type =A3

Now select range B1 to D3.

Use the fill handle to pull this formula and pattern down the page for as long as you have names.

Once all the data is pulled out, highlight columns B, C, and D and choose copy.

Right click in cell E1 and choose Paste Special, selecting the Values radio button from the first column, and click OK.

Delete columns A, B, C, and D.

Highlight the new A, B, and C columns.

Click sort to remove empty rows.

Done.

[steuben]

okay have a bit of an awkward solution but it looks like it works.
assuming the data is in column b and first line of data is row 1
first insert a coloum A and fill it with a series starting at 1 and incrementing by three. so it is 1,3,4,7,...

now for column c put in =INDIRECT("b"&a1)
column d =INDIRECT("b"&a1+1)
columb e =INDIRECT("b"&a1+2)

and fill down c,d, and e, for the full extent of data

copy out the columns

hmmm battling solutions.

[Robinmagus]

Efflixi, Hydlaa is in Glendale? It has the same zip if it's not   

[Meredee]

Hi,

You can solve this problem with a very simple macro, I wrote it for you:

------
Option Explicit

Sub Hydlaa()

    Dim i As Integer
    Dim shAkt As Worksheet
   
    Set shAkt = ActiveSheet
   
    For i = 1 To shAkt.UsedRange.Rows.Count + 5
        shAkt.Cells(i, 2) = shAkt.Cells(i + 1, 1)
        shAkt.Cells(i, 3) = shAkt.Cells(i + 2, 1)
        shAkt.Rows(i + 1).EntireRow.Delete
        shAkt.Rows(i + 1).EntireRow.Delete
    Next i

End Sub
------

Open Excel, press Alt + F11 to open VBA Editor, create new module (on the top: Insert >> Module), copy and paste this code there, select the worksheet that you want to convert and activate macro (place cursor inside of the macro, press F5). The changes will be irreversible so you may want to copy your data somewhere before running the macro on them Let me know if it worked.
Btw programming is fun to learn and it's not that difficult as many people think. And programming in VBA can be very useful during studies or at work

[Efflixi Aduro]

Efflixi, Hydlaa is in Glendale? It has the same zip if it's not   

Ya actually. 

I'll try out all these solutions guys thank you once again for the help. PS forums are more helpful than the Excel forums I've been going to. 

[potatoehead64]

alternatively you could copy/paste the data to a word doc and re copy it to excel. I think it places everything with gaps in separate cell columns by default.