Hi,

Am an excel novice in many respects so this one is a bit beyond me.

I am creating a spreadsheet that will be flawed because of the format
of the data I will be using, but hopefully it will be capable of being
tidied up.

The spreadsheet will have three columns. In the first row it will have
a name in one cell, then a street name and number in the second cell
and a telephone number in the third.

In the second row there will be nothing in the first cell, the town in
the second cell and nothing in the third cell.

The third row will be blank.

So my question is, how do I set up some sort of repeating or looping
function to apply to the whole database? It would need to cut the town
from its cell in each instance, paste it to a new column alongside the
street name and number and delete the newly created blank row and the
existing blank row underneath it.

Thanks in anticipation.

T


--
Trickster
------------------------------------------------------------------------
Trickster's Profile: http://www.excelforum.com/member.php...o&userid=25374
View this thread: http://www.excelforum.com/showthread...hreadid=388553

One interp, and play ..

Assume your data looks like this, in cols A to D, from row1 down

Name1 Str1 Tel1
-----------Twn1
<blank
Name2 Str2 Tel2
-----------Twn2
<blank
Name3 Str3 Tel3
-----------Twn3
<blank
etc

Insert a new col to the left of col D (the "Tel" col)
(to accomodate the extract of: Twn1, Twn2 .. next to Str1, Str2)

In the new col C

Put in C1:
=IF(MOD(ROWS($A$1:A1)-1,3)=0,OFFSET(B1,1,),"")
Copy C1 down until the last row of data

Col C will copy: Twn1, Twn2, etc
aligned with/next to Str1, Str2, etc

Kill the formulas in col C
with an "in-place" copy paste special values ok

Insert a new row1, enter a label into C1
Select C1, click Data Filter Autofilter
Select (Blanks) from the autofilter droplist in C1
(This will filter out blank rows in col C)

Select all the filtered rows (select all the "blue" row headers)
Right-click on selection choose "Delete Rows"
Remove the autofilter

You'd get the desired results in row2 down:

Name1 Str1 Twn1 Tel1
Name2 Str2 Twn2 Tel2
Name3 Str3 Twn3 Tel3
etc

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Trickster" wrote
in message ...

Hi,

Am an excel novice in many respects so this one is a bit beyond me.

I am creating a spreadsheet that will be flawed because of the format
of the data I will be using, but hopefully it will be capable of being
tidied up.

The spreadsheet will have three columns. In the first row it will have
a name in one cell, then a street name and number in the second cell
and a telephone number in the third.

In the second row there will be nothing in the first cell, the town in
the second cell and nothing in the third cell.

The third row will be blank.

So my question is, how do I set up some sort of repeating or looping
function to apply to the whole database? It would need to cut the town
from its cell in each instance, paste it to a new column alongside the
street name and number and delete the newly created blank row and the
existing blank row underneath it.

Thanks in anticipation.

T


--
Trickster
------------------------------------------------------------------------
Trickster's Profile:
http://www.excelforum.com/member.php...o&userid=25374
View this thread: http://www.excelforum.com/showthread...hreadid=388553

Some typos, sorry:

Assume your data looks like this, in cols A to D, from row1 down
should read as:
Assume your data looks like this, in cols A to C, from row1 down

Insert a new col to the left of col D (the "Tel" col)
should read as:
Insert a new col to the left of col C (the "Tel" col)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Great - many thanks!


--
Trickster
------------------------------------------------------------------------
Trickster's Profile: http://www.excelforum.com/member.php...o&userid=25374
View this thread: http://www.excelforum.com/showthread...hreadid=388553

You're welcome !
Thanks for the feedback
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Trickster" wrote
in message ...

Great - many thanks!