Join 2 tables c. Separation of information blocks

In this article, you'll learn how to quickly merge data from two Excel tables when key columns don't match exactly. For example, when the unique identifier from the first table is the first five characters of the identifier from the second table. All solutions proposed in this article were tested by me in Excel 2013, 2010 and 2007.

So, there are two Excel sheets that need to be combined for further data analysis. Let's assume that one table contains prices (the Price column) and descriptions of the goods (the Beer column) that you sell, and the second table displays data on the availability of goods in stock (the In stock column). If you or your colleagues compiled both tables for the catalog, then both must contain at least one key column with unique product identifiers. The product description or price may change, but the unique identifier always remains the same.

The difficulties begin when you receive some tables from the manufacturer or from other departments of the company. The matter may become even more complicated if a new format of unique identifiers is suddenly introduced or if warehouse stock keeping units (SKUs) change even slightly. And you are faced with the task of combining new and old tables with data in Excel. One way or another, a situation arises when there is only a partial match of records in key columns, for example, “ 12345 " And " 12345-new_suffix“. You understand that this is the same SKU, but the computer is not so smart! This non-exact match makes it impossible to use regular Excel formulas to combine data from two tables.

And what’s really bad is that the correspondences can be completely fuzzy, and “ Some company” in one table can turn into “ CJSC "Some Company"” in another table, and “ New Company (formerly Some Company)" And " Old Company” will also turn out to be a record of the same company. You know this, but how can Excel explain it?

Comment: The solutions described in this article are universal. You can adapt them for further use with any standard formulas, such as VLOOKUP(VLOOKUP) SEARCH(MATCH) GPR(HLOOKUP) and so on.

Select the appropriate example to jump straight to the solution you need:

A key column in one of the tables contains extra characters

Let's look at two tables. The columns of the first table contain the product number (SKU), the name of the beer (Beer) and its price (Price). The second table records the SKU and the number of bottles in stock (In stock). Instead of beer there can be any product, and the number of columns in real life can be much larger.

In a table with additional symbols, create an auxiliary column. You can add it to the end of the table, but it's best to insert it next to the right after the key column so it's visible.

The key column in the table in our example is A with SKU data, and you need to extract the first 5 characters from it. Let's add an auxiliary column and name it SKU helper:

Ready! We now have key columns with exact value matches - column SKU helper in the main table and column SKU in the table where the search will be performed.

Now using the function VLOOKUP(VLOOKUP) we get the desired result:

Other formulas

Extract first X characters to the right: for example, 6 characters to the right from the entry “DSFH-164900”. The formula will look like this:
RIGHT(A2,6)
=RIGHT(A2,6)
Skip first X characters, extract the following Y characters: for example, you need to extract “0123” from the entry “PREFIX_0123_SUFF”. Here we need to skip the first 8 characters and extract the next 4 characters. The formula will look like this:
PSTR(A2;8;4)
=MID(A2,8,4)
Extract all characters up to the delimiter; the length of the resulting sequence may vary. For example, you need to extract “123456” and “0123” from the records “123456-suffix” and “0123-suffix”, respectively. The formula will look like this:
LEFTSIMB(A2,FIND("-",A2)-1)
=LEFT(A2,FIND("-",A2)-1)

In short, you can use Excel functions such as LEVSIMV(LEFT), RIGHT(RIGHT), PSTR(MID), FIND(FIND) to retrieve any parts of a composite index. If you have any difficulties with this, we will do everything possible to help you.

Data from a key column in the first table is split into two or more columns in the second table

Suppose the table you are searching contains a column with identifiers. The cells in this column contain entries like XXXX-YYYY, Where XXXX– this is the code designation of a group of goods (mobile phones, televisions, video cameras, cameras), and YYYY– this is the product code within the group. The main table consists of two columns: one contains product group codes (Group), the second contains product codes (ID). We cannot simply discard product group codes, since the same product code may be repeated in different groups.

In cell C2 Let's write the following formula:

CONCATENATE(A2;"-";B2)
=CONCATENATE(A2,"-",B2)

Here A2– this is the address of the cell containing the group code; symbol " – ” is a separator; B2– this is the address of the cell containing the product code. Let's copy the formula to the remaining lines.

Now combining data from our two tables will not be difficult. We will match the column Full ID first table with column ID second table. If a match is found, entries from the columns Description And Price the second table will be added to the first table.

Data in key columns does not match

Here's an example: You are the owner of a small store, you receive goods from one or more suppliers. Each of them has its own nomenclature, different from yours. As a result, situations arise where your “Case-Ip4S-01” entry matches the “SPK-A1403” entry in the Excel file received from the supplier. Such discrepancies occur randomly and there is no general rule to automatically convert “SPK-A1403” to “Case-Ip4S-01”.

Bad news: The data contained in these two Excel tables will have to be processed manually in order to be able to combine them later.

Good news: This only needs to be done once, and the resulting auxiliary table can be saved for future use. Next, you can merge these tables automatically and thus save a lot of time

1. Create an auxiliary table for the search.

Create a new Excel sheet and name it SKU converter. Copying the entire column Our.SKU from a sheet Store to a new sheet, delete duplicates and leave only unique values in it.

Add a column next to it Supp.SKU and manually look for correspondence between the column values Our.SKU And Supp.SKU(the descriptions from the column will help us with this Description). This is a boring job, let you be happy with the thought that you only have to do it once :-).

As a result, we have the following table:

2. Update the main table using data from the lookup table.

Insert a new column into the main table (Store sheet) Supp.SKU.

Next, using the function VLOOKUP(VLOOKUP) compare sheets Store And SKU converter Our.SKU, and for updated data – a column Supp.SKU.

Column Supp.SKU filled in with original manufacturer codes.

Comment: If in the column Supp.SKU empty cells appear, then you need to take all the codes SKU corresponding to these empty cells, add them to the table SKU converter and find the corresponding code from the supplier table. After this, repeat step 2.

3. Transfer data from the lookup table to the main table

Our main table has a key column with an exact match for the lookup table entries, so this task is now easy

Using the function VLOOKUP(VLOOKUP) merge sheet data Store with sheet data Wholesale Supplier 1, using the column to find matches Supp.SKU.

Here is an example of updated data in a column Wholesale Price:

It's simple, isn't it? Ask your questions in the comments to the article, I will try to answer as soon as possible.

When working with tables in the Word text editor, it is often necessary to split or merge tables. There are usually no problems with division, since Word has a separate button for this procedure. But merging tables often confuses non-experienced users. They cannot find a button that would be responsible for this function and, accordingly, do not know what to do. In this material we will look at two ways to combine tables in Word 2003, 2007, 2010, 2013 or 2016.

Method number 1. Removing all symbols between tables.

The easiest way to merge tables in Word is to simply delete all the characters between the two tables. In this case, you need to remove both visible and non-visible (non-printable) characters. To do this, position the cursor between the tables and delete all characters between them (including spaces) using the Backspace key.

After all the characters between the two tables have been deleted, you need to press the Delete key. This will remove the invisible line break between the tables and they will be automatically merged.

If you don’t quite understand why you need to press the Delete key, then on the “Home” tab there is a button “ Show all signs" If you click it, all the symbols in the document will be displayed.

This will allow us to understand what is preventing the tables from joining and what we are removing by pressing the Delete key.

Method #2: Cut the second table and paste it under the first.

The second way to combine tables in Word involves using the “Cut” and “Paste” functions (Ctrl-X and Ctrl-V). The advantage of this method is that you don't have to delete text between tables. After merging the tables, the text will simply move down.

In order to use this method, you need to select the bottom part of the table with the mouse and cut it using the Ctrl-X key combination or using the “Cut” function in the context menu.

After this, you need to place the cursor immediately after the first table (before the text that was between the tables).

And paste the cut table using the Ctrl-V key combination.

As a result, you will merge the tables. At the same time, the text that was previously between the tables was not deleted, but simply moved down the combined table.

Addition. How to split tables.

We also need to mention table partitioning. In order to split previously merged tables, you need to place the cursor in the table on the line from which the second table should begin. Then you need to go to the “ Working with Tables - Layout" and click on the button " Split tables" You can also split tables using the key combination Ctrl-Shift-Enter.

If you have Word 2003, then to split the merged tables you need to open the “Table” menu at the top of the window and select “Split table” there.

Microsoft's text editor has a huge list of functions and tools, so something as simple as joining a table is easy to do in Word 2007. This article will be devoted to this topic.

How to join tables in Word

Before jumping directly into the tutorial on how to join two separate tables, it's worth mentioning that there are several ways to accomplish this task. The first involves using the "Cut" function. By the way, it is with its help that you can combine a table vertically and horizontally. The second one uses another option of the text editor - “Display”. This will be discussed in more detail later in the text.

Method 1: Using the "Cut" option

So, let's move on directly to how to connect tables in Word using the "Cut" option. Let us remind you that using this method you can perform both horizontal and vertical merging.

Vertical association

We open a text document in the program and see two tables. Our task is to combine them into one so that the second is below the first. To do this you will need to do the following:

Perform a full selection of the second table. To do this, it will not be enough to highlight the contents in the cells. You need to click on the icon in the upper right corner.
Cut out the table by placing it on the clipboard. This can be done in two ways: by pressing the hot keys Ctrl+X or by clicking the "Cut" button, which is located on the toolbar in the "Clipboard" group.
Place the cursor immediately below the first table. It should appear exactly below the left border.
Paste the element you copied earlier. This can also be done in two ways: by pressing the hot keys Ctrl+V or the "Paste" button on the toolbar.

Immediately after this, the first and second tables will be merged vertically. Accordingly, the assigned task can be considered completed.

Horizontal merger

We already know how to connect tables vertically in Word, but what if we need to place the second one to the right of the first one. To do this, you can use the same instructions, making some adjustments to it.

Fully select the table you want to merge with another.
Cut it out.
Place the cursor on the right side of the table. If you do everything correctly, it should blink immediately after the first line.
Paste the table you copied earlier.

You will find that both tables have been merged into one.

Method 2: through the option "Display invisible characters"

As mentioned earlier, there is a second way to connect tables in Word. By the way, it will require less time and effort, but it has one significant drawback - horizontal merging is not performed. If you are satisfied with this, then follow the following algorithm of actions:

Launch a text editor and go to the main tab.
On the toolbar, click the "Display invisible characters" button, which is located in the "Paragraph" tool group.
After this, additional symbols will appear on all sheets that correspond to their role in the text. We are interested in the paragraph mark (¶).
Find this symbol between the first and second tables and place the cursor immediately to the right of it.
Press Backspace.

Immediately after this, the space between the tables will disappear and they will merge.

Conclusion

Now you know how to connect 2 tables in Word. It is worth noting that all actions are performed very simply, so every user can easily remember them. Another important fact remains that the developers of the text editor provided the right to choose to carry out the task: perform the merge by removing paragraph marks or using the “Cut” option.

Beginner users are afraid of some cases that prevent them from working in the office software package. One of these situations is when a problem arises, how to connect two tables in Word. After all, the algorithm of actions varies depending on the version of Microsoft Word.

Introducing Microsoft Word

Probably every user already knows a program such as Word from the Microsoft Office office suite. This text editor is the most popular all over the world. I'm glad that Word provides many options for typing and formatting text. And if you have questions about “or how to insert a picture,” you can find an answer that is accessible and understandable to everyone.

So, what are the main features of Word:

Create bulleted and numbered lists.
Display non-printable characters.
Aligning a text fragment to different positions.
Inserting page breaks and paragraphs.
Purpose of headers and footers.
Inserting pictures, page numbers, various objects from other programs, symbols that are not on the keyboard.
Creation of tables (matrices) and art objects.

And this is not all the capabilities of the text editor.

Joining tables in Word 2003

To combine two matrices in Word, one main condition must be met: the number of columns must be the same. To get out of this situation and answer the question of how to connect two tables in Word 2003, you need to:

place the cursor on the break line between two objects;
Press the “Delete” button several times until the merger occurs.

If the width of the columns of one matrix differs from the data of indicators in another object, then you need to use the “Align Column Widths” command. To call it, select the table, right-click and select the required item. There is no need to be afraid that, according to the tabular data, one column should be wider, the other narrower. After merging objects, you can manually adjust this indicator.

Joining tables in Word 2007 and other versions

In a situation where it is not clear how to connect two tables in Word 2007, there is a very simple solution. Even a novice user can perform this operation. To do this, you just need to adhere to the following algorithm:

Select an object that will be combined with its “second” half.
Copy a fragment using one of the known methods:
- press CTRL + C;
- right-click (right mouse button) on the specified fragment and select “Copy”;
- Click the “Copy” button on the toolbar.
Place the cursor above or below the original matrix that will be merged with.
There is an arrow on the “Insert” button on the toolbar. You need to click on it and select “Insert table by adding”. Alternatively, when inserting a table, a small button appears with a context menu from which you can select one of the necessary commands.

This algorithm is suitable not only for version 2007, but also for others, since subsequent releases of Word did not bring global changes to the program interface. Therefore, when thinking about how to connect two tables in Word 2010, follow the specified algorithm. The main thing is to remember to remove extra characters and spaces between two objects.

Now no one should be bothered by the question of how to connect two tables in Word.

Working in Excel is, without a doubt, an invaluable experience that will be useful to every user. Of course, I’m not saying that every user should be fluent in this tool, but still, the more knowledge you have, the better. In my previous articles, I have already repeatedly talked about the capabilities of Excel, about the intricacies of graphs and tables. In addition, more than once there was talk of using certain functions in practice.

This time I propose to discuss the question of how to combine tables in Excel. To be more precise, this is called “consolidation” of tables, and such a function is in great demand. Well, let's not beat around the bush, let's figure out what it is and why it is needed at all.

If the tables are the same

As you can imagine, consolidating tables greatly simplifies your work. It allows you to create a pivot table by combining all the other data into one. Let's first consider the first option, when the tables are, in general, of the same type, the only difference is in the numbers. In this case, it is very simple to bring them together; you just need to sum up the values in the cells. I have already told you how this is done: it is used, which sums up all the values that match by quarter and name (in a specific case). That's all, the principle of operation is very simple.

If the tables are different

It is not uncommon for the sources to differ in many criteria or to be made on different files. As you understand, it is unlikely that you will be able to use the previous option; it is a painfully labor-intensive process. And since the summation of cells is no longer necessary, we need to look for other options.

Luckily, there is such an option! Let's imagine that we have three diverse tables that need to be merged.

They can be completely different, but there is one condition: in such plates the line and column headings must match. The fact is that the first row and left column of each table is the criterion by which the Excel search engine will find matches and summarize them.

How to perform consolidation? Everything is very simple: