How to align two columns where one is a subset of the other and each has unshared data columns associated with them

Question

I am looking for a way to automate the sorting of a table formatted similarly to the image above.

Every entry in column L is a member of column C, but it is a subset such that not all entries in column C exist in column L.

Columns A and K share the same information, but formatted differently so that it is not useful.

What I want is to be able to distribute the data in columns K, L, M to match the row-by-row alignment in columns A-G, where the two blocks of data are aligned so that columns C and L are matched. Any rows that are missing in the right block (e.g. students 3, 6, 7, 9) can be left blank.

The desired output would look like this:

I have no problem setting this up with XLOOKUP to move the entries manually, but this is needed for sets of data where block A-G will have ~2000 rows and block K-M will have ~500 rows of data and needs to be done several times per year.

The two softwares outputting the data in two different formats are institutionally locked and cannot be synced to each other internally, so this sorting must be done in a spreadsheet.

Answer 1

Hi @David Armstrong, 

Thank you for posting your question in the Microsoft Q&A forum. Based on your requirement for high automation, handling large datasets, and performing this task repeatedly throughout the year, Power Query is the recommended solution. It is more powerful than XLOOKUP because it can handle differences in data formatting (for example, numbers stored as text) and automatically refresh when new data is added. 

Please try the following steps: 

Step 1: Prepare the data 

• For the main table (Columns A–G), select the data range, go to the Data tab, and choose From Table/Range. Name this table Data Source 1. 

• For the secondary table (Columns K–M), it’s best to keep it on a separate sheet or in a separate file. Go to New Source > File > Excel Workbook, select the file or sheet containing columns K–M, and click OK. Name this query Data Source 2. 

Step 2: Merge the data 

• In the Power Query interface, select the query Data Source 1 on the left panel. 
• On the toolbar, click Merge Queries. 
  • For the first dropdown, choose Data Source 1 
  • For the second dropdown, choose Data Source 2. 
• Click on the Student # column in both tables (Column C and Column L) to set them as the matching keys. For Join Kind, select Left Outer (all from first, matching from second). This ensures that the full list of students from A–G is preserved, and any missing data from K–M will remain blank. 
• Click OK. 

Step 3: Expand and adjust formatting 

• A new column will appear. Click the two-arrow icon in the column header to expand it.  Uncheck Use original column name as prefix for cleaner column names. 
• If the ID columns in the two tables don’t match (one is Text and the other is Number), right-click the ID column header, choose Change Type, and set both to Text before merging. 

Step 4: Load back to Excel 

• Click Close & Load. Excel will create a new sheet with the fully aligned dataset. You can rearrange the columns to match your original layout if needed. 
• For future updates, simply replace the source data and click Data > Refresh All. The result table will update automatically. 

Here’s a resource that might be helpful for you: Create, load, or edit a query in Excel (Power Query) - Microsoft Support 

I hope this gives you a helpful idea to get started. If anything is unclear or you’d like to share more details, please feel free to reply, I’ll do my best to assist you further. 

Thank for your time! 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.