Concatenate Moving Range

[citadelr]

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

 

[=ODIN=]

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:

=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]

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]

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:

=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!

 

[Eric W]

Glad we could help!