insert rows based on cell value or cut copy paset based on the another column ce

[dvsdasari]

Hi,


I have data in on worksheet as following..

columnA columnB

1 11 test1

2 test2

3 13 test3

4

5

6

7 14


The result should be following manner..


columnA columnB

1 11 test1

2 test1

3 13 test2

4 test2

5 test2

6 test2

7 14 test3


Please help me, i am struglling to solve this since one day but i didn't get any solution for this.


I'm verythankful to you poeple i can get solution from here.


Thanks in advance.

Sekahr

 

[SirJB7]

Hi, dvsdasari!


Try using two helper columns, let's say C & D.

In C1 type and copy down as required:

=CONTAR.SI(A$1:A1;">0") -----> in english: =COUNTIF(A$1:A1,">0")

Same for D1:

=SI(ESNUMERO(A1);"test"&C1;SI(NO(ESNUMERO(A2));BUSCARV(C1;C:D;2;FALSO);"")) -----> in english: =IF(ISNUMBER(A1),"test"&C1,IF(NOT(ISNUMBER(A2)),VLOOKUP(C1,C:D,2,FALSE),""))


Regards!