How to Align Two Sets of Data in Excel (6 Examples)

Below is a dataset showing two columns: Product List 1 and Product List 2. Next to them is a separate column, Aligned Dataset, where the aligned data will appear.

Method 1 – Using the VLOOKUP Function

Steps:

• In cell I5, enter =VLOOKUP
• Select the second dataset,
• Click cell F4 and enter a $ sign.
• Press 1,0).
• Enter the following formula:

• If the name doesn’t match it shows Not Applicable (N/A). To avoid N/A, let’s expand the formula further.

We have added the IFERROR function with the previous formula.

This is for the first column.

If we want to fetch the second column,

• Enter the following formula in cell J5 :

• For the third column, enter the following formula in cell K5:

• Once we enter all the columns,
• Copy the whole formula, select the rest of the columns, and paste it.

Now, we can see that the dataset has been aligned according to column B.

Read More: All Types of Alignment in Excel (Explained in Detail)

Method 2 – Merging IF, ISNA, MATCH & INDEX Functions 

In the following example, there are two sets of data. The first dataset contains some product names that are in Product List 1, and the second dataset also contains some product names that are in Product List 2. Some products are similar, so we are going to align those products.

Steps:

• Enter the following formula into the first column in the aligned dataset:

• Use the Fill Handle tool down to all the cells we want to apply this formula.

We can see all the values in the two datasets are aligned with the duplicated values.

Read More: How to Align Columns in Excel (4 Easy Methods)

Method 3 – Combining IF, ISNA & VLOOKUP Functions 

Steps:

• Enter the following formula in cell E5:

• Press ENTER.

• Drag the Fill Handle icon to paste the used formula respectively to the other cells of the column.

You will get all the Aligned datasets that are duplicated in both datasets.

Method 4 – Merging IFERROR, VLOOKUP & COLUMN Functions 

Steps:

• Enter the following formula in cell E5:

• Press ENTER.

• Drag the Fill Handle icon to paste the used formula respectively to the other cells of the column.

You will get all the Aligned datasets that are duplicated in both datasets.

Method 5 – Using the Consolidate Feature 

Steps:

You have to keep a common column header along with numerical values within those two datasets. The Consolidate feature will use them to do the calculation.

• Select cell B14.
• From the Data tab >> click on the Consolidate feature, which belongs to Data Tools.

A new dialog box named Consolidate will appear.

• Choose the function. We have chosen the SUM function. So, the Consolidate feature will add the prices for the same product. Say, in the first dataset, there is Sampoo in the B5 cell, which price is $760. Now, this Consolidate feature will search for Sampoo in the second dataset. If it gets one, then it will add that price to the $760. So, here the output will be $760+$960 for the product Shampoo.

Below are the final results.

You can change the column header to a suitable one. We have changed the column header to Sold Product, and Total Sales.

Read More: How to Maintain Excel Header Alignment (with Easy Steps)

Method 6 – Employing VBA Code 

Steps:

• Go to the Developer Tab and select Visual basic. This will open the visual basic editor.

• Click the Insert drop-down and select Module. This will insert a new module window.

• Or we can open the visual basic editor by right-clicking on the sheet from the sheet bar and then going to View Code.
• Enter the VBA code here:

VBA Code:

Sub Align_duplicates()
Dim my_rnge As Range
Set my_rnge = Range([B4], Cells(Rows.Count, "B").End(xlUp))
my_rnge.Offset(0, 1).Columns.Insert
With my_rnge.Offset(0, 1)
.FormulaR1C1 = _
"=IF(ISNA(MATCH(RC[-1],C[1],0)),"""",INDEX(C[1],MATCH(RC[-1],C[1],0)))"
.Value = .Value
End With
End Sub

• Now, Save the code then go back to Excel File.
• From the Developer tab >> select Macros.

• Run the code.

• This will insert a new column and align all the duplicate values in this new column from the two sets of data. We can see our desired result.

Read More: Excel Align Matching Values in Two Columns

Practice Section

Now, you can practice the explained method by yourself.

Download the Practice Workbook

You can download the workbook and practice with them.

Related Articles

• How to Apply Alignment in Excel Conditional Formatting
• Excel VBA to Set Vertical Alignment (5 Suitable Examples)
• [Fixed!] Excel Cell Alignment Not Working (5 Possible Solutions)
• Align Numbers in Excel (6 Simple Methods)