# Align Two Sets of Data in Excel (6 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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 the ways shown below. ## 6 Suitable Examples 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.

For the following methods, we’re going to use two sets of data. The first dataset contains some product names and the prices of the products. And the second set of data contains the same products which are in column B in dataset 1 but some are missing. So, now we will see how we can align those two sets of data. ### 1. Using VLOOKUP Function to Line up Two Sets of Data 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. Now, 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 and 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)`
• If the name doesn’t match it shows Not Applicable (N/A). To avoid N/A, let’s expand the formula further.
`=IFERROR(VLOOKUP(B5,\$E:\$G,1,0),””)`

Here, we have added the IFERROR function with the previous formula. 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:

`=IFERROR(VLOOKUP(B5,\$E:\$G,2,0),"")` And for the third column in the K5 cell,

`=IFERROR(VLOOKUP(B5,\$E:\$G,3,0),"")` • 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. Merging IF, ISNA, MATCH & INDEX Functions for Aligning Duplicate Values in Two Sets of Data

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 that 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:
`=IF(ISNA(MATCH(B5,\$C\$5:\$C\$12,0)),"",INDEX(\$C\$5:\$C\$12,MATCH(B5,\$C\$5:\$C\$12,0))` • 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 duplicated values. Formula Breakdown

• MATCH(B5,\$C\$5:\$C\$12,0)—> The MATCH function will find the exact match of B5 cell value within the \$C\$5:\$C\$12 array.
• Output: 2.
• Now, INDEX(\$C\$5:\$C\$12,2)—>Here the INDEX function will return the 2nd row from the given array \$C\$5:\$C\$12.
• Output: “Shampoo”.
• Similarly, MATCH(B5,\$C\$5:\$C\$12,0)—> turns 2.
• So, IF(ISNA(2),””, “Shampoo”)—> this will be the final expression of the formula. The ISNA function will examine whether the cell value is error or valid. As 2 is a valid number so ISNA function will give FALSE so the IF function will show “Shampoo” as output, on the other hand it will show a void space.

### 3. Combining IF, ISNA & VLOOKUP Functions to Align Duplicate Values

Here, we can use another combination of some Excel functions. They are the IF, ISNA, and VLOOKUP functions. With this formula, you can align the duplicate values from two sets of data in Excel.

• Firstly, write the following formula in the E5 cell.
`=IF(ISNA(VLOOKUP(B5,\$C\$5:\$C\$12,1,0)),"",VLOOKUP(B5,\$C\$5:\$C\$12,1,0))`
• Secondly, press ENTER. Formula Breakdown

• VLOOKUP(B5,\$C\$5:\$C\$12,1,0)—> will return “Shampoo” where this VLOOKUP function is looking up for the B5 cell value, within \$C\$5:\$C\$12 this array. And 1 is the column index number.
• Then, ISNA(“Shampoo”)—> is the logical test for the IF function. Here, the ISNA function will check whether the value is an error or not. So, this will give FALSE as output.
• Then, the IF function will return a void space when the logical test is TRUE otherwise will operate VLOOKUP(B5,\$C\$5:\$C\$12,1,0) this operation.
• VLOOKUP(B5,\$C\$5:\$C\$12,1,0) this is a similar operation to the first one. Where this VLOOKUP function is looking up for the B5 cell value, within \$C\$5:\$C\$12 this array. And 1 is the column index number. 0 is for the exact match.

• Then, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column.

Lastly, you will get all the Aligned datasets that are duplicated in both datasets. ### 4. Merging IFERROR, VLOOKUP & COLUMN Functions to Extract Similar Values from Two Sets of Data

Again, we can use a new combination of some Excel functions. They are the IFERROR, VLOOKUP, and COLUMN functions. Similarly, with this formula, you can line up the duplicate values from two sets of data.

• Firstly, write the following formula in the E5 cell.
`=IFERROR(VLOOKUP(B5,\$C\$5:\$C\$12,COLUMN()-COLUMN(\$E5)+1,0),"")`
• Secondly, press ENTER. Formula Breakdown

• Here, COLUMN()-COLUMN(\$E5)+1 is the number of the column index for the VLOOKUP function. The COLUMN function gives the numerical representation of a given column. As the formula is used in the E column, COLUMN() becomes {5}. Also, COLUMN(\$E5) turns {5}. Ultimately, COLUMN()-COLUMN(\$E5)+1 will be 5-5+1 which is equal to 1.
• So, the formula will be as VLOOKUP(B5,\$C\$5:\$C\$12,{1},0). In this term, the VLOOKUP function will search for the value situated in the B5 cell, within the \$C\$5:\$C\$12 array. Then, 1 is the column index number and 0 denotes exact matching.
• Lastly, the IFERROR function will check whether the value is an error or not.

• Now, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column.

Lastly, you will get all the Aligned datasets that are duplicated in both datasets. ### 5. Use of Consolidate Feature for Summing Up Values within Two Sets of Data in Excel

Here, we will use the Consolidate feature under the Data tab to align data along with doing some calculations like sum, average, maximum, minimum, etc. within the data sets. Now, let’s talk about how you can use this Consolidate feature in Excel.

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.

• Now, select the B14 cell.
• Then, from the Data tab >> click on the Consolidate feature, which belongs to Data Tools. At this time, a new dialog box named Consolidate will appear.

• First, choose the function. Here, 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, we have attached the final results. If you want, then you can change the column header to a suitable one. For like, we have changed the column header to Sold Product, and Total Sales. ### 6. Employing VBA Code 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.

• 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.

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,0)),"""",INDEX(C,MATCH(RC[-1],C,0)))"
.Value = .Value
End With
End Sub`````` Code Breakdown

• Here, we have created a Sub Procedure named Align_duplicates.
• Next, declare a variable my_rnge as Range.
• After that, we used a formula to align the duplicate values from two sets of data.

• Now, Save the code then go back to Excel File.
• From the Developer tab >> select Macros. • 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. ## Practice Section

Now, you can practice the explained method by yourself. ## 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!

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. 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 Team Leader. I'm a graduate in BSc in Computer Science and Engineering from United International University. I love working with computers and solving problems. I’ve always been interested in research and development. Here I post articles related to Microsoft Excel. Hoped this may help you. Thank you.

1. Reply On the code : =IF(ISNA(MATCH(B5,\$C\$5:\$C\$12,0)),””,INDEX(\$C\$5:\$C\$12,MATCH(B5,\$C\$5:\$C\$12,0))

What if I have on column “D” a price for each product and each time I pull the item on “aligned dataset” it brings as well the price next to it ? Advanced Excel Exercises with Solutions PDF  