fokisy.blogg.se

Set rngA = Range(, Cells(Rows.Count, "E").End(xlUp))

VBA code: Align duplicates or matching values in two columns Sub Listduplicates() Click Insert > Module, and paste the following code in the Module Window. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.Ģ. Supposing you have two adjacent columns- column E and column F which have some duplicate names, with the following code, a new column will be inserted between these two columns and the duplicate values are listed as below screenshot shown:ġ. Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. You can change the cell references to your need. Note: In the above formula, A2 is the first cell value of the column which you want to compare, and C2:C12 another column data that you want to be compared with. Please do as this:Įnter this formula: =IF(ISNA(MATCH(A2,$C$2:$C$12,0)),"",INDEX($C$2:$C$12,MATCH(A2,$C$2:$C$12,0))) into a blank cell which beside your first column, B2, for example, then drag the fill handle down to the cells that you want to apply this formula, and all the values both in column A and column C have been aligned with the matching values in column A, see screenshot: Here is a simple formula which can help you to display the duplicate values from two columns. To list the duplicate values which exist in both columns, this article may introduce some tricks for solving it.Īlign duplicates or matching values in two columns with formulaĪlign duplicates or matching values in two columns with VBA codeįind and highlight the duplicates or matching values in two columns with Kutools for Excel

If you have two list of names, and you want to compare these two columns and find the duplicates in both, and then align or display the matching names based on the first column in a new column as following screenshot shown.