Bazaprogram.ru

Новости из мира ПК
4 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel merge tables

Merge two Excel tables Based on matching data in Columns

I’ve been working on a excel problem, that I need to find an answer for I’ll explain it below.

I’ve Table01 with the Columns :

I’ve Table02 with the columns:

I’ve merged two tables of Table01 & Table02 as shown in the Image03 , But without order.

But,as you see Group No Column is similar in both tables.

What I need is to get the matching rows of Table01 & 02 considering ‘Group No’ Column.

The Final result is to be seen as the final image.

Is there a way to do this with excel functions ?

2 Answers 2

Put the table in the second image on Sheet2, columns D to F.

In Sheet1, cell D2 use the formula

copy across and down.

Edit: here is a picture. The data is in two sheets. On Sheet1, enter the formula into cell D2. Then copy the formula across to F2 and then down as many rows as you need.

Teylyn’s answer worked great for me, but I had to modify it a bit to get proper results. I want to provide an extended explanation for whoever would need it.

My setup was as follows:

  • Sheet1: full data of 2014
  • Sheet2: updated rows for 2015 in A1:D50, sorted by first column
  • Sheet3: merged rows
  • My data does not have a header row

I put the following formula in cell A1 of Sheet3:

Read this as follows: Take the value of the first column in Sheet1 (old data). Look up in Sheet2 (updated rows). If present, output the value from the indicated column in Sheet2. On error, output the value for the current column of Sheet1.

In my version of the formula, «;» is used as parameter separator instead of «,». That is because I am located in Europe and we use the «,» as decimal separator. Change «;» back to «,» if you live in a country where «.» is the decimal separator.

A$1: means always take column 1 when copying the formula to a cell in a different column. $A$1 means: always take the exact cell A1, even when copying the formula to a different row or column.

After pasting the formula in A1, I extended the range to columns B, C, etc., until the full width of my table was reached. Because of the $-signs used, this gives the following formula’s in cells B1, C1, etc.:

and so forth. Note that the lookup is still done in the first column. This is because VLOOKUP needs the lookup data to be sorted on the column where the lookup is done. The output column is however the column where the formula is pasted.

Next, select a rectangle in Sheet 3 starting at A1 and having the size of the data in Sheet1 (same number of rows and columns). Press Ctrl-D to copy the formulas of the first row to all selected cells.

Cells A2, A3, etc. will get these formulas:

Because of the use of $-signs, the lookup area is constant, but input data is used from the current row.

Copy identical data from lookup Excel table to the main one

Merge Tables Wizard for Excel 2016, 2013-2007

You need to compare two Excel spreadsheets, find the updates or new records and pull them to your main table for further analysis. The problem is it’s too time-consuming. Plus checking, copying and pasting manually don’t give you quick results or guarantee that none of the important data are overlooked.

Solution

Merge Tables add-in for Excel will automatically compare 2 sheets you select as master and lookup, find matching rows and update your main table. It will take you just five steps and less than a minute. In addition, you can select any number of key columns for comparing the data, choose those you want to update, highlight or filter the modified entries, and append new columns missing in the main worksheet.

Merge Tables Wizard for Excel key features

  • Automatically find and combine data from 2 Excel spreadsheets.
  • Use multiple columns as matching criteria.
  • Combine tables from different workbooks.
  • Add new columns to the original spreadsheet or update existing columns.
  • Add updates after the last row in your original table.
  • Color updated entries.
  • Ignore extra spaces in matching columns.
  • Update only those cells in your original table, which have no data.
  • Update your original table only if there are values in the respective lookup table cells.
  • Add a column with the status in the updated original table, which would show whether the row was updated or not.
Читать еще:  Функция адрес в excel

How Merge Tables Wizard works

Merge Tables add-in is a handy alternative to Excel Vlookup/Lookup functions, which automatically locates matching rows in one table and adds them to the other. All you need is to select your worksheets and pick the columns to compare. Choose if you want to update the columns or add them to your original table.

There are also some additional options that you may find useful. For matching rows this tool will set the background color of updated cells, update only empty cells in your main table or update only if cells from the lookup table contain data.

You can choose to Add non-matching rows to your original table. Besides, you can tweak the following options to quickly see the updates: Clear background color in the updated columns, and specify a color for the cells that were updated.

Find and merge data from 2 Excel spreadsheets in 5 steps

This 5-step merging Wizard allows you to forget about manual checking of two Excel tables for matching data. You just select the master table — the one that you want to update. Then you choose the Lookup table where the add-in will look for the new data. After that you pick matching columns or those with common index fields in both spreadsheets. And choose the columns to be updated or added to the main table. Last but not least, you select some handy additional options to be applied.

Use one or several columns as matching criteria

You can choose to compare one or more columns in your spreadsheets and the plug-in will use them as the matching criteria. All matching rows will be exported from the lookup to the master table and neatly merged with the current data.

Update data or add new columns

When you are merging your Excel tables, you can choose to update data in matching columns or copy non-matching column(s) to the master sheet.

The add-in will flawlessly do its job in either case. The plug-in matches table A and table B data. If a match is found, it will overwrite any outdated info. At the same time if there is a non-matching column, Merge Tables Wizard will add it to the other table.

Add updates to the end of the table

For a better view of the merge results, feel free to tweak the tool to shift all found updates to the end of your original table. So you will easily see all newly added rows.

Additional options for matching rows

For the matching rows you can select to set background color of the updated cells and then the add-in will highlight the corresponding entries with the color you choose.

At the same time it is possible to renew only those cells that were initially empty in the original worksheet, which can be especially handy if you want to keep the original data and just fill the gaps.

Last but not least you can choose to copy only those cells from the lookup worksheet that have data. This will prevent copying empty cells from your second table.

Marks for merged data: Updated, Non-updated, or New row

You can add a column with the status to the updated original table. It will help identify updated, new, or non-updated rows.

Thus you will have a chance to check the updates as well as see the rows that were added. Benefit from this option if you want to have this information at hand even after sorting the rows or changing the layout of your worksheet.

Merge Tables in Excel

When we work in excel it is not definite that data will be in a single worksheet, it can be in multiple worksheets in multiple tables, if we want to merge tables there are various methods to do that so that we can have data in a single table and this is known as merging tables in excel, this can be done by using VLOOKUP or INDEX and MATCH functions.

Excel Merge Table (Table of Contents)

Читать еще:  Как отключить формулы в excel

Merge Tables in Excel

Sometimes while analyzing the data, we might gather all the necessary information in a single worksheet. It’s a very common problem or situation when data is divided across many worksheets or workbooks. There are many ways to merge the data from multiple tables into one table in excel.

How to Merge 2 Tables in Excel?

We have given Customer data city wise in two tables. We have taken 20 records for this.

Sheet 1: Table 1: CustomerInfo

Sheet 2: Table 2: ProductDetails

In both the tables, Order No. is the common information on which basis we will create a relationship between them.

Below are the steps for merging these two tables:

  • Click on any cell in the Customer Info table. Go to INSERT tab and click on Table option under Tables section. Refer below screenshot.

  • Then Create table dialog box will appear. Our table “CustomerInfo” has column headers, hence the checkbox “My table has headers” should be checked. Refer below screenshot.

  • It will convert our data into a table format. Now, click on the Table Name field under the Properties section and give the name of this table as “Customer_info”.

  • Follow the same steps for another table “ProductDetails”. We have given the name “Products” to another table. Refer below screenshot.

  • Click on somewhere on the Customer_Info table then, Go to Insert tab and click on Pivot Table option under Tables section.

  • A dialog box for Create Pivot Table will appear. Tick on Checkbox “Add this data to the Data Model” as shown in below screenshot.

  • Click on OK then, It will open a new sheet with a new Pivot Table Fields section in the right side as shown in below screenshot.

  • Click on ALL tab in the Pivot Table Field section and it will display all the tables created by us. Refer below screenshot.

  • Now click on Relationships option under the Calculations section as shown in below screenshot.

  • It will open a dialog box for creating a relationship between these tables. Click on the New button. Refer below screenshot.

  • It will again open a dialog box as shown below and created tables are listed here.

  • As there is one field “Order No.” is common in both the tables, hence we will create a relationship between these tables by using this common field/column.
  • Select Customer_Info under Tables section and Order No. field under Column Section. Refer below screenshot.

  • Select another table Products under Related table section and select Order No. field under Related column section. Refer below screenshot.

  • Primary Key is the unique values that appear once in the table then, Click on OK. It will display the relationship as shown in below screenshot.

  • Now we can drag & drop field accordingly to see the result. Click on Customer_Info table as shown in below screenshot.

  • Drag fields Order No. , Customer Name and city under Rows box.
  • Drag Age field under Filter box.
  • Drag Product field under Column Box and Values box for the count of products.

The final result is below:

Accordingly, as per your requirement, you can drag & drop the fields.

Excel Merge Two Tables

Merge Two Tables in Excel (Table of Contents)

Merge Two Tables in Excel

Merging tables in Excel is not possible when both the table at least one common column. If the first condition is satisfied then we can merge the tables with VlookUP and Index Match function. Wherewith the help of Vlookup function, we can get the values from the second table to the first table, if lookup value matches with Lookup range. And if we want to use Index function along with Match to merge the tables, for this also we must have at least one common value to map.

Excel functions, formula, charts, formatting creating excel dashboard & others

Examples of Merge Two Tables in Excel

Example #1: Merge with Copy Paste Method

This is the easiest method to do. It does not involve any kind of formula or rocket science. We just simply do the copy & paste method to combine two. I have two months of sales tables. One is Jan and another one is Feb month sales data.

Читать еще:  Массив ячеек в excel

I have two identical tables here but not together. We can do two simple merges here. One is creating a new table altogether or add data to the existing table. If you observe the headings of both the tables are same.

Alternative 1

Since I have the region as the same heading I can merge table like the below one.

In the above image, I have excluded Region heading because it is common for both the tables.

Alternative 2

To make the data more flexible for analysis I can just modify the data structure as shown in the below table.

Now look at the above table, I have merged the one below the other but I have added one more column as Month. This is to analyze the data by applying the pivot table, the above data structure is important. We can easily apply pivot table for the above table.

Example #2: Merge with VLOOKUP Method

Who does not know VLOOKUP? All most all the excel users are aware of it, I hope you are also aware of it. If you the full usage of VLOOKUP, merging tables will be easier for you. I have three tables of employee’s information. The first table shows the Emp ID and their name, the second table shows the Emp ID and their department, the third table shows the Emp ID and their salary information.

If you look at all the three tables EMP ID is the common heading in all the three tables. Based on this common heading we can merge all the three tables together. Merging should be in the below format.

I need to merge Department and Salary columns from Table 2 and Table 3. If you are aware of the VLOOKUP formula then this will be a walk in the park.

  • Apply VLOOKUP formula for Department from table 2 :

  • Apply VLOOKUP formula on cell N2 of department column :

  • Apply the formula as shown below :

  • The result is shown in the below image :

  • Now drag down the formula to all the empty cells :

  • Apply VLOOKUP formula for Salary from table 3:

  • Apply VLOOKUP formula on cell O2 of Salary column :

  • Apply the formula as shown below :

  • The result is shown in the below image :

  • Now drag down the formula to all the empty cells :

Now we have merged all the data from different table together by applying the VLOOKUP method.

Example #3: Merge with INDEX & MATCH Method

INDEX & MATCH would be a new formula for you. But nothing to worry it is just an alternative formula for VLOOKUP formula. It works exactly the same as the VLOOKUP formula.

  • Use below formula for merging Department column from Table 2:

  • Apply VLOOKUP formula on cell L3 of department column :

  • Apply the formula as shown below :

  • The result is shown in the below image :

  • Now drag down the formula to all the empty cells :

  • Use below formula for merging Salary column from Table 3 :

  • Apply VLOOKUP formula on cell N2 of department column :

  • Apply the formula as shown below :

  • The result is shown in the below image :

  • Now drag down the formula to all the empty cells :

Things to Remember about Merge Two Tables in Excel

  • POWER QUERY can merge many tables together. In Excel 2010 and in 2013 it is an add-in and in excel 2016 it is a built-in function.
  • If you are merging by using VLOOKUP you need to be careful of duplicate entries in lookup value. Check for duplicate values before you proceed.
  • Once the formula is applied remove the formula by using paste special method. Because it will hamper your merged data table if something happens to other dependent tables.
  • In case of errors, you can use IFERROR function to get rid of errors generated by VLOOKUP formula.
  • If the data is in many worksheets with same headings you can search for VBA code to combine worksheets together.

Recommended Articles

This has been a guide to Merge Two Tables in Excel. Here we discuss the Merge Two Tables in Excel and how to Merge Two Tables in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

Ссылка на основную публикацию
Adblock
detector