Sorting on two columns at once.

[slamb999]

Hello,
I am working on a schedule for my kids school and need some help. The schedule was made and has 380 rows with all the basketball games and looks like this.

A B C D E F
Game # Date Time Location Home Team Away Team
1 12-8-09 7:30 Gym A Team A Team x
2 12-9-09 8:00 Gym B Team y Team A
...
n 2-20-10 5:00 Gym n Team N Team M


I can sort the data on home team or away team column but can not sort on both. Example is that I would like to see when Team A plays (home or away) in one report. I tried a pivot table and that did not work well. If you sort on column E, you will miss all the games in F. Let me know if anyone has an easy way to do this.

Thanks,

Steve

[jasoncw]

This is one way to do it:

Create a new column G. Be sure to include a title for the column. Starting in row 2 of column G, enter the formula:

=E2&F2

Copy the formula down.

With the active cell anywhere within the table, select Data > Format > AutoFormat.

Now, on the drop-down arrow for the new column G, select Custom.

On the drop-down, select Contains, and in the blank box, type in the team name (i.e. Team A).

Hope that didn't confuse you.

Jason

[slamb999]

Jason,
Thanks for the idea but I am not sure that will work. You will never have Team A playing Team A. What I really need is an 'or' clause between the two columns. I need to sort on the entire workbook and to give me back the rows if E = Team A or F = Team A.

[jasoncw]

That is easy to do with VBA, but not with AutoFilter unfortunately. The purpose of column G is to have one column contain both teams. That way, when you filter, you can use "Contains" "Team A". That way, if either the home or away team is "Team A", it will show.

See attached dummy workbook to hopefully show you what I mean.

[slamb999]

Thanks as that did the trick. Would I need to create a VBA script if I want to create a drop down with all the team names on it and then have it filter fro the games? Should be much easier then the key strokes to set the filter with a contains clause.

[jasoncw]

Sure, it is a bit more complicated, but here is what I came up with. I created 3 named ranges, a cell with data validation to select the team name, and the VBA procedure to hide/unhide the rows.