How Excel extracts the relevant data from two tables to form a new table

If you need to copy a lot of tables and the format is consistent, for example:

1.are all from sheet1 (i.e. all your tables are copied from the corresponding cells named "sheet1". Other names are acceptable, but they must be the same), then you can record a macro if you copy C5, C6, C7 to Sheet2 (i.e. all your tables are copied to "Sheet2", other names are OK, but they must be the same).

2.The specific steps are (take version 03 as an example) to open a table first, and then select: Tools - Macros - Record a new macro.

After clicking OK, you can start recording macro, in fact, you can do the operation you want to do it again, and then select: Tools - Macro - Stop recording macro.

Can. 3.Then you can open Other, find the macro you just recorded from Tools-Macros-Run Macro, and then run to repeat your corresponding operation.

For example, if you have two columns in your sheet, A and B, B is the information column, a total of 10 rows.

There is only column A in Sheet2.

Column A is partially identical on two pages.

Enter =vlookup(a1, sheet1!) in b1 of sheet2. a1:b10,2,true).

After that, drag it down to do it.

Step 1: Remove the duplicate ** in the new table (my method is: first sort by the ** column, then classify and summarize, count as you like, sum it all, and then press CTRL G to copy the unique ** to the new table, and use left(a2,(len(a2)-3))) to get the new distinct **.

Step 2: Use vlookup to find pairings in separate tables!

Another: I still don't understand, add mine.

It should be possible to use the vlookup function, but if it doesn't work, paste the content of the second worksheet into the first worksheet, and then you can use the sorting method to press the same rows together, and then do some simple processing.

If it's convenient, send the file to see (.).

Two ** find out different data and extract, how to operate, let's take a look at the method.

1. First open ** in your computer and click on duplicates under the data options.

2. Then click Set Highlight Duplicates in the pop-up menu bar.

3. Then in the page that opens, select the ** area and click OK.

4. At this time, in the open **, different data will be displayed.

The steps to solve how to extract the same data from two excel sheets are as follows:

1.Put two worksheets in the window, Table 1 is all the students, and Table 2 is the names of the students in a class.

2.In **1, enter =if(countif()) in the blank space of the same line corresponding to the name

3.Then switch to **2, select all the names and press the Enter button.

4.Switch to Table 1 again, and the function becomes =if(countif(sheet2!a1:a44))。

5.Finally, add the function =if(countif(sheet2!$a$1:

a$44,a2),"s","f"When the typing is complete, press Enter, and the student in this class appears as liter guess s, not as f. The filling is then pulled down from this line, and all the students are screened. This solves the problem of how to extract the same data from two excel sheets.

1. Enter two sets of data according to the classification in the open excel**, and you need to use the vlookup function to extract the two sets of data into another **.

2. Open another **, you can see that the order of the year and the first ** is different, you need to extract the data according to the year, and enter the extraction function: =vlookup(a2,sheet1!a2:c7,2,false), click Enter.

3. After getting the extracted data, you can extract the first set of data according to the year by filling in the formula downward.

4. Brother Hong uses the same formula to change the return value to "3", the formula: =vlookup(A2,Sheet1!a2:c7,3,false)。

5. The second set of extracted data can be obtained, and the matching extraction operation that satisfies the two conditions can be realized.

Suppose Table 1 is numbered in column A and the number of people is in column B.

Table 2 Number in column A The name of the enterprise is in column B.

Table 3 Number in column A, enterprise name in column B, number in column C, cell B2 in sheet 3.

vlookup(a2,sheet2!a:b,2,) in cell C2.

vlookup(a2,sheet1!a:b,2,) is done.

The simulation form has been sent, please receive it.

Use the vlookup function.

Syntax: vlookup (query value, search range, return column nth, exact search or not) example table 1.

Table 2: Formulas for enterprises.

The formula for the number of people.

Enter B2 in Table 3.

if(a2="","", vlookup(a2, table 2!) a:b,2,0))

Enter b3 in Table 3.

if(a2="","", vlookup (A2, Table 1!) a:b,2,0))

Copy down, and then you only need to fill in the number in column A of Table 3.

First of all, copy and paste the number of Table 1 or Table 2 into Table 3 and enter it in cell B2 of Table 3.

, vlookup(a2, table 2!) a:b,2,0) pulls down on cell C2 input.

, vlookup (A2, Table 1!) a:b,2,0) pulls down.

In Table III.

a2 input formula =a!a2b2 input formula =a! B2C2 input formula =VlookUp(A2,B!

a:b,2，false)

The formula A2, B2, C2 is copied downward.

It doesn't affect, you can use the vlookup function, someone sent it to you, I won't send it, you can't figure it out and then find me.

Use the vlookup function to solve the secondary problem.

Please inform the mailbox, completed, waiting to be sent, as long as the number and other correspondence, there is no problem with the order.

It can be achieved with the almighty VBA. Illustrate.

It is now required to extract column A of sheet1 and sheet3 and put it into sheet4. It is assumed that the number of known data items is the same and equal to 5

Step 1: Insert the module and compile ** as follows:

sub Create a new table ().

const hs = 5

for i = 1 to hs

next i

end sub

See screenshot of the sample table.

Extract data from column A of table 1, table 2, and table 3 to the input of column A2 of sheet 1 in the new table.

The formula pulls down to the right.

Your problem needs to be implemented in VBA. Need specific help +pp2327285699

Just use the vlookup function of excel.

In the contract**, the product name and unit price are listed in AB.

Enter the formula in the damper meter you want to find.

That is, according to the value of A2, find the value in columns A to B of the ** of "In the contract", and the output value is the value of the second column in the column A to B in the ** of "In the Contract", that is, column B, and take the exact value.

You need to modify the parameters in the formula appropriately according to the cells where your data is located.