[Suvaxal]

I have a question regarding excel. My girlfriend has to change all addresses on this list to a horizontal way. This is the original list: http://www.vzls.ch/fr/mitgl_alpha.html

The way it should be done is like this:

Zahntechnische Werkstatt GmbH Hinterdorf 106 7220 Schiers / GR 081 330 44 77 081 330 44 78 zahnwerkstatt@bluewin.ch

So the whole thing should be on one line. I know the transpose function, but the other addresses need to be on different rows and when I transpose all it will all be on a horizontal line.

[Ervntewc]

I have a question regarding excel. My girlfriend has to change all addresses on this list to a horizontal way. This is the original list: http://www.vzls.ch/fr/mitgl_alpha.html

The way it should be done is like this:

Zahntechnische Werkstatt GmbH Hinterdorf 106 7220 Schiers / GR 081 330 44 77 081 330 44 78 zahnwerkstatt@bluewin.ch

So the whole thing should be on one line. I know the transpose function, but the other addresses need to be on different rows and when I transpose all it will all be on a horizontal line.

Use a macro to transpose one entry (four colums) at a time (1 column with four rows into 4 colums with 1 row)

Something like:

intCell = 2
While (Range("A" & intCell).value "")
....

Bah, just got called into a meeting. I will come back here to help if you still need it. Sorry

[Suvaxal]

I still need help for this.. changing 450 addresses is just a big hassle

[Ervntewc]

Alright, my friend, are you familiar with VBA or writing macros in Excel?

If so, I can write something really grimy up for you and you can fix it however you see fit. Sound good? I will just email/FTP you the file. Should not take long.

[AlbrtJhnsqw]

Do you have them in an Excel file already, or are they being copied from the website?

You need to give us more information about how they are stored in the spreadsheet i.e. are there 6 entries in a single column for the 6 data items (cells A1 to A6 for example).

[Suvaxal]

I myself am not really familiar with excel but I have an idea what macros are.

All the addresses that are on the website I posted have to be inserted in an excel spreadsheet. The way it should be done is all horizontal.

Instead of having data on different rows it should all be on one row next to eachother.

Example:

Company Name, Owner Name, Adress, Telephone Number, Fax, Email, Website Link

All on one row and no different columns..

thx for the help

[Ervntewc]

So, all in one column? Just delimited by comma.

[AlbrtJhnsqw]

Pulling the information from HTML to the format you specify is a completely different world to transposing data items already within a spreadsheet.

Is there no database with the originals in?

[Ervntewc]

Pulling the information from HTML to the format you specify is a completely different world to transposing data items already within a spreadsheet.

Is there no database with the originals in?

Yeah, the raw data would be super easy, but this is not bad so long as the OP describes the job thoroughly.

[Suvaxal]

unfortunately not.. I guess my girlfriend just has to do one address at a time..

Ooh and not all in 1 column.. Everything that's seperated by a coma in a different column.. All just on 1 row..

right now she's using the transpose function, but 450 addresses take a looong time..

[77chawzence]

You can record a macro for one line and then repeat it for all others

search macro in the online help

[Ervntewc]

unfortunately not.. I guess my girlfriend just has to do one address at a time..

Ooh and not all in 1 column.. Everything that's seperated by a coma in a different column.. All just on 1 row..

right now she's using the transpose function, but 450 addresses take a looong time..

Tell her to stop. I am almost done.

I am writing this assuming that you just copy and pasted the entire HTML table into Excel starting at row 2.

[Suvaxal]

example:

http://img141.imageshack.us/my.php?image=excel.jpg

[Suvaxal]

Tell her to stop. I am almost done.

I am writing this assuming that you just copy and pasted the entire HTML table into Excel starting at row 2.

She did something like that yes... wondering how can do it that fast... thx though

[Ervntewc]

DONE!!!! Give me an email addy or something to send it to you.

I will post the code here in a bit.

[Suvaxal]

thx I sent you a pm