Spalten Markieren
How to find duplicate values in two columns in Excel?When we use a worksheet, sometimes we need to compare two columns and find the same values. For example, I have two long columns of student names, and now, I want to compare them and find out the same names. If I compare them cell by cell, it will be tiresome and waste a lot of time. Look at the following screenshot, I have column A (A2:A15) and column C (C2:C13) which contain some duplicate names.By using a formula, we can compare two columns and display the duplicate values. Please do with the following steps:1. In cell B2, input this formula: =IF(ISERROR(MATCH(A2,$C$2:$C$13,0)),',A2).Note: In the above formula: A2 is the column which you want to be compared.
$C$2:$C$13 is the range that you want to be compared with. You can change the variables for what you are using.2. Press the Enter key.
Select cell B2, and then drag the fill handle over cell B15. And all of the duplicate names will be displayed in column B.
See screenshot:Find duplicate values in two columns with VBA codeThe following VBA code can also help you to find out the duplicate values between two columns.1. After installing Kutools for Excel, please do as below:Click Kutools Select Select Same & Different Cells.1. In the Select Same & Different Cells dialog box, do the following operations:(1.) Click the first button to select the source column to be compared, then click the second button to select the column you are compared with;(2.) Choose Each row under the Based on section;(3.) Then select Same Values fromt he Find section;(4.) At last, you can choose one background or font color to highlight the same cells under the Processing of resuts section as you need.2. The Best Office Productivity Tools Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by80%.
Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails. Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range. Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns. Hello,Is there any way to umatch & pair /uduplicates in two separate columns? You can also use the Match function.Make sure to put your headers so you can also sort afterIn column A you have 1 list and in column C your 2nd listIn B2 use Match(A:A,C:C,0) hit enter. It won't work if your items aren't matching in what they are, like a number to a number thingThen using your filter function uncheck everything except the N/A in the 2nd column and you will see what isn't in the 1st. Easily knowing what didn't have matches and what did.
Spalte Markieren Excel
You can do it both ways so just reverse the C and A in the above formula and see what isn't in A that is in C. It works great for really long lists, then you don't have to continually scroll for those with and without matches.