Concatenate Every Second Row for Certain Number of Rows

[PostTIT]

As my title suggests I'm wanting to concatenate data that is in a single column. (e.g. Column A:A)
But I only want to concatenate data in every second row (i.e. A1,A3,A5,etc...)
I have another cell (e.g. B1) that contains an inputted number by the user. I'm looking for this number to limit the amount of cells that are concatenated. (i.e. if "B1 = 6" then I want to concatenate "A1,A3,A5,A7,A9,A11" and if "B1 = 2" then concatenate "A1,A3")

I have tried a simple concatenate function and selected the cells that I want, but I'm looking to automate it using a function (not a macro as I find other users don't appreciate macro-enabled workbooks, therefore don't enable them, therefore miss out on the functions they provide).

I have tried concatenate(transpose) but with the same result as above.

I have tried the following formula:

=CONCATENATE((MOD(ROW(OFFSET($A$1,,,1+2*($B$1-1)))-ROW($A$1),2)=0)*OFFSET($A$1,,,1+2*($B$1-1)))

but it only outputted the final value, not all of them, which wasn't desired.

And then from that I've taken various edits of the OFFSET section of the formula and used with the CONCATENATE and TRANSPOSE functions and had no luck.
I feel like I'm fairly close, but just falling short every time. Any help would be greatly appreciated.

 

[Special-K99]

There's a TEXTJOIN formula that may solve your problem but looks like you need some form of Office 365 subscription

https://support.office.com/en-us/article/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c

https://theexceltrainer.co.uk/textjoin/

Apart from that you're left with VBA/UDF/macros

 

[RoryA]

Are you using Google Chrome? If so, please have a read of this and follow the instructions to whitelist MrExcel so that you don't keep getting all that garbage code at the end of all your posts: https://www.mrexcel.com/forum/about-board/1024515-weird-posting-issue-post4919212.html#post4919212
Thanks.

 

[PostTIT]

Thanks Rory - white-listing the site was something I overlooked. I was confused as to why I had all the source/garbage code at the end of my posts.

And thanks Special, TEXTJOIN is more fluid than CONCATENATE is to use. I just need to figure out how to get it to only take the required number of cells into consideration.
I'll post an update when I have it.

 

[Special-K99]

Stick an IF(MOD(ROW() ),2)= in the TEXTJOIN though that will make it an array formula (I think).

Something like

=TEXTJOIN(",",TRUE,IF(MOD(ROW(A1:Z1),2)=1,A1:Z1)

1 for odd numbered rows, 0 for even

 

[PostTIT]

Right so thanks up till now, been very helpful.

However the thing that is missing now from the code is the ability for it to only join the text of a certain number of cells. Up till now you guys have helped get every second row in the dataset to be joined together, but if I am looking to only take the first 6 values, or the first 16 values (whatever the number, it will be based on the value in another cell) then I'm not quite sure what to do.
As I said initially I'd prefer to not have a macro do this, but a formula instead.

Any help please and thanks and thanks and please.

 

[Special-K99]

My formula was way out ROW(A1:Z1) will only ever return 1. Looks like I meant ROW(A1:A1000)

Assuming the cell count is in B1
try this (dunno if it will work)

=TEXTJOIN(",",TRUE,IF((MOD(ROW(A1:A1000),2)=1)*(ROW(A1:Z1)<=B1*2+1),A1:A1000)

 

[PostTIT]

Aye so the new formula works much like the previous iteration. The issue is with the IF statement where the formula does the test of MOD(ROW()) etc and then it outputs every second row between A1:A1000 in the TEXTJOIN. It's not able to limit it to only outputting the number of values specified in B1.

 

[Special-K99]

I don't believe I've done it again!

Should be this

=TEXTJOIN(",",TRUE,IF((MOD(ROW(A1:A1000),2)=1)*(ROW(A1:A1000)<=B$1*2-1),A1:A1000)

The second part of the condition

(ROW(A1:A1000)<=B$1*2-1)

should limit the number of TEXTJOINs to only where the row number is less than B1*2-1, ie if B1 is 6 then odd numbered rows up to 6*2-1 = 11, ie 1, 3, 5, 7, 9, 11

 

[PostTIT]

Unfortunately I think you have... From what I can work out all that happens is the if statement returns 'true' from that statement there:

MOD(ROW(A1:A1000),2)=1)*(ROW(A1:A1000)<=B$1*2-1)

and then it just takes all the values from A1:A1000 regardless. The values it is returning are not bound by any conditions.

ROW(A1:A1000) returns '1' as it is the first row. The MOD function with the '2' divisor returns '1' again. Which is, as in the formula, equal to '1'. Then you multiply '1' by Row(A1:A1000) which again is equal to 1.
B$1*2-1 is then fulfilled as true as long as the value in cell B1 is greater than '0'. Then it just takes all the data from A1:A1000.

Is there any way to have it so the formula does something like:

=TEXTJOIN(", ",TRUE,A1:A(B1))

Where it takes the value from B1 to replace the number of A1:A#?