Mismatched Columns

Help!  My Columns Don't Match!
We want to compare the attendance for Football and Baseball by city, as shown in the two Excel files below.  Unfortunately, our two data tables have different columns and their order is chaotic. Here are the two tables, opened in Excel:
2017 US Baseball Attendance
2017 US Football Attendance
Step 1: Poor Alignment
When you first open these two tables in Beyond Compare, they will not align at all and you will complain that you want a refund on BC!
Step 2: Better Alignment
Go to Rules->Columns.  Use the arrow keys to slide the column headers up and down.
Also, you can hide some columns, giving the result below, where the marked in red are now hidden (we hid them because they were irrelevant to our comparison).
Blue: The result of using the green arrows  Red: The result of rightclicking 'Hide' on the irrelevant columns
The alignment looks better but the key columns are still not ideal.  This is because Beyond Compare is naively using Column 1 to do the "join". By default, BC will select Column 1 as the key column. The result looks okay, but not great, below:
Step 3: Best Alignment
By default, BC will select Column 1 as the key column. In this case, we might want to join instead on City. You can rightclick any column heading that you want to set as a Key column.  We can now make some interesting conclusions.  For example, Minneapolis attendance is ranks better in its baseball league than in its football league. Whereas Dallas seems to like their football team more than their baseball team.
Football and Baseball Attendance tables joined by city name.
​

Table Compare - Previous

Keys

Next - Table Compare

Longer example, opening .csv files

Last modified 2yr ago

Copy link

Contents

Help! My Columns Don't Match!

Step 1: Poor Alignment

Step 2: Better Alignment

Step 3: Best Alignment