OfficeMacros
Excel Macro: AlignRowsById
Align rows from two spreadsheets with a common ID column.
- Call the two sheets sheet L and sheet R
- Sort both spreadsheets by the ID column
- Insert 2 new columns A and B, which could have headings "L ID" and "R ID"
- Select all columns from sheet L and insert into sheet R at column A so that the 2 empty columns from the previous step separate the L data from the R data
- Input a formula into the "L ID" column so that it reflect the ID (or combination key via CONCAT or similar) of the L data
- Input a formula into the "R ID" column so that it reflect the ID (or combination key via CONCAT or similar) of the R data
- Run the AlignRowsById macro
- Within the form, select the "L ID" and "R ID" cells; do NOT select the header (if you added one); do NOT just select the entire column
- Click "Realign Rows"
- It will give you the opportunity to stop every 100 rows for a long spreadsheet
Getting Started
Run From Ribbon
- Download ExcelMacros and save where it can remain accessible
- Open ExcelMacros in Excel
- If you see "SECURITY WARNING: Macros have been disabled.", then click "Enable Content"
- Keep ExcelMacros open but switch to a new workbook (File > New)
- In Excel, go to File > Options > Customize Ribbon
- "Choose Commands From:" > "Macros"
- Select "ExcelMacros.xslm!AlignRowsById" (adjust for the file name you chose plus the macro you want)
- Select the tab and group on the right where you want the option to appear, and click "Add >>"
- Click "OK" to close the window
- In the future, you can use the macros without opening the file first
Developing OfficeMacros
- Clone from GitHub
- Resume the instructions from step 2 above
- In Excel, go to File > Options > Customize Ribbon
- Enable the "Developer" tab on the right
- Click "OK" to close the window
- Access "Microsoft Visual Basic for Applications" via "Developer" > "View Code"
- After modifying a macro, right-click on the object in the VBAProject and "Export File..." to prepare it to commit
- Commit the exported files -- do NOT commit Office document files
- Issue a pull request