Sorry, I explained this slightly wrong. Please read following for
better explanation of what I'm after.

I have Excel 2007.

There is one sheet with 2 columns of data.

Column one has a series of numbers (e.g, 111,22,3333,etc).
Column 2 has a series of terms (e.g, work,gym,home,etc).

My problem is: whereas there is a series of numbers in column 1, the
terms in column 2 only appear on the first row of each of these column
1 series.

1 work
1
1

1
1
1
1

2 gym
2

2
2
2
2

3 shop
3
3
3

3
3

I want the terms to appear every time alongside each column 1 number.

1 work
1 work
1 work

1 work
1 work
1 work
1 work

2 gym
2 gym

2 gym
2 gym
2 gym
2 gym

3 shop
3 shop
3 shop
3 shop

3 shop
3 shop

Can anyone supply a formula which I can put in column 3 which I can
fill down to provide the desired effect??
Big thanks.
Colwyn.

Assume source data as posted in A1:B1 down
In C1: =B1
In C2, copied down: =IF(B2="",C1,B2)
Then in D1, copied down: =IF(A1="","",C1)
Required result in col D
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---
"colwyn" wrote in message
...
Sorry, I explained this slightly wrong. Please read following for
better explanation of what I'm after.

I have Excel 2007.

There is one sheet with 2 columns of data.

Column one has a series of numbers (e.g, 111,22,3333,etc).
Column 2 has a series of terms (e.g, work,gym,home,etc).

My problem is: whereas there is a series of numbers in column 1, the
terms in column 2 only appear on the first row of each of these column
1 series.

1 work
1
1

1
1
1
1

2 gym
2

2
2
2
2

3 shop
3
3
3

3
3

I want the terms to appear every time alongside each column 1 number.

1 work
1 work
1 work

1 work
1 work
1 work
1 work

2 gym
2 gym

2 gym
2 gym
2 gym
2 gym

3 shop
3 shop
3 shop
3 shop

3 shop
3 shop

Can anyone supply a formula which I can put in column 3 which I can
fill down to provide the desired effect??
Big thanks.
Colwyn.

Hi,

Turn on Auto Filters, Filter column A for all Non blanks. Highlight the
column B data and press F5, Special, Blanks. With all the blanks selected
type
=
Up Arrow
and press Ctrl Enter
(this means type = to start a formula, press the up arrow key once, then
press Ctrl Enter).
Select all the cells in column B and choose Copy, Paste Special, Values.

Even if you don't need this technique here, it is well worth learning!


--
Thanks,
Shane Devenshire


"colwyn" wrote:

Sorry, I explained this slightly wrong. Please read following for
better explanation of what I'm after.

I have Excel 2007.

There is one sheet with 2 columns of data.

Column one has a series of numbers (e.g, 111,22,3333,etc).
Column 2 has a series of terms (e.g, work,gym,home,etc).

My problem is: whereas there is a series of numbers in column 1, the
terms in column 2 only appear on the first row of each of these column
1 series.

1 work
1
1

1
1
1
1

2 gym
2

2
2
2
2

3 shop
3
3
3

3
3

I want the terms to appear every time alongside each column 1 number.

1 work
1 work
1 work

1 work
1 work
1 work
1 work

2 gym
2 gym

2 gym
2 gym
2 gym
2 gym

3 shop
3 shop
3 shop
3 shop

3 shop
3 shop

Can anyone supply a formula which I can put in column 3 which I can
fill down to provide the desired effect??
Big thanks.
Colwyn.