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

 

[Greg Truby]

=CONCATENATE("CUSTOMER TRAVELLED FROM ",INDIRECT("C" & MROUND(ROW(),2)-1)," TO ",INDIRECT("C" & MROUND(ROW(),2))," ON ",TEXT($C$4,"dd-mm-yyyy"))

 

[cbk40060]

GREG thanks for the help.
But I did not understand the INDIRECT and MROUND functions.
Will you please explain me the code.
Sorry I am troubling you a lot.

 

[Greg Truby]

INDIRECT() and MROUND() are NOT "code" in the sense that they have nothing to do with VBA. They're just regular old Excel functions. What I gave you was the function to drop into the cell (D7?) and then autofill downwards.

I only put CODE tags around the formula because once in a while vBulletin's software parses out backslashes or other key bits and pieces of things one posts unless they're wrapped in CODE tags. It's just an old habit.

As for INDIRECT() — this function simply takes some expression and treats it like an address or name. For example I could rewrite the formula

=SUM(A1,B2:B7)

to read:

=SUM(INDIRECT("A1"),INDIRECT("B2:B7"))

No real reason to, but I could.

So, essentially you could read the first INDIRECT like so:

[COLOR=#0000ff]INDIRECT("C" & if I'm on an odd row, the row I'm on and if I'm on an even row, the row above me)[/COLOR]

And then the MROUND() function is the bit that rounds the row up to the next multiple of two and then subtracts one (for the first INDIRECT) thereby 7,7 then 9,9 then 11,11 etc. as we continue down the column.

 

[cbk40060]

Thanks again GREG.

But if the starting row is a even row, then how I have to modify the code?

 

[cbk40060]

Thanks a lot GREG.

The code is working wonderfully.

But I was just curious to know as to, if the starting row is a even row, then how I have to modify the code.

 

[Greg Truby]

Rather than just tell you (giving you a fish). I'm going to tell you how to figure it out for yourself (teach you how to fish).

First of all, on the Formulas tab there is an Evaluate Formula button. Try clicking in a cell and then using this tool to step through the formula to see how it comes together.

After you've done that a couple of times, you'll see that the key bits to the formula are what I've underlined below:

=CONCATENATE("CUSTOMER TRAVELLED FROM ",INDIRECT("C" & MROUND(ROW(),2)-1)," TO ",INDIRECT("C" & MROUND(ROW(),2))," ON ",TEXT($C$4,"dd-mm-yyyy"))

So take these two key bits and copy just those bits into some spare columns and see what you're getting... for rows 7 and 8 the first bit will return 7 and 7 while the second bit will return 8 and 8. For rows 9 and 10 the first bit yields 9 & 9 while the second = 10 and 10. Now try tinkering with these bits until for rows 8 and 9 the first bit returns 8 and 8 and the second bit returns 9 and 9; and for rows 10 and 11 the first bit yields 10 and 10 while the second yields 11 and 11. Once you've got these bits working this way, then try re-inserting your new and improved bits into the original formula. If it doesn't yield the results you expect, use the Evaluate Formula tool to see what's going on.

Once you've got it working, the next step is to figure out the appropriate side dishes and beverage to accompany your catch of fish.

 

[cbk40060]

By tinkering with the first bit and the second bit, I came up the following code:

[TABLE="width: 571"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]First Bit Code[/TD]
[TD]Second Bit Code[/TD]
[TD]First bit Output[/TD]
[TD]Second Bit Output[/TD]
[/TR]
[TR]
[TD]MROUND(ROW(),2)[/TD]
[TD]MROUND(ROW(),2)+1[/TD]
[TD] 8 [/TD]
[TD] 9[/TD]
[/TR]
[TR]
[TD]MROUND(ROW(),2)-2[/TD]
[TD]MROUND(ROW(),2)-1[/TD]
[TD] 8[/TD]
[TD] 9[/TD]
[/TR]
[TR]
[TD]MROUND(ROW(),2)[/TD]
[TD]MROUND(ROW(),2)+1[/TD]
[TD] 10[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]MROUND(ROW(),2)-2[/TD]
[TD]MROUND(ROW(),2)-1[/TD]
[TD] 10[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]

But I am not able to make out, how I have to use these codes for my task.
I want to use these bits of code, when the starting row is a even row.
I also want to put this code in a macro.
Please guide me.

 

[jtakw]

Hi,

You can accomplish this with just your original formula as posted in Post # 1 (No adjustment to your formula needed, and no New formula needed).

With your "identical" formula in D1 and D2 already in place.
Select BOTH D1 and D2, right click, Copy.
Now select D3 down as far as you need, but make sure the number of rows you select are Even (NOT the row numbers, but the number of Rows, i.e. 10 rows, 20 rows, 100 rows, etc.)
Now right click, paste Formula, you're done.

 

[cbk40060]

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.