How to find the same records in two EXCEL tables and count them

I have two EXCEL tables. How do I count the same records in these two tables?

Step 1: Copy all phone numbers in Table 1 and Table 2 to column A of Table 3. The first line of Table 3 is the header, and the second line starts with all the phone numbers, a total of 50,000 lines.

Step 2: Enter the number 1 in column B of Table 3, and use the formula "=COUNTA(A:A)" to fill the cells in column A to count the number of names in column A.

Step 3: Enter the formula SUMIF($B$2:$B$50000,A1,$B$2:$B$50000) in cell C2 of the table. Through this formula, you can get the number of duplicate names in the cells in column A. frequency. When the result is 1, it means there is no repetition. When it is greater than 1, it means there is repetition and the number of repetitions is displayed.

Step 4: Perform automatic filtering in column C of Table 3. The filtering should be based on the current data. For example, filtering "2" means selecting phone numbers repeated twice, and then filtering a phone number from column A. Number (because there may be many duplicate phone numbers, it is best to process them one by one to avoid errors). After deleting the remaining ones, the formula will automatically get "1". After all column A is displayed, it will show that there is no processing. Phone numbers that are repeated twice can be processed in the same way.

When all the data in column C is "1", the remaining phone numbers in column A are the only phone numbers without duplication.

Maybe a little troublesome, but still very accurate.

Also, I borrowed other people’s methods and showed them to you:

Step 1: First copy all the phone numbers in Table 1 and Table 2 to column B of Table 3, set row number 1 as the header, row number 2 to row number 50000 as all phone numbers;

Step 2: Enter the formula =IF(COUNTIF(B$2:B2,B2)=1,B2,＂＂)

in A2

Then drag B2 towards fill to A50000

What is displayed are the phone numbers that are not repeated, and the repeated phone numbers are blank cells.

Copy A2:A50000, then right-click - Paste Special - Value, sort ascending and descending, and then clear the contents of column B.

How to find the corresponding information of duplicate cells in two EXCEL tables

If you want to use the 2003 version, just use the perspective mentioned above. If it is the 2007 version, there is an item in the data tab to delete duplicates. The specific operation is as follows:

1. Select the table you want to operate

2. Open the data tab

3.

4. Then select the Remove Duplicates option

5.

6. Just select the column you want to remove duplicates from. If it's in column B, select the title you wrote in column B. I wrote as supplier here. So select the hook before the supplier and remove the hook before the serial number. Just click OK.

7. The last and most important point is that your repeated names must be exactly the same. As you said, it is the same supplier, but the names are not exactly the same, such as Jinfuya Furniture and Federal. The names of the suppliers are not exactly the same, so you just have to search and replace them with the same name. Finally I hope it helps you.

How to compare two excel tables with some identical data in the two tables

1. Open the excel table, put data one on "Sheet1" and data two on "Sheet2".

2. Enter in cell C2 of sheet1: =IF(COUNTIF(.

3. Switch to the column in Sheet2 that needs to be queried, and then select the entire column.

4. At this time, the formula in cell C2 in the Sheet1 table automatically changes to: =IF(COUNTIF(Sheet2!A:A.

5. Keep the operation interface in Sheet1 and enter the comma under the English characters after the formula: =if(COUNTIF(Sheet2!A:A, .

6. In sheet1, select cell A2 with the mouse, and the formula becomes: =IF(COUNTIF(Sheet2!A:A,Sheet1!A2.

7. Next, manually complete the formula in English: =IF(COUNTIF(Sheet2!A:A,Sheet1!A2)>0, "Yes", "No").

8. Click Enter to get the formula judgment result.

9. Simply pull down the cell with the mouse to complete all judgments.

The above is the detailed content of How to find the same records in two EXCEL tables and count them. For more information, please follow other related articles on the PHP Chinese website!