I need a formula for excel 2007

[colwyn]

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.

 

[Mike H]

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

 

[Roger Govier]

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 C>Paste Special>Values 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 B>F5>Special>Blanks>OK>press up arrow>type = and press up
arrow>Control+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 Special>Values
back over the original data.

 

[Bob Phillips]

AS posted at ExcelForum

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

 

[colwyn]

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.

 

[Roger Govier]

Hi
Your explanation was clear the first time.
Did you not try any of the solutions provided?

 

[colwyn]

Roger, they didn't work.

 

[Bob Phillips]

In what way?

--
__________________________________
HTH

Bob

Roger, they didn't work.