apply same formula for a set of two consecutive rows

[cbk40060]

I am using the CONCATENATE function to combine the data of two columns.
But the same formula should be applied for the first two rows and the next series of the formula should be assigned to the next two rows.
I think I can explain it better with the following table:
[TABLE="class: grid, width: 900, align: center"]
<tbody>[TR]
[TD="colspan: 3"]Particulars of the travel :[/TD]
[/TR]
[TR]
[TD]Date of travel :[/TD]
[TD]29-08-2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Sl.No.
(Column 'B")[/TD]
[TD] Place_Code
(Column 'C")[/TD]
[TD]Transaction description
(Column 'D")[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]=CONCATENATE("CUSTOMER TRAVELLED FROM ",C7," TO ",C8," ON ",TEXT(C4,"dd-mm-yyyy"))[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]=CONCATENATE("CUSTOMER TRAVELLED FROM ",C7," TO ",C8," ON ",TEXT(C4,"dd-mm-yyyy"))[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]=CONCATENATE("CUSTOMER TRAVELLED FROM ",C9," TO ",C10," ON ",TEXT(C4,"dd-mm-yyyy"))[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]=CONCATENATE("CUSTOMER TRAVELLED FROM ",C9," TO ",C10," ON ",TEXT(C4,"dd-mm-yyyy"))[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]=CONCATENATE("CUSTOMER TRAVELLED FROM ",C11," TO ",C12," ON ",TEXT(C4,"dd-mm-yyyy"))[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]=CONCATENATE("CUSTOMER TRAVELLED FROM ",C11," TO ",C12," ON ",TEXT(C4,"dd-mm-yyyy"))[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]=CONCATENATE("CUSTOMER TRAVELLED FROM ",C13," TO ",C14," ON ",TEXT(C4,"dd-mm-yyyy"))[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]=CONCATENATE("CUSTOMER TRAVELLED FROM ",C13," TO ",C14," ON ",TEXT(C4,"dd-mm-yyyy"))[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]=CONCATENATE("CUSTOMER TRAVELLED FROM ",C15," TO ",C16," ON ",TEXT(C4,"dd-mm-yyyy"))[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]=CONCATENATE("CUSTOMER TRAVELLED FROM ",C15," TO ",C16," ON ",TEXT(C4,"dd-mm-yyyy"))[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

In the above table, 'Sl.No.' is in column 'B', 'Place_Code' is in column 'C' and "Transaction description' is in column 'D".

In the above table, the third column (column 'D'), shows the output required by me.

I can get the result by manually applying the formula.

But since I have to work with many rows, I am looking for a macro to do the work for me
.
I wish I could have attached the sample excel file.

Any help in this connection, will be very much appreciated.

- CHANDRASHEKHAR

 

[jtakw]

I have to do this for about 1000 rows and I want to accomplish this using a macro.
I am not able to make out how I have to put this code snippet in a macro, when the starting row is a even row.

I actually left the house to go somewhere, but came back in to answer your question.

To be honest, 1000 rows isn't "that" many rows to scroll down using my method above, it'll probably take just a minute.
And why are you insisting on a macro???

You can do this:

With your "identical" formula in D1 and D2 already in place.
Select BOTH D1 and D2, right click, Copy.
Hit F5 on keyboard, in the "Goto" dialogue box, enter D3:D1000, hold down "Shift", click OK
Now, click Control V, done.

That's as lazy as it gets.

 

[cbk40060]

Thanks jtakw.
But I have to do this on a daily basis and I have to use the output in another macro.
So, please help me with a macro for this task.