Replace the value of row to next row of value if meet any condition

Copper Contributor



Hey excel family. I am stuck in this issue while summarizing report.

What in the sheet is first row is the customer data with its transactions values. Now below the customer there is a bill change column means for that customer their bill is changed for some reason and their values in written below that row (ignore yellow highlighted row). If you see the second customer there is no bill change but if you go down again you will see customer 3 their bill is changed so i want to replace only that customer transactions values where they have any bill changed else others will remain same. How can i solve this issue?

2 Replies
best response confirmed by kabeerkhann (Copper Contributor)



A simple solution could be with this formula in cell K2 filled across range K2:N18 in the example.

=IF(AND(ISNUMBER(SEARCH("bill change",$B3)),NOT(ISBLANK($A2))),E4,IF(AND(ISNUMBER(SEARCH("bill made",$B3)),NOT(ISBLANK($A2))),E2,""))

This formula is in cell O2 and filled across range O2:S18.

bill change.png

After this you can copy the result and paste only values and then filter and delete the extra blank rows if required.


Thank you sir i got my answer