Concatenate Moving Range

citadelr

New Member
Joined
Sep 12, 2018
Messages
2
Hi there!


Been a long time lurker of the forum, but finally forced to ask for help on something

I have a column with values e.g. in B1 I have "red", B2 has "Blue", B3 has "Green" etc.

In another cell I have a randbetween function e.g. C1 = randbetween(1,N)

I want to concatenate from cell B1 down to the row number randbetween function gives me. ie let C1=n, then I need to concatenate B1, B2, B3,...Bn

e.g If randbetween resolved to 2, I want to concatenate B1 and B2. If resolved to 3, I want to concetante B1, B2, B3 etc.

Any ideas on how I may do this?

Cheers all
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

=ODIN=

Active Member
Joined
Dec 3, 2009
Messages
288
That's a tough one. I'm interested to see if anyone else has a great idea. As far as I know, Excel's concat formulas do not accept ranges, so this will be difficult to pull off.

For me, I have an add-in called Nutilities installed that has a custom formula called "delimiter" http://www.iwishexcel.com/custom-formulas/delimiter/

With it installed, I can put this formula into a cell and do what you're describing:
Code:
=Delimiter(INDIRECT("b1:b"&C1),"","")

Be aware though! if you go this route, if you share the wordbook with anyone, the formula will error for them unless they have the same add-in installed.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,602
If you have Excel 365 with the CONCAT or TEXTJOIN functions, you can use the same idea as ODIN like this:

=CONCAT(OFFSET(B1,0,0,C1))

or

=TEXTJOIN(",",FALSE,OFFSET(B1,0,0,C1))


Failing that, you could use a helper column. In D1, put =B1. In D2, put =D1&B2 and drag that down the column. Then you could use this formula: =INDEX(D:D,C1)

Finally, you could write a VBA macro to do the same thing. So with ODIN's add-in, those are the only ways I can think of to accomplish your task.
 

citadelr

New Member
Joined
Sep 12, 2018
Messages
2
If you have Excel 365 with the CONCAT or TEXTJOIN functions, you can use the same idea as ODIN like this:

=CONCAT(OFFSET(B1,0,0,C1))

or

=TEXTJOIN(",",FALSE,OFFSET(B1,0,0,C1))


Failing that, you could use a helper column. In D1, put =B1. In D2, put =D1&B2 and drag that down the column. Then you could use this formula: =INDEX(D:D,C1)

Finally, you could write a VBA macro to do the same thing. So with ODIN's add-in, those are the only ways I can think of to accomplish your task.

That's a tough one. I'm interested to see if anyone else has a great idea. As far as I know, Excel's concat formulas do not accept ranges, so this will be difficult to pull off.

For me, I have an add-in called Nutilities installed that has a custom formula called "delimiter" http://www.iwishexcel.com/custom-formulas/delimiter/

With it installed, I can put this formula into a cell and do what you're describing:
Code:
=Delimiter(INDIRECT("b1:b"&C1),"","")

Be aware though! if you go this route, if you share the wordbook with anyone, the formula will error for them unless they have the same add-in installed.



Thanks both of you for the replies!

I went with the helper column method! Cheers!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,158
Messages
5,594,581
Members
413,915
Latest member
namreh

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
Top