3

Image

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?

4
  • Are the number of items in each row the same or can row 1 be 3 and row 2 be 4? May 29, 2020 at 14:31
  • Different, I will edit question
    – erotsppa
    May 29, 2020 at 14:31
  • What version of Excel do you have? May 29, 2020 at 14:31
  • 16.35 excel for mac
    – erotsppa
    May 29, 2020 at 14:32

2 Answers 2

4

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.

enter image description here

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.

7
  • I just tested it, and seems to be off?
    – erotsppa
    May 29, 2020 at 14:27
  • @erotsppa I updated it....................... May 29, 2020 at 14:28
  • It breaks as soon as you have 4 items in second row (4, 5, 6, 7)
    – erotsppa
    May 29, 2020 at 14:29
  • @erotsppa You are correct....................I will delete this answer................. May 29, 2020 at 14:30
  • @erotsppa Updated answer for arbitrary fills of the first two rows. May 29, 2020 at 14:55
1

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"))

enter image description here


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.

enter image description here

2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .