How to use vlookup to filter duplicate excel data?
How to use vlookup to filter two excel duplicate data:
There are two clothing sales tables with duplicate data (one is in "excel tutorial. xlsx" table 1, and the other one is in "clothingSales.xlsx" table 2) (see Figure 2). You need to find duplicate records. This can be achieved by using the vlookup function. The method is as follows: yi
1. Add an "auxiliary" column after both tables to mark rows with duplicate records. Use the automatic filling method to fill in all the "auxiliary" columns in the "excel tutorial" with 1. The operation steps are as shown in the figure:
2. Switch to clothingSale .xlsx, enter =IFERROR(VLOOKUP(A2,, in G2 cell; select the "View" tab, click "Switch Window", select "excel tutorial", then switch to the "excel tutorial" window, click Sheet6 in the lower left corner , select the "View" tab, click "Switch Window", select clothingSales.xlsx, switch back to the "excel tutorial" window, [excel tutorial.xlsx]Sheet6! is automatically filled in behind A2, and the formula has changed to =IFERROR( VLOOKUP(A2,[excel tutorial.xlsx]Sheet6!, continue to enter $A2:$G10,7,0),""), then the complete formula is =IFERROR(VLOOKUP(A2,[excel tutorial.xlsx]Sheet6!$ A2:$G10,7,0),""), press Enter to return 1; move the mouse to the cell fill handle and drag it down to find all duplicate records (those with 1 are duplicate records) ).
3. Formula description
Formula=IFERROR(VLOOKUP(A2,[excel tutorial.xlsx]Sheet6!$A2:$G10,7,0),"") also by IFERROR and VLOOKUP are composed of two functions. The function of the IFERROR function is the same as the "vlookup filtering duplicates in two columns" above. The search value of the VLOOKUP function is A2;
The search area is another document (i.e. [ $A2:$G10 (that is, find each column and each row of the table) of the Sheet6 workbook of the excel tutorial. Row changes, $G10 and $A2 have the same meaning; the returned column number is 7; 0 means an exact match.
4. Note
(1), when the 2nd line in the clothingSales document When the "number" in row 9 in the "excle tutorial" document is the same, as shown in the figure:
(2), although the second row in the two tables If different, an incorrect result will be returned (i.e. 1), as shown in the figure:
(3) This situation occurs when the value to be found (i.e. A2) Column (i.e. column A). It can be seen that this method is only suitable for finding the same data in corresponding rows of two tables.
The above is the detailed content of How to use vlookup to filter duplicate data from two excels?. For more information, please follow other related articles on the PHP Chinese website!