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.

Below, for your better understanding, we have added an overview image.

Align Two Sets of Data in Excel


Download Practice Workbook

You can download the workbook and practice with them.


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.

First Dataset for this article

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.

Second Dataset for this article


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.

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:

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

Applying IFERROR & VLOOKUP Functions for Column Index 2

And for the third column in the K5 cell,

=IFERROR(VLOOKUP(B5,$E:$G,3,0),"")

Applying IFERROR & VLOOKUP Functions for Column Index 3

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

Align Two Sets of Data Using IFERROR & VLOOKUP Functions

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


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.

Dataset 3 to show the alignment of dataset

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

Using IF, ISNA, MATCH & INDEX Functions

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.

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


Similar Readings


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.

Combining IF, ISNA & VLOOKUP Functions in Excel

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.

Combining IF, ISNA & VLOOKUP Functions to Align Duplicate Values from Two Sets of Data


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.

Using IFERROR, VLOOKUP & COLUMN Functions

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.

Merging IFERROR, VLOOKUP & COLUMN Functions to Extract Similar Values from Two Sets of Data


Similar Readings


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.

Use of Consolidate Feature under Data Tab

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.

Use of Consolidate Dialog Box

Below, we have attached the final results.

Results for Using Consolidate Feature

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.

Use of Consolidate Feature for Summing Up Values within Two Sets of Data in Excel

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


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.

Employing Excel VBA to Align Two Sets of Data

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

Inserting Module

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

VBA Code for Aligning two sets of Data

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.

Going to Macros

  • Then, Run the code.

Pressing RUN Button to 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.

Align Two Sets of Data by Using VBA Code

Read More: Excel Align Matching Values in Two Columns


Practice Section

Now, you can practice the explained method by yourself.

Practice Section for Aligning Two Sets of Data


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 Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sabrina Ayon
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

2 Comments
  1. 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 ?

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo