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.
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.
We are going to align those two data and merge them in a new set of data named Aligned Dataset.
- 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:
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
And for the third column in the K5 cell,
- If the name doesn’t match it shows Not Applicable (N/A). To avoid N/A, let expand the formula further
- 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.
- First, into the first column in the aligned dataset, enter the 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
- First, go to the Developer Tab and then 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.
- After that, write down the VBA code here.
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,0)),"""",INDEX(C,MATCH(RC[-1],C,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.
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!