Mismatched Columns
Last updated
Last updated
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:
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!
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).
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:
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.
