apply same formula for a set of two consecutive rows

cbk40060

New Member
Joined
Jul 5, 2018
Messages
13
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
 
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. :)
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top