Setting up some sort of cutting and pasting loop

T

Trickster

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
 
M

Max

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
 
M

Max

Some typos, sorry:
Assume your data looks like this, in cols A to D, from row1 down
Click to expand...
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)
Click to expand...
should read as:
Insert a new col to the left of col C (the "Tel" col)
 
Top