3 Easy Ways to Align Two Sets of Data in Excel

Organizing data using various techniques is quite common in Excel. Records are arranged in a tabular form here. After entering data, you should align two sets of data in Excel. These two sets should be interconnected. You can edit a few of the records. But in cases of an extensive dataset, you should consider Excel formulas or shortcut techniques to align two sets of data in Excel. This will help you in three ways to guide you in the alignment. It is applicable for multiple cells in Excel and you can eventually modify it according to your requirements. All the methods have step-by-step instructions with a figurative description.

📁 Download Excel File

Download the Excel file below.

Learn to Align Two Sets of Data in Excel with These 3 Approaches

The article is going to introduce three approaches in Excel to align two sets of data. These methods will help you understand the uses elaborately. The following discussion will cover uses of the VLOOKUP function, IF function, and finally a VBA code. Therefore, users will get a walkthrough of these solutions. A dataset will be modified in the following methods below to help you understand.

The VLOOKUP function is a finder function that can work across sheets. You can widely use this function to align two sets of data in Excel. In order to remove the error, we would like to utilize the IFERROR function.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, select cell ID H6.
• Secondly, enter the following formula on the cell.

• Thirdly, press the Enter key.

• Using the Fill Handle icon, copy the formula down all the cells of the column.

• Now, copy the same formula for the next column. Use “2” instead of “1” this time as you need the 2nd column of the table this time.

• In the same way, copy the formula down to the remaining cells using the Fill Handle icon. You should get an interface like the following image.

The IF function uses frequently in Excel. It will allow you to make reasonable comparisons between data and criteria. The IF function combined with ISNA, MATCH, and INDEX functions can help you align two of your data sets in Excel.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select cell ID H6.
• Next, enter the following formula on the cell.

• After that, press the Enter key.

• Now, using Fill Handle icon, copy the formula down all cells of the table. For the score column, you will notice a different set of output as the formula is matching with the score column.

Using VBA (Visual Basic for Applications), you can employ Macro code to apply on your worksheet. Moreover, it is also the only method that will create a new column for your common data automatically. With the proper instruction, you can make your work easier. Following is an example to find out common student names.

⬇️⬇️ STEPS ⬇️⬇️

• In the beginning, press Alt+F11 to open Visual Basic Application (VBA).
• Now, select the Module option from Insert tab. You can also press Alt+I+L for the action.
• Write the following code on the Module box.

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

• You should see the window like the image below.

• Now, go back to the workbook window.
• Next, press Alt+F8 or choose the Macros option from the Developer tab.
• In this stage, the Macro window will pop up to confirm the macro. Click on Run.

• Another new column “Aligned” has been generated with all the common data from both the “Student List“. You should get an interface like the following picture.

How to Insert Multiple Rows in Excel Between Data Automatically

Inserting rows is a regular task in data recording through Microsoft Excel. Apart from inserting new rows using the Ribbon or the Context Menu, you can use keyboard shortcuts as well.

⬇️⬇️ STEPS ⬇️⬇️

• In the beginning, click on row header 7 to add a new row between the 6th and 7th rows.

• Now, press Alt+I from your keyboard.
• Then, press Alt+R You will find a new row automatically.
• Keep repeating the shortcut for every new row.

📄 Important Notes

🖊️  The VBA method might interrupt your format on cells. It might occur during the automatic insert of a new column.

🖊️  You need to use the VLOOKUP and IF functions carefully. The absolute values used here might need to change according to your data requirements.

📝 Takeaways from This Article

The article lets the readers understand the variety of options available to align two sets of data in Excel.

📌  Primarily, you can use the VLOOKUP function to align two sets of data. It can even work across sheets.

📌  You can combine the IF function with ISNA, INDEX, and MATCH functions to do the alignment. Although the formula requires individual attention, it is an effective method.

📌  With the help of VBA a certain Macro can be run on the cell ranges. This method aligns two sets of data in Excel.

Conclusion

To align two sets of data in Excel, three methods are useful. You can use the VLOOKUP function for the alignment formula. You can even combine the IF function with ISNA, INDEX, and MATCH functions. Eventually, a Macro Code can help you with it as well. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.