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.