HOW TO COMPARE AND FILTER EXCEL DATA?

You try it with macros:

F11 opens the VB scripter.

2.(Menu) Insert -> Module.

3.Paste it as follows**:

sub macro1()

k = 0sheets("sheet2").select

sheets("sheet1").select

cells(1, 1).select

for i = 1 to 65535

cells(i, 1).select

tmp =if len(tmp) <= 0 then

endend if

if asc(tmp) >= 48 and asc(tmp) <= 57 then

if tmp <>int(tmp) then

k = k 1

rows(i).select

sheets("sheet2").select

rows(k).select

sheets("sheet1").select

rows(i).select

i = i - 1

end if

end if

nextend sub

4.Close the vb scripter and go back to excel

f8 runs the macro.

Note: This macro deletes the whole row of records whose first column is not an integer, and backs up the deleted whole row to the sheet2 worksheet.

When the first column of data is not a number, do not process, note that sheet2 is completely empty, because the beginning of the program will clear sheet2

8.Note that this ** will become a line after pasting, you need to pay attention to the line break, involving the deletion operation, it is recommended that you back up the data before deleting.

The method and detailed operation steps for comparing and filtering the data content in the two ** sheets in excel are as follows:

Preparation materials: Microsoft Office 2016 Excel software, computer.

1. First, find the numbers in column A, and then fill in the numbers in column B in column E, as shown in the figure below.

2. Secondly, enter the vlookup function in cell e2, that is, =vlookup (search value, search range, number of search columns, exact match or approximate match), where the search value is the name of column d, the search range is column A and column B, the number of search columns is column B, and the number of column A is the second column, so fill in the number 2, the exact match is represented by the number 0, and Wang Wucan's data matching is 30, as shown in the figure below.

3. Finally, drop down and fill in the formula to get all the processing values, you can get the number of other people, but there is no content quotient seven in column a, so n a is displayed, which means that it cannot be found, as shown in the figure below. The above is the method of comparing and filtering the data content in the two ** sheets in Excel.

Excel often needs to use the technique of filtering comparison data, how to use the filter function to compare data? The following is the method I bring to you to use excel to filter and compare data for your reference.

Excel makes use of the method of filtering comparison data:

Step 1: In the excel sheet, list two columns of data, compare the data in column B with column A, filter out which data in column B is different, and mark it in red.

Step 2: Select column b. Left-click on column b to select it. "Get started"---"Conditional formatting"---"Create a new rule"：

Filter the comparison dataStep 3: Select the last item in 'Select Rule Type' 'Use formula to determine which cells to format':

Step 4: In the red marked box, enter the formula: =countif(a:

a,b1)=0 (the meaning of the formula: use the data of b1 to compare all the data in column a, if there is a duplicate value, calculate plus 1, and if it occurs n times, the value of the formula is n. If it doesn't happen, the result is 0).

Step 5: In the red box, click 'Format': mark the different data filtered.

Step 6 of Filtering and Comparing Data: In this example, the color of 'Font' is used as the mark of 'Data Difference'. Choose the font color according to your personal preference, in this case red. Mouse 'OK'.

Step 7: Return to this page, and you can also 'OK'.

Preparation Tools Materials: A computer with Windows 10, Microsoft Office Home and Student Edition 2016 Excel software.

In excel, the method of comparing and filtering the data content in the two ** sheets is as follows:

2. Enter the vlookup function in cell E2, =vlookup (search value, search range, number of search columns, exact match or approximate match), where the search value is the name of column D, the search range is column A and column B, the number of search columns is column B, and the number in column A is the second column, so fill in the number 2, the exact match is represented by the number 0, and Wang Wucan's data matching is 30.

3. If you drop down and fill in the formula, you can get the number of other people, but there is no content quotient 7 in column a, so n a is displayed, which means that it cannot be found.

1. Open**, select the cells that need to filter duplicate data (one column, one row, multiple columns, multiple rows, multiple cells are acceptable), click "Conditional Formatting" under the "Start" menu, select "Highlight cell size staring rule", and then click the "Duplicate Value" option.

2. Now you will see the following window, the first window on the left has two options "Repeat" and "Unique", select "Repeat", select the color of the marked duplicate data on the right, and then click "OK".

3. The effect is as follows, all duplicate values are marked with the same color.

4. Select the title cell, click "Sort & Filter" under the "Start" menu, and select "Filter", so that a small triangle will appear down next to the title cell.

5. Click on the downward Dan brother and small triangle, select "Filter by color", and then select the color you just set in step 2.

6. Look, all the repetitive numbers are screened out.

Preparation Tools Materials: A computer with Windows 10, Microsoft Office Home and Student Edition 2016 Excel software.

In excel, the method of comparing and filtering the data content in the two ** sheets is as follows:

2. Enter the vlookup function in cell E2, =vlookup (search value, search range, number of search columns, exact match or approximate match), where the search value is the name of column D, the search range is column A and column B, the number of search columns is column B, and the number in column A is the second column, so fill in the number 2, the exact match is represented by the number 0, and Wang Wucan's data matching is 30.

3. If you drop down and fill in the formula, you can get the number of other people, but there is no content quotient 7 in column a, so n a is displayed, which means that it cannot be found.

The application of the number of inclusions can be solved as follows:

1.Enter the data in sheet1.

2.The formula in sheet2 is as follows:

a1=sheet1!a$1

a2=offset(sheet1!a$1,counta(a$1:a1)*2,0)

a3=offset(sheet1!a$1,counta(a$1:a2)*2,0)

a4=offset(sheet1!a$1,counta(a$1:a3)*2,0)

If the position of your actual data changes, you can change the cell position in the above formula accordingly.

1. Overall introduction: Compare the data in two excel sheets. As shown in the figure above, the data of column A of sheet1 and sheet2 are the same and different, and the difference is matched by the formula.

2. Tools and raw materials: excel, data**, vlookup, if, iserror functions.

3. Method: sheet1

b2=if(iserror(vlookup(a2,sheet2!$a$2:$a$11,1,0)),"different","Same") to copy downward.

sheet2

b2=if(iserror(vlookup(a2,sheet1!$a$2:$a$11,1,0)),"different","Same") to copy downward.

1. Open Excel**, and then select the cell area that needs to be filtered for statistical quantity.

2. Then click the "Insert" option in the toolbar above and select the "Pivot Table".

3. Drag the "Data" column into the "Filter" box, and drag the "Data" column into the "Value" box.

4. Click the "Data" drop-down box, click the value to be filtered, and click OK.

5. The cells can be filtered, and the filtered values will be calculated into statistical quantities.

When filtering, you need to select the header of the table and click Filter.

Operation method: 1. First, select the header position of the data cell to be filtered in Excel**.

2. Then open the "Start" tab and select the drop-down triangle location below the "Sort & Filter" option.

3. Click the "Filter" button in the options menu that opens.

4. After selection, you can add the function of filtering open combustion to the selected header cell, and click the icon of the drop-down triangle box to filter it.

In Excel, you can use the filter function to filter the data. Here are some common screening methods:

1.Use auto-filtering: Select a data range and click"data"tab"Filtering"Button. Excel adds a drop-down arrow to the first row of each column. Click the arrow to select the values you want to filter on.

2.Filter by condition: Select a data range and click"Slag sells data"tab"Filtering"Button.

Then click on the drop-down arrow oak head on the column and select it in the pop-up menu"Filtering"and select it"Custom filtering"。In the dialog box that appears, you can set multiple filter criteria.

3.Use advanced filtering: Enter the filter criteria into a separate field (e.g. a separate **), then select a data range and click it"data"tab"Advanced"Button.

In the dialog box that pops up, Settings"List area"For the data range, set"Condition area"to filter the area, and then click"OK"Conduct screening.

Find the same sail data in 2 excels and mark them as follows:

<>2. Enter =if(countif()) in the blank space of the same line corresponding to the name in sheet1

3. Then switch to sheet2, select all names and press enter.

4. Shoot the hail and then switch to sheet1, then this function becomes =if(countif(sheet2!a1:a44))。

5. Finally, add the function =if(countif(sheet2!$a$1:$a$44,a2),"s","f"After the input is completed, press enter, and those displayed as s are the students in this class, and those displayed as f are not.

Pull down from this row to fill in and all students will be screened.