Excel: How to merge two columns into one (from different sheets or separated columns)

user_11841532 0
Viewed 219

I have met some problem with merging two columns to one. I have some solution but not exactly fits to my problem (found here: Excel: Merge two columns into one column with alternating values)

As I understand two columns has to be one by one:

A       B       C
==================
A       1       A
B       2       1
C       3       B
                2
                C
                3

To get the above solution in C column I should use:

=INDEX($A$2:$B$9;ROUND(ROW(A1)/2;0);MOD(ROW();2)+1)

So in the formula I'm using data like A1:B4.

How to do it if I will have columns with data A and D and I dont want to include columns B and C? Or for instance I will have one column from sheet_A and second column from sheet_B?

excel
3 Answers

The Index function can be used in two different way. This example use the index function with multiple range area. The 4th parameter is the sequence number of the individual reference range.

Here is the equation for the result combined in one column.

=LET(RC,ROW(M9)-ROW($M$8),Rx,IF(ISODD(RC),(RC+1)/2,RC/2),Ax,IF(ISODD(RC),1,2),INDEX(($F$11:$F$20,$I$18:$I$27),Rx,,Ax))

RC is the variable representing the row in the result column. Rx represent the row in the reference area, Ax is the reference area number.

NOTE: M9 is my 1st row in the RC column

enter image description here

user_167831350

If you want to stick to your formula, then you could modify it like this:

https://1drv.ms/x/s!AncAhUkdErOkguUaToQkVkl5Qw-l_g?e=5d9gVM

Odd Start row

=INDEX($A$2:$D$9;ROUND(ROW(A1)/2;0);IF(MOD(ROW()-ROW($A$2);2)=1;4;1))

Even Start row

=INDEX($A$2:$D$9;ROUND(ROW(A1)/2;0);IF(MOD(ROW()-ROW($A$1);2)=1;4;1))

What is A1 in the picture is the cell directly above your first data cell.

enter image description here

If you want to place it on a different sheet you just add the sheet name:

=INDEX(MySheet!$A$2:$D$9;ROUND(ROW(MySheet!A1)/2;0);IF(MOD(ROW()-ROW(MySheet!$A$2);2)=1;4;1))
=INDEX(MySheet!$A$2:$D$9;ROUND(ROW(MySheet!A1)/2;0);IF(MOD(ROW()-ROW(MySheet!$A$1);2)=1;4;1))
user_175168990

This formula is total flexible. You can have your two seperate columns on different sheets. And the Result column also can be wherever you want.

=IF(ISEVEN(ROW())=FALSE;INDEX($A$4:$A$20;ROUND((ROWS($A$4:A4)/2);0));INDEX($B$6:$B$24;ROUND((ROWS($B$6:B6)/2);0)))

enter image description here

user_175168990
Related