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?

  • Are the number of items in each row the same or can row 1 be 3 and row 2 be 4? – Scott Craner May 29 at 14:31
  • Different, I will edit question – erotsppa May 29 at 14:31
  • What version of Excel do you have? – Scott Craner May 29 at 14:31
  • 16.35 excel for mac – erotsppa May 29 at 14:32
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.

| improve this answer | |
  • I just tested it, and seems to be off? – erotsppa May 29 at 14:27
  • @erotsppa I updated it....................... – Gary's Student May 29 at 14:28
  • It breaks as soon as you have 4 items in second row (4, 5, 6, 7) – erotsppa May 29 at 14:29
  • @erotsppa You are correct....................I will delete this answer................. – Gary's Student May 29 at 14:30
  • @erotsppa Updated answer for arbitrary fills of the first two rows. – Gary's Student May 29 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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.