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

2 gym
2

3 shop
3
3
3

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

1 work
1 work
1 work

2 gym
2 gym

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,

You need a helper column.

In C1 enter the formula =B1
In C2 enter the formula
=IF(A2=A1,C1,B2)

Drag down as required.

Hide column B. Alternatively, copy Column C and paste it back over itself
using
Paste Special - paste values and then delete Column B

Mike

"colwyn" wrote:

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

2 gym
2

3 shop
3
3
3

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

1 work
1 work
1 work

2 gym
2 gym

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

With Row 1 being blank, and your data starting in A2, enter this formula in
C2
=IF(B2<"",B2,IF(A2=A1,C1,""))
Copy down as far as required.
Copy column CPaste SpecialValues back over column B
Column C can then be deleted.

Your sample data shows blank rows between each set of data. If that is only
to highlight the example posted, and the blanks do not exist in reality,
then you can achieve your desired result without formulae.

Highlight column BF5SpecialBlanksOKpress up arrowtype = and press up
arrowControl+Enter
All of the blank cells will be filled with the values from the cell above.
Once again, if you want to "fix" the data, Copy and paste SpecialValues
back over the original data.
--
Regards
Roger Govier

"colwyn" wrote in message
...
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

2 gym
2

3 shop
3
3
3

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

1 work
1 work
1 work

2 gym
2 gym

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.

AS posted at ExcelForum

=IF(A3=A2,C2,"")

--
__________________________________
HTH

Bob

"colwyn" wrote in message
...
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

2 gym
2

3 shop
3
3
3

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

1 work
1 work
1 work

2 gym
2 gym

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.

On Oct 20, 11:22*am, colwyn wrote:
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

2 *gym
2

3 *shop
3
3
3

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

1 *work
1 *work
1 *work

2 *gym
2 *gym

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.



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
Your explanation was clear the first time.
Did you not try any of the solutions provided?

--
Regards
Roger Govier

Roger, they didn't work.


On Oct 20, 3:54*pm, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote:
Hi
Your explanation was clear the first time.
Did you not try any of the solutions provided?

--
Regards
Roger Govier

In what way?

--
__________________________________
HTH

Bob

"colwyn" wrote in message
...
Roger, they didn't work.


On Oct 20, 3:54 pm, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote:
Hi
Your explanation was clear the first time.
Did you not try any of the solutions provided?

--
Regards
Roger Govier