winning/losing streak without dates in order

I have a tough one here:

I need to calculated a longest ever winning streak and current winning streak for a largest set of data.

The data is set up as follows:

Column A [B C D] [E F G] [H I J]
List of teams each brack represents a team
(can't be sorted) B / E / H all would be dates
C / F / I all would be W/L
D / G / J all would be scores

I need to determine the longest ever winning streak and current winning streak for each [bracketed] team. The problem is column A is fixed and can't be sorted, therefore the dates are out of order.

Please let me know if this is impossible.

Thanx
Chemmiah

Hi chemmiah,

A sample worksheet would be very helpful to understand this problem. Click the green Edit button below your original message, Then Go Advanced, Then click on the PaperClip icon above the message area to attach a sample file.

Hi chemmiah,

I love basketball stats but the structure of your data keeps me from helping as it is. To give you some other ideas I took your Sheet1 and converted it (using sheet2) to a Table/List on Sheet3. The reason for doing this was to show you how to use Advanced Filters and Pivot Tables which would make it a lot easier to deal with your data.

If you had Conference, Region and Section attached to each team you could filter by them as well as just team name. That would be really cool. I didn't want to do your work for you so I didn't include them.

On the Pivot Table you can do many more things than I've shown. Filtering and grouping and totaling by different fields can be done.

Using Advanced Filters would be the easiest way to calculate winning streaks. Afte the filter (shown on page 3) a pretty simple formula can be used to get the max winning streak.

Look at Sheet3, Study a little Advanced Filters and Pivot Tables and see why the Structure of the Data on Sheet3 allows Excel to work with your data better. Also on the Sheet3 table, both teams need to be entered using the same date which is the secret to getting the tables to work.

See if this might be a different/better way to get your results.