1
This is a task I frequently have to do:
I have two tables whose contents are similar but not the same. Let's say: Table 1 with columns A1, B1, C1, ... Table 2 with columns A2, B2, C2, ... Let's say Table 1 is an older version of table 2. So table 2 has records that table 1 doesn't, some records have been deleted, and some changed. Let's say A is the id column in both tables.
If I have the data from these two tables side by side in an excel sheet (A1, B1, C1, ..., A2, B2, C2, ...), how can I sort them so that rows whose A columns match in both tables end up next to each other, and rows that contain data in one table but don't contain data in the other are sorted separately with empty columns on the other side?
E.g. Table 1's column A contains: 1,2,5,6
Table 2's column A contains: 2,3,6,7,10
Then how can I get this result:
A1 A2 1 2 2 3 5 6 6 7 10
The B, C, etc. columns should be sorted alongside the A columns.
(For more clarity: if I were to do this with two mysql tables, it would be like the union of a left and a right join between Table 1 and 2, on column A1=A2, sorted by coalesce(A1,A2).)
The Ax columns can contain alphanumeric data, not only numeric, and the number of columns between the two tables aren't always the same (they just need to be sorted alongside their respective Ax columns).
The solution does not have to be a macro but I suspect I couldn't do it without one?
ETA: The way I usually do this is to copy data from one table on a worksheet, sort it by A, then copy data from the other table on another worksheet, sort by A, copy them next to each other and then insert empty "rows" manually to the half where the row is missing.
For the macro, I don't care how it starts, I'm guessing it would be easiest if the two were on separate worksheets in the same excel file, but I need the end result side by side on the same work sheet.
Please specify that,, you want to Sort Columns in TWO TABLES or in a DATA RANGE? – Rajesh S – 2018-02-21T12:23:46.817
@RajeshS I added some clarification to the post because it was too long for a comment. I didn't quite understand your question but I hope this answers it. – Real Subtle – 2018-02-21T12:31:34.013
You have written "If I have the data from these two tables side by side in an excel sheet" and the Question Title is, "sort two columns side by side". I want to ask whether your Data is in TABLES or in DATA RANGE. Please remember TABLE and DATA RANGE are two different objects in Excel. – Rajesh S – 2018-02-21T12:36:38.327
@RajeshS originally they are two different tables. One of them might not even be an Excel table. But I need the end result on the same excel worksheet so I can compare them. I guess that makes them one data range? But there can be empty columns between the two if that makes it easier. (I tried to google the definition of data range in excel and they made it seem that only continuous data without empty rows and columns counts as one so I don't really know.) – Real Subtle – 2018-02-21T12:44:41.700