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:
Particulars of the travel :
Date of travel :29-08-2018
Sl.No.
(Column 'B")
Place_Code
(Column 'C")
Transaction description
(Column 'D")
1A=CONCATENATE("CUSTOMER TRAVELLED FROM ",C7," TO ",C8," ON ",TEXT(C4,"dd-mm-yyyy"))
2B=CONCATENATE("CUSTOMER TRAVELLED FROM ",C7," TO ",C8," ON ",TEXT(C4,"dd-mm-yyyy"))
3C=CONCATENATE("CUSTOMER TRAVELLED FROM ",C9," TO ",C10," ON ",TEXT(C4,"dd-mm-yyyy"))
4D=CONCATENATE("CUSTOMER TRAVELLED FROM ",C9," TO ",C10," ON ",TEXT(C4,"dd-mm-yyyy"))
5E=CONCATENATE("CUSTOMER TRAVELLED FROM ",C11," TO ",C12," ON ",TEXT(C4,"dd-mm-yyyy"))
6F=CONCATENATE("CUSTOMER TRAVELLED FROM ",C11," TO ",C12," ON ",TEXT(C4,"dd-mm-yyyy"))
7G=CONCATENATE("CUSTOMER TRAVELLED FROM ",C13," TO ",C14," ON ",TEXT(C4,"dd-mm-yyyy"))
8H=CONCATENATE("CUSTOMER TRAVELLED FROM ",C13," TO ",C14," ON ",TEXT(C4,"dd-mm-yyyy"))
9I=CONCATENATE("CUSTOMER TRAVELLED FROM ",C15," TO ",C16," ON ",TEXT(C4,"dd-mm-yyyy"))
10J=CONCATENATE("CUSTOMER TRAVELLED FROM ",C15," TO ",C16," ON ",TEXT(C4,"dd-mm-yyyy"))
11K
12L
13M
14N

<tbody>
</tbody>

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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Code:
=CONCATENATE("CUSTOMER TRAVELLED FROM ",INDIRECT("C" & MROUND(ROW(),2)-1)," TO ",INDIRECT("C" & MROUND(ROW(),2))," ON ",TEXT($C$4,"dd-mm-yyyy"))
 
Upvote 0
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.
 
Upvote 0
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

Rich (BB code):
=SUM(A1,B2:B7)

to read:

Rich (BB code):
=SUM(INDIRECT("A1"),INDIRECT("B2:B7"))

No real reason to, but I could.

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

Code:
[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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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. :p
 
Upvote 0
By tinkering with the first bit and the second bit, I came up the following code:

First Bit CodeSecond Bit CodeFirst bit OutputSecond Bit Output
MROUND(ROW(),2)MROUND(ROW(),2)+1 8 9
MROUND(ROW(),2)-2MROUND(ROW(),2)-1 8 9
MROUND(ROW(),2)MROUND(ROW(),2)+1 1011
MROUND(ROW(),2)-2MROUND(ROW(),2)-1 1011

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

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