Forum Discussion

Copper 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

combining columns.xlsx24 KB

rachelgomez161999
Iron Contributor
Jul 29, 2022
Steps are given below-

Click on the Data tab.
In the Get & Transform Data group, click on Get Data.
In the drop-down, click on Combine Queries.
Click on Merge. This will open the Merge dialog box.Merge Tables using Power Query - Combine queries merge queries options
In the Merge dialog box, select ‘Sales_Data’ from the first drop down.Select Sales Data in the Merge Dialog box
Select ‘Pdt_Id’ from the second drop down.Select Product Id in Merge dialog box
In ‘Sales_Data’ preview, click on the ‘Item’ column. Doing this will select the entire column.
In ‘Pdt_Id’ preview, click on the ‘Item’ column. Doing this will select the entire column.Select Columns that are common - merge tables in Excel
In the ‘Join Kind’ drop-down, select ‘Left Outer (all from first, matching from second)’.Merge Tables in Excel using Power Query Join drop down
Click OK.

Greeting,
Rachel Gomez

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