transfer of row data to column

[anandvarma]

dear all i have data set like this


b5 Staff Welfare
b6 Primary Cost Category
b7 RAIL - 1053
b8 Cash
b9 Staff Welfare
b10 Primary Cost Category
b11 RAIL - 1007

i need to transfer cell contents after Primary Cost Category i.e.B7 to C5

this sheet contain 8000 rows

help me
pvarma

 

[anandvarma]

Dear all

I could able to workout using this formula

=IF(INDIRECT("B"&ROW()+1)="Primary Cost Category",INDIRECT("B"&ROW()+2),0)

can any body suggest better way for this

pvarma

 

[Sektor]

Is "b6 Primary Cost Category" in one cell?

 

[anandvarma]

the cell b7 contains the text Premiary cost category

 

[Useful]

Hi! try this, hope it'll work
=IF(B6="Primary Cost Category";B7;"")

 

[anandvarma]

thank you for the interest shown

This formula have to be copies to 8000 rows. it should not be static, i have already posted the solution. can you update that with better one

 

[Useful]

Hi again!
Have you inserted the formula from C5? (I think it works as required and not static)
Also you may use this VBA code

Sub find()
Dim a As Integer
For a = 5 To 8000
If Cells(a, 2).Value = "Primary Cost Category" Then
Cells(a, 2).Offset(-1, 1).Value = Cells(a, 2).Offset(1).Value
End If
Next a
End Sub

Hope this helps

 

[anandvarma]

it is not working giving Name# error

 

[Useful]

it is not working giving Name# error

Why? I'm not sure that ur doing right steps. Both methods works correctly. Chek it again my be u'll find the reason