I want the third row to be a combination of the first two row. The first two rows can be of varying length and will be different length from each other.
How can this be done?
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityI want the third row to be a combination of the first two row. The first two rows can be of varying length and will be different length from each other.
How can this be done?
In A3 enter:
=IF(COLUMNS($A:A)<COUNTA(1:1)+1,INDEX(1:1,COLUMNS($A:A)),INDEX(2:2,COLUMNS($A:A)-COUNTA(1:1)))
and copy across.
This should work for different lengths of data in the first two rows. It does not require TEXTJOIN()
or XML filtering.
EDIT#4:
The formula is pretty simple-minded. It just marches across the first row. Once it has picked up all the contents of the first, it drops down to the second row and starts picking up data starting back at column A.
With care, it is possible to extend this to more than two rows.
If one has the dynamic array formula and TEXTJOIN then put this in A3:
=TRANSPOSE(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",TRUE,1:2)&"</b></a>","//b"))
If one does not have the dynamic array formula but has TEXTJOIN, put this in A3:
=IFERROR(INDEX(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",TRUE,1:2)&"</b></a>","//b"),COLUMN(A1)),"")
This may need to be confirmed with Ctrl-Shift-Enter when exiting edit mode. Then Copy over till one gets blanks.