How to tell if two cells in my Excel are duplicates or contain the same value?

by

The Microsoft Excel tool helps to carry out work in a dizzying way, and it even helps you to know if two cells are duplicates or contain the same value, and this is because Excel is a spreadsheet used to do automation and systematization work , finance or statistics. For this reason, it has become a completely essential document for all people, especially professionals and office workers today.

How to Know if Two Cells in my Excel Are Duplicates or Contain the Same Value?

But, ‘How do I know if two cells in my Excel are duplicates or contain the same value?’ To answer this question, this article will look at four points . ‘First, How do you compare two Excel columns. Second, how do I make the same thing appear in one column as in another? Third, What is the correct way to use the ‘EQUALS’ formula to see if values ​​are repeated. And, fourth, What other ways are there to know if the value is repeated in Excel Online’.

Index( )

  1. How do you compare two Excel columns?
    1. MATCH
    2. SI (COUNTIF)
    3. MATCH and COUNTIF from conditional formatting.
  2. How do I make it appear in one column the same as in another?
  3. What is the correct way to use the ‘Same’ formula to see if values ​​are repeated?
  4. What other ways are there to tell if the value is repeated in Excel Online?
    1. Cntrl + F

How do you compare two Excel columns?

It is important to compare two Excel columns, especially when we work with large data sets , because in this way it is known if there are errors or it also indicates if the data is not in the right place, or even if it is repeated. in various sequences. Hence, comparing them will allow you to have a greater validity of the work you do and with much  more stability so as not to make mistakes.

Whatever the reason why the errors occur, it is very advantageous to be able to know ‘How to compare two Excel columns’, since as we mentioned above, in complicated tables with a lot of data, it is unacceptable to stamp them one by one . For this reason, we are going to explain how to do it below by means of: MATCH, COUNTIF and MATCH and COUNTIF from the conditional format.

MATCH

To ‘compare two Excel columns with the MATCH formula, we are going to show you how to do it in Excel, so continue reading in detail:

  • First,you should know that the main table, which is in blue , represents all the customers that have appeared in the store that we will call A and the second table those customers that have gone to the store that we will call B. How do you want to compare the Match names on list A with names on list B and just look at the people who have arrived at the stores. In these two tables we find: a column with the identification of our client, his name and a column that is empty, ‘it is advisable to order the data of the matrix from greater to less ‘, in ‘both datas, since this will allow to have them in order and be able to detect them quickly’.
  • Second,then you put the  MATCH function in the first cell of your column that is empty and then look for the value you care about among the other values. Later you are going to use the identification of the first name or client of the first table as value. Immediately the MATCH function will ask you for the searched Matrix, which is simply asking you to choose the matrix where you want to search for the previously chosen value.
  • Third,proceed to set the reference with F4 so that the matrix you are looking for is the same for all other values. Finally, Proceed to put 0(ZERO) in the Match Type option, since what you want is an exact match.
  • Fourth,with the function already applied, a number 17 will appear that will show you the focus of your value in the part inside the indicated range, belonging to the second table. This means that the first client will also be found in the second table and in the 17th position. Once everything is done, all you have to do is copy the MATCH formula by clicking the cross icon that is located in the lower right corner of the cell and that’s it.

CONTAR.SI (COUNTIF)

To ‘compare two Excel columns with the formula COUNTIF  (COUNTIF) we are going to show you how to do it in Excel, so continue reading in detail:

  • First,you should know that the error that comes out as N/A, are the clients that are not in the second table, this means that they have not been repeated. So, proceed to repeat the same function in the second table so that you can visualize those customers that are not in the main table.
  • Second,at this point, proceed to use the formula COUNTIF (COUNTIF) , which is responsible for counting the number of cells that are within the range, and that at the same time performs a detailed criterion. These ranges will be all the IDs or identifications that are in the main table, and their criteria is the first ID that is in the second table.
  • Third,when you have already executed the COUNTIF formula, the number 1 will appear in the cell, which will show you the number of times the ID of the second table will appear, in said main table. When the number 0 (ZERO) appears in the rows, this will mean that the client will not appear in the other list.

MATCH and COUNTIF from conditional formatting.

To ‘compare two Excel columns with the MATCH and COUNTIF formula from the conditional format , we are going to show you how to do it in Excel, since this formula allows you to highlight the data you want to mark. Therefore, if you use this ‘conditional format’ to the MATCH and COUNTIF formulas, the results that do not appear in the two tables will be highlighted. So continue reading to explain how to use it in detail:

  • First, choose the two columns from the main table and proceed to look for an option titled New rule in said ‘conditional formatting’.
  • Second,a list of options will appear, these will allow you to establish the conditions. But really, the condition that you should care about is the one that is located in the last part and it is the one that will allow you to place a function as a rule. In the description of this rule, this is where you should place the MATCH and COUNT function .

How do I make it appear in one column the same as in another?

‘So that one column appears the same as in another’, Excel will move all the data that it has, including the formulas, the resulting values, the dimensions, the cell formats and also the cells that are hidden. Therefore, it is best to use the Cut or copy command in cells, rows and columns that are already selected. Although you can also move or copy them simply using the mouse. Next, we will explain the step by step:

  • First, choose, in this case, the ‘Column’ by shading it with the mouse that you want to appear in the other ‘Column’.
  • Second, To move the shaded ‘Column’, you must go to  Start, and where it says Clipboard, proceed to click on the Cut option.
  • Third, Proceed to move the previously selected ‘Column’, and then click the option that is titled Insert Cut Cells.
  • Fourth,When you have already copied the ‘Column’, click on the Insert copied cells selection, do not right click the mouse to paste the selected column, because what this would do is replace the content that you already have in the destination cells. So the best thing you can do is click on the Paste option that appears in the Start window or simply press the CTRL+V keys and that’s it.

What is the correct way to use the ‘Same’ formula to see if values ​​are repeated?

‘The ‘EQUAL’ formula to see if values ​​are repeated’ helps you to compare the two text strings and return TRUE, if they are exactly the same, and it will return FALSE, if they are not. So, ‘the correct way to use the formula’, is the following: In case you need to distinguish something written in uppercase from lowercase, what you should use is the EQUAL function. That is, you simply have to place it this way: =IGUAL(A1;A2) , if the values ​​are repeated the result is TRUE, and if it is not repeated, the result will be FALSE. It is his ‘proper way’.

What other ways are there to tell if the value is repeated in Excel Online?

Data that is repeated in a spreadsheet in Excel Online has different default values. However, in a number of cases, the data posted is the same  and legitimate. Such is the case, that we want to make a list of the expenses that we have every month and with a fixed payment, and we want to confirm if any of these are repeated. But as we already know, many times certain errors appear.

These errors could come out due to data that has been registered twice, which we want to identify for later elimination because it simply repeats itself . For this reason, we are going to indicate ‘other ways exist to know if the value is repeated in Excel Online’ through the ‘Cntrl + F’ function.

Cntrl + F

‘Cntrl + F is another way that exists to know if the value is repeated in Excel Online’ which is used in this spreadsheet in a very simple way, you just have to follow these two steps:

  • First, Press the Ctrl+F keys and then proceed to type the search words that you think are repeated and this function will look it up for you quickly, since it is a keyboard shortcut at this point
  • Second, in the event that you do not have any of these keys that, as already mentioned, is a shortcut, what you should do is simply proceed to record a macro to create itand ‘know if the value is repeated in Excel Online’ .

 

 

Related Posts: