Forum Discussion

Brass Contributor

Jul 28, 2022

Solved

Combining columns from multiple tables

Hi , I was wondering if anybody could help me solve an issue I am facing. I have 2 tables on different sheets but I need to get one column on a new sheet which combines the the col ID and ITM...

excel

Harun24HR

Jul 28, 2022

V-GEe7

Its really hard to merger two columns to old versions of excel. You can achieve it by below formula to Excel-2016.

=IFERROR(INDEX(CHOOSE({1,2},Sheet2!$A$2:$A$6,Sheet3!$A$2:$A$6),IF(MOD(ROW(A1),COUNTA(Sheet2!$A$2:$A$6))=0,COUNTA(Sheet2!$A$2:$A$6),MOD(ROW(A1),COUNTA(Sheet2!$A$2:$A$6))),ROUNDUP(ROW(1:1)/COUNTA(Sheet2!$A$2:$A$6),0)),"")

Download the attached sample file.

merge_two_column.xlsx10 KB

OliverScheurich

Gold Contributor

Jul 28, 2022

V-GEe7

An alternative could be Power Query. In the attached file you can add data in Table1 and Table2 and then click in the result table. Then right click with the mouse and refresh.

Table1

ID col 1 col 2

1	aa	ba
2	ab	bb
3	ac	bc
4	ad	bd
5	ae	be
6	ae	be
7	ae	be
8	ae	be

Table2

ITM col 2 col 3

A	ca	da
B	cb	db
C	cc	dc
D	cd	dd
E	ce	de
F	ce	de
G	ce	de
H	ce	de
I	ce	de
J	ce	de
K	ce	de
L	ce	de

A	ca	da
B	cb	db
C	cc	dc
D	cd	dd
E	ce	de
F	ce	de
G	ce	de
H	ce	de
I	ce	de
J	ce	de
K	ce	de
L	ce	de

A	ca	da
B	cb	db
C	cc	dc
D	cd	dd
E	ce	de
F	ce	de
G	ce	de
H	ce	de
I	ce	de
J	ce	de
K	ce	de
L	ce	de

A	ca	da
B	cb	db
C	cc	dc
D	cd	dd
E	ce	de
F	ce	de
G	ce	de
H	ce	de
I	ce	de
J	ce	de
K	ce	de
L	ce	de