How to compare two rows in Excel

When using tabular processor Microsoft Office Excel, in addition to problems comparing numeric values, it is often necessary to compare and text ("string") data table cells. This can be done using built-in Excel functions comparison, if the result of the operation is necessary to obtain a digital or logical values. And you can use the conditional formatting if the result should be a visual selection of matching (or mismatching) of the table cells.

How to compare two rows in Excel
How to select max value from table
How to merge cells in Excel without losing data
How in Excel to make a list in one cell

Instruction

Use the built-in comparison function of cells COUNTIF, if it is necessary to compare the text values in cells of table columns with sample text and count all matching values. Start with filling the column with text values and then to another column, click the cell where you want to see the result of the calculation, and enter the appropriate formula. For example, if check values are in column A, and the result should be placed in the first cell of column C, its contents should be:=COUNTIF($A:$A;"Grapes")Are "Grapes" - a string value that is matched against the values of all cells in column A. you Can not specify it in the formula and put in a separate cell (e.g. B1) and insert in the formula the appropriate link:=COUNTIF($A:$A;B1)

Use the conditional formatting if it is necessary to visually identify in the table the result of the comparison of string variables. For example, if you need to highlight in column A cell, in which the text coincides with the sample in cell B1, then start with the selection of this column, click its header. Then click the "Conditional formatting" in the group of commands "Styles" bookmarks "Main" menu in Excel. Go to "Rules for the cell selection" and select "Equal to". In the opened window, specify the cell-type (click cell B1 and select in the drop-down list version of the design of the matching rows. Then click "OK".

Use a combination of built-in functions IF you CONCATENATE when you need to compare with the sample is not a single text cell, but several. The CONCATENATE function combines these values into a single string variable. For example, CONCATENATE(A1;""; B1) to the string from cell A1 adds the text " and ", and after he put the string from cell B1. Designed in such a way the string can then be compared with the sample using the IF function. When it is necessary to compare more than one string is more convenient to give the name of the cell sample. Click it and to the left of the line formulas instead of using cell (e.g. C1) type the new name (e.g. "sample"). Then click the cell which should contain the result of the comparison, and enter a formula:IF(CONCATENATE(A1;""; B1)=sample;1;0)Here the unit is the value that will contain the cell with the formula, if the comparison gives a positive result, and zero for a negative result. To propagate the formula on all rows of the table that you want to compare with the sample is very simple, hover over the lower right corner of the cell and when cursor will change (will become a black cross), click the left mouse button and drag this cell down to the last compare strings.

Is the advice useful?