How to do you CONCAT data from alternative rows?

[Predaking]

Long time reader, first time poster.

This forum has helped me in so many ways, there aren't enough words.

Apologies if this has been asked before (I have searched everywhere) but I can't find a definitive answer for what may be a basic question.


My data is a follows.
A
1
B
2
C
3
D
4
E
5

I would like to know how get my data in the following format.
A1
B2
C3
D4
E5

I have tried to use the CONCAT function, but using the fill down function has it all messed up.

Thanks in advance!

 

[hagia_sofia]

Hello, maybe (hopefully it will work in 2021):

=LET(
a,A1:A10,
INDEX(a,SEQUENCE(ROWS(a)/2,,1,2))&INDEX(a,SEQUENCE(ROWS(a)/2,,2,2)))

 

[Predaking]

Thanks for the reply!

I must apologise. I have Office 2021 on another PC!

I am currently running Office 2016 as well.

Unfortunately, using the formula you provided I get a "#SPILL" error. in Office 2016

Just out of curiousity, what if I want to insert a carriage return Char(10) between the A and 1.

Thanks again

 

[Fluff]

using the formula you provided I get a "#SPILL" error. in Office 2016

You cannot get a #spill error in 2016, as 2016 does not have spill ranges.

 

[hagia_sofia]

Please test the following:

=INDEX($A$1:$A$10,2*ROWS(A$1:A1)-1)&CHAR(10)&INDEX($A$1:$A$10,2*ROWS(A$1:A1))

 

[Predaking]

Please test the following:

=INDEX($A$1:$A$10,2*ROWS(A$1:A1)-1)&CHAR(10)&INDEX($A$1:$A$10,2*ROWS(A$1:A1))

Works like a charm!..for one column

I have a table of data, 348 rows, 14 columns (A1:N348), where I would like data from A1 and A2 to combined and then A3 to A4 and so forth (as per the single column example). This being repeated for the column B, C, D, etc

I've tried re-arranging the formula, by increasing the range of the Index limit to suit, and changing some of the target cell data and then using fill down (unless I'm doing something wrong) but no luck.

Thanks again for your help!

 

[Kaper]

So in M1 (or further to the right, may be also down as far as needed, like P11 or whatever) write version of this formula with relative columns addressing, like:

=INDEX(A$1:A$10,2*ROWS(A$1:A1)-1) & CHAR(10) & INDEX(A$1:A$10, 2*ROWS(A$1:A1))

And copy it down and right

If your data has headers in row 2 and real data starts in row 2, use

=INDEX(A$2:A$11,2*ROWS(A$2:A2)-1) & CHAR(10) & INDEX(A$2:A$11, 2*ROWS(A$2:A2))

and so on.