Align Two Sets of Data in Excel (3 Simple Methods)

In Microsoft Excel, alignment is basically the positions of one’s data in the cells. Excel has its own default alignment. But we can easily change, edit or align two sets of data in Excel by following those ways shown below.


Download Practice Workbook

You can download the workbook and practice with them.


3 Ways to Align Two Sets of Data in Excel

There are a few simple techniques for controlling cell behavior. Excel has the flexibility to set the data as per the requirements.

1. Line up Two Sets of Data in Excel Using VLOOKUP Function in Excel

In Excel, the VLOOKUP Function means “Vertical Lookup” by which we can search for a certain value in a column. We can also align the datasets by using this function. In the following example, there are two sets of data. The first dataset contains some product names, the prices of the products.

Line up Two Sets of Data in Excel Using VLOOKUP Function

And the second set of data contains the same products which are on column B in dataset 1 but some are missing. So, now we will see how we can align those two sets of data.

Line up Two Sets of Data in Excel Using VLOOKUP Function

We are going to align those two data and merge them in a new set of data named Aligned Dataset.

STEPS:

  • First, In cell I5, we need to write down =VLOOKUP( now it’s asking which value you are looking for. So, we are looking for cell B5 then pressing comma (,) from the keyboard. Now select the second dataset then press cell F4. We will put a $ sign in the F4 cell, which will freeze them. Then press 1,0).
    So the function we need to write is:
=VLOOKUP(B5,$E:$G,1,0)

Line up Two Sets of Data in Excel Using VLOOKUP Function

And this is for the first column. If we want to fetch the second column, just select the whole formula and in cell J5 paste the formula. Change it by

=VLOOKUP(B5,$E:$G,2,0)

And for the third column in the K5 cell,

=VLOOKUP(B5,$E:$G,3,0)
  • If the name doesn’t match it shows Not Applicable (N/A). To avoid N/A, let expand the formula further
=IFERROR(VLOOKUP(B5,$E:$G,1,0),””)

Line up Two Sets of Data in Excel Using VLOOKUP Function

  • Once we enter all the columns, copy the whole formula and select the rest of the columns and simply paste it. Now, we can see that the dataset has been aligned according to column B.


2. Aligning Duplicate Values in Two Sets of Data with IF Formula

The IF function is the most popular function in Excel. With this, we will make logical comparisons and align the duplicate values in two sets of data.

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

Aligning Duplicate Values in Two Sets of Data with IF Formula

STEPS:

  • First, into the first column in the aligned dataset, enter the formula:
=IF(ISNA(MATCH(B5,$C$5:$C$12,0)),"",INDEX($C$5:$C$12,MATCH(B5,$C$5:$C$12,0))

Aligning Duplicate Values in Two Sets of Data with IF Formula

  • Then, use the Fill Handle tool down to all the cells we want to apply this formula.

  • Now, we can see all the values in the two datasets are aligned with the duplicates values.


3. Using VBA to Align Two Sets of Data in Excel

We can use a simple VBA Code for aligning the duplicate values in two sets of data. For this, we are using the same dataset which is used for the IF Function to align the matching values.

Using VBA to Align Two Sets of Duplicate Values in Excel

  • First, go to the Developer Tab and then select Visual basic. This will open the visual basic editor.

Using VBA to Align Two Sets of Duplicate Values in Excel

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

Using VBA to Align Two Sets of Duplicate Values in Excel

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

  • After that, write down the VBA code here.

VBA Code:

Sub Findduplicates()
Dim rng As Range
Set rng = Range([B4], Cells(Rows.Count, "B").End(xlUp))
rng.Offset(0, 1).Columns.Insert
With rng.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

  • Then, 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. And we can see our desired result.


Conclusion

By following the steps, we can easily align two sets of data in our workbook. All those methods are simple, fast and reliable. Hope this will help you! If you have any questions, suggestions or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!


Related Searches

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo