Forum Discussion
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 from both tables to give a new table/column that can be used in a data validation list.
| Table 1 | ||
| ID | col 1 | col 2 |
| 1 | aa | ba |
| 2 | ab | bb |
| 3 | ac | bc |
| 4 | ad | bd |
| 5 | ae | be |
Table 2 | ||
| ITM | col 2 | col 3 |
| A | ca | da |
| B | cb | db |
| C | cc | dc |
| D | cd | dd |
| E | ce | de |
Expected result
| result |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| A |
| B |
| C |
| D |
| E |
I am currently using Office 2016 so I don't have a lot of the newer functions.
Thanks in advance.
V
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.
3 Replies
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 - 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
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.
