This post will guide you how to **align duplicate values within two columns based on the first column** in your worksheet in Excel. How do I use an formula to align two columns duplicate values in Excel.

Table of Contents

## 1. Aligning Duplicate Values in Two Columns using Formula

Assuming that you have two columns including product names in your worksheet. and you want to find duplicate values in those two columns(Column A and Column B) and align them based on the first column in a new column(Column A and Column B). How to do it. And you can use an formula based on the ** IF function**, the

**, the**

`ISNA function`

**, and the**

`MATCH function`

**. Like below:**

`INDEX function`

`=IF(ISNA(MATCH(A1,$B$1:$B$7,0)),"",INDEX($A$1:$B$7,MATCH(A1,$B$1:$B$7,0),2))`

Type this formula into the first cell in Column C, and press **Enter** key on your keyboard to apply it. then drag the **AutoFill handle** down to other cells to apply this formula.

**Note:** Cell A1 is the first cell in the first column, and B1:B7 is the range of cells in the second column.

## 2. Aligning Duplicate Values in Two Columns with VBA Code

You can also align duplicate values within two columns using VBA Macro in Excel, just do the following steps:

**Step1:** Press “**Alt**” and “**F11**” keys at the same time to open the Visual Basic Editor.

**Step2:** In the Visual Basic Editor, select “**Insert**” from the menu bar, then choose “**Module**” to create a new module.

**Step3:** Copy and paste the below VBA code into the new module. Close the Visual Basic Editor and return to the Excel sheet.

```
Sub CheckDuplicateValues_ExcelHow()
Dim sourceRange As Range, lookupRange As Range, outputRange As Range
Dim i As Long, matchIndex As Variant
' prompt the user to select the source range
On Error Resume Next ' to handle the cancel button
Set sourceRange = Application.InputBox("Select the source range:", Type:=8)
On Error GoTo 0 ' reset the error handling
If sourceRange Is Nothing Then Exit Sub ' exit if the user cancels
' prompt the user to select the lookup range
On Error Resume Next
Set lookupRange = Application.InputBox("Select the lookup range:", Type:=8)
On Error GoTo 0
If lookupRange Is Nothing Then Exit Sub
' prompt the user to select the output range
On Error Resume Next
Set outputRange = Application.InputBox("Select the output range:", Type:=8)
On Error GoTo 0
If outputRange Is Nothing Then Exit Sub
For i = 1 To sourceRange.Cells.Count
matchIndex = Application.Match(sourceRange.Cells(i), lookupRange, 0)
If IsError(matchIndex) Then
outputRange.Cells(i).Value = ""
Else
outputRange.Cells(i).Value = Application.Index(sourceRange.Resize(, 2), matchIndex, 2)
End If
Next i
End Sub
```

**Step4:** Press “**Alt**” and “**F8**” keys at the same time to open the Macro dialog box. Select the macro ” **CheckDuplicateValues_ExcelHow**” from the list and click the “**Run**” button to execute the code.

**Step5:** The code will prompt you to select the source range, lookup range, and output range.

**Step6:** The code will run and populate the output range with the values that match between the source and lookup ranges.

You should now have the results in the output range.

## 3. Video: Aligning Duplicate Values in Two Columns

This video will show you how to align duplicate values within two columns in Excel using both a formula and a VBA code.

## 4. Related Functions

- Excel IF function

The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])…. - Excel INDEX function

The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])… - Excel MATCH function

The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])…. - Excel ISNA function

The Excel ISNA function used to check if a cell contains the #N/A error, if so, returns TRUE; otherwise, the ISNA function returns FALSE.The syntax of the ISNA function is as below:=ISNA(value)….

## Leave a Reply

You must be logged in to post a comment.