Column Counting

M

MHau5

New Member
Joined
Oct 23, 2021
Messages
26
Office Version
  1. 2019
Platform
  1. Windows
There's a file I have that shows the full season schedule with score results from a pro sports league last year. I have five columns in the file:

Column A: contains list of dates (868 dates to be exact, this is all the games that were played that season)
Column B: the name of the team
Column C: the name of the opponent that the team from Column B played on that night.
Column D: the number of goals that the team from Column B scored
Column E: the number of goals that the team from Column C scored

In sports, there are obviously days off in between games. Sometimes it's one day off, two days, three, four, and sometimes there aren't any. What I'm wanting to do is run a query that identifies how many times the team from Column B had:

1.) Games played with no days of rest in between games
2.) Games played with one day of rest in between games
3.) Games played with two days of rest in between games
4.) Games played with three days of rest in between games
5.) Games played with four or more days of rest in between games

Ideally it would be great if it identified how many times instances 1-5 appeared without considering who the team is, but I get that might be difficult if you don't actually put the team name from Column B in the query.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
If you posted you data in a manner which could be copied (xl2bb), or downloaded (dropbox, etc.), you would probably get an answer real quick...
 
Upvote 0
MHau5 said:
Here is the mini table XL2BB format...
Click to expand...
New NHL Model.xlsx
ABCDEF
1DateVisitorGHomeG2Column1
21/13/2021St. Louis Blues4Colorado Avalanche1
31/13/2021Vancouver Canucks5Edmonton Oilers3
41/13/2021Pittsburgh Penguins3Philadelphia Flyers6
51/13/2021Chicago Blackhawks1Tampa Bay Lightning5
61/13/2021Montreal Canadiens4Toronto Maple Leafs5OT
71/14/2021San Jose Sharks4Arizona Coyotes3SO
81/14/2021Washington Capitals6Buffalo Sabres4
91/14/2021Carolina Hurricanes3Detroit Red Wings0
101/14/2021Vancouver Canucks2Edmonton Oilers5
111/14/2021Minnesota Wild4Los Angeles Kings3OT
121/14/2021Boston Bruins3New Jersey Devils2SO
131/14/2021Columbus Blue Jackets1Nashville Predators3
141/14/2021New York Islanders4New York Rangers0
151/14/2021Anaheim Ducks2Vegas Golden Knights5
161/14/2021Calgary Flames3Winnipeg Jets4OT
171/15/2021Washington Capitals2Buffalo Sabres1
181/15/2021St. Louis Blues0Colorado Avalanche8
191/15/2021Toronto Maple Leafs3Ottawa Senators5
201/15/2021Pittsburgh Penguins2Philadelphia Flyers5
211/15/2021Chicago Blackhawks2Tampa Bay Lightning5
221/16/2021San Jose Sharks3Arizona Coyotes5
231/16/2021Vancouver Canucks0Calgary Flames3
241/16/2021Carolina Hurricanes2Detroit Red Wings4
251/16/2021Montreal Canadiens5Edmonton Oilers1
261/16/2021Minnesota Wild4Los Angeles Kings3OT
271/16/2021Boston Bruins1New Jersey Devils2OT
281/16/2021Columbus Blue Jackets2Nashville Predators5
291/16/2021New York Islanders0New York Rangers5
301/16/2021Toronto Maple Leafs3Ottawa Senators2
311/16/2021Anaheim Ducks1Vegas Golden Knights2OT
321/17/2021Chicago Blackhawks2Florida Panthers5
331/17/2021Washington Capitals3Pittsburgh Penguins4SO
341/18/2021Minnesota Wild0Anaheim Ducks1
351/18/2021Vancouver Canucks2Calgary Flames5
361/18/2021Columbus Blue Jackets3Detroit Red Wings2
371/18/2021Montreal Canadiens3Edmonton Oilers1
381/18/2021Carolina Hurricanes4Nashville Predators2
391/18/2021Boston Bruins0New York Islanders1
401/18/2021Buffalo Sabres6Philadelphia Flyers1
411/18/2021San Jose Sharks4St. Louis Blues5
421/18/2021Winnipeg Jets1Toronto Maple Leafs3
431/18/2021Arizona Coyotes2Vegas Golden Knights4
441/19/2021Columbus Blue Jackets2Detroit Red Wings3OT
451/19/2021Chicago Blackhawks4Florida Panthers5OT
461/19/2021Colorado Avalanche3Los Angeles Kings2
471/19/2021New Jersey Devils4New York Rangers3
481/19/2021Winnipeg Jets4Ottawa Senators3OT
491/19/2021Buffalo Sabres0Philadelphia Flyers3
501/19/2021Washington Capitals4Pittsburgh Penguins5OT
511/20/2021Minnesota Wild3Anaheim Ducks2
521/20/2021San Jose Sharks2St. Louis Blues1SO
531/20/2021Edmonton Oilers3Toronto Maple Leafs1
541/20/2021Montreal Canadiens5Vancouver Canucks6SO
551/20/2021Arizona Coyotes2Vegas Golden Knights5
561/21/2021Philadelphia Flyers4Boston Bruins5SO
571/21/2021Tampa Bay Lightning3Columbus Blue Jackets2OT
581/21/2021Colorado Avalanche2Los Angeles Kings4
591/21/2021New Jersey Devils1New York Islanders4
601/21/2021Winnipeg Jets4Ottawa Senators1
611/21/2021Montreal Canadiens7Vancouver Canucks3
621/22/2021Colorado Avalanche3Anaheim Ducks2OT
631/22/2021Vegas Golden Knights2Arizona Coyotes5
641/22/2021Detroit Red Wings1Chicago Blackhawks4
651/22/2021Nashville Predators0Dallas Stars7
661/22/2021San Jose Sharks1Minnesota Wild4
671/22/2021New York Rangers3Pittsburgh Penguins4SO
681/22/2021Edmonton Oilers2Toronto Maple Leafs4
691/22/2021Buffalo Sabres3Washington Capitals4SO
701/23/2021Philadelphia Flyers1Boston Bruins6
711/23/2021Tampa Bay Lightning2Columbus Blue Jackets5
721/23/2021Los Angeles Kings2St. Louis Blues4
731/23/2021Montreal Canadiens5Vancouver Canucks2
741/23/2021Ottawa Senators3Winnipeg Jets6
751/24/2021Colorado Avalanche1Anaheim Ducks3
761/24/2021Vegas Golden Knights1Arizona Coyotes0
771/24/2021Toronto Maple Leafs3Calgary Flames2
781/24/2021Detroit Red Wings2Chicago Blackhawks6
791/24/2021Nashville Predators2Dallas Stars3
801/24/2021San Jose Sharks5Minnesota Wild3
811/24/2021New York Islanders0New Jersey Devils2
821/24/2021New York Rangers2Pittsburgh Penguins3
831/24/2021Los Angeles Kings6St. Louis Blues3
841/24/2021Edmonton Oilers4Winnipeg Jets3
851/24/2021Buffalo Sabres4Washington Capitals3SO
861/25/2021Ottawa Senators1Vancouver Canucks7
871/26/2021Anaheim Ducks1Arizona Coyotes0
881/26/2021Pittsburgh Penguins2Boston Bruins3OT
891/26/2021New York Rangers2Buffalo Sabres3
901/26/2021Florida Panthers4Columbus Blue Jackets3SO
911/26/2021Toronto Maple Leafs4Calgary Flames3
921/26/2021San Jose Sharks3Colorado Avalanche7
931/26/2021Detroit Red Wings1Dallas Stars2OT
941/26/2021Los Angeles Kings2Minnesota Wild1
951/26/2021Philadelphia Flyers5New Jersey Devils3
961/26/2021Chicago Blackhawks2Nashville Predators3OT
971/26/2021St. Louis Blues5Vegas Golden Knights4SO
981/26/2021Edmonton Oilers4Winnipeg Jets6
991/26/2021New York Islanders2Washington Capitals3
1001/27/2021Chicago Blackhawks1Nashville Predators2SO
1011/27/2021Ottawa Senators1Vancouver Canucks5
1021/28/2021Anaheim Ducks2Arizona Coyotes3
1031/28/2021Pittsburgh Penguins1Boston Bruins4
1041/28/2021New York Rangers3Buffalo Sabres2OT
1051/28/2021Tampa Bay Lightning0Carolina Hurricanes1OT
1061/28/2021Florida Panthers2Columbus Blue Jackets3SO
1071/28/2021San Jose Sharks0Colorado Avalanche3
1081/28/2021Detroit Red Wings3Dallas Stars7
1091/28/2021Toronto Maple Leafs4Edmonton Oilers3
1101/28/2021Los Angeles Kings3Minnesota Wild5
1111/28/2021Calgary Flames2Montreal Canadiens4
1121/28/2021Philadelphia Flyers3New Jersey Devils1
1131/28/2021Ottawa Senators1Vancouver Canucks4
1141/28/2021New York Islanders3Washington Capitals6
1151/29/2021Columbus Blue Jackets2Chicago Blackhawks1
1161/30/2021St. Louis Blues6Anaheim Ducks1
1171/30/2021New Jersey Devils3Buffalo Sabres4SO
1181/30/2021Dallas Stars1Carolina Hurricanes4
1191/30/2021Florida Panthers3Detroit Red Wings2OT
1201/30/2021Toronto Maple Leafs3Edmonton Oilers4OT
1211/30/2021Colorado Avalanche5Minnesota Wild1
1221/30/2021Calgary Flames2Montreal Canadiens0
1231/30/2021Pittsburgh Penguins5New York Rangers4OT
1241/30/2021New York Islanders2Philadelphia Flyers3OT
1251/30/2021Nashville Predators3Tampa Bay Lightning4
1261/30/2021Vancouver Canucks4Winnipeg Jets1
1271/30/2021Boston Bruins3Washington Capitals4OT
1281/31/2021St. Louis Blues4Anaheim Ducks1
1291/31/2021New Jersey Devils5Buffalo Sabres3
1301/31/2021Dallas Stars3Carolina Hurricanes4SO
1311/31/2021Columbus Blue Jackets1Chicago Blackhawks3
1321/31/2021Florida Panthers3Detroit Red Wings2
1331/31/2021Ottawa Senators5Edmonton Oilers8
1341/31/2021Colorado Avalanche3Minnesota Wild4OT
1351/31/2021New York Islanders3Philadelphia Flyers4OT
Schedule
 
Upvote 0
igold said:
If you posted you data in a manner which could be copied (xl2bb), or downloaded (dropbox, etc.), you would probably get an answer real quick...
Click to expand...
Posted it above, thank you for the suggestion.
 
Upvote 0
Given your sample data, does this do what you want...

VBA Code: 
Sub Resting()

    Dim arr, desc
    Dim lRow As Long
    Dim ws As Worksheet: Set ws = ActiveSheet
    
    Application.ScreenUpdating = False
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    arr = Range("A2:F" & lRow)
    Range("A1:F" & lRow).Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range("B2:B" & lRow _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range("A2:A" & lRow _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("A1:F" & lRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    With ws
        .Range("G2").Formula = "=if(B2=B3,A3-A2,"""")"
        .Range("G2").AutoFill Destination:=.Range("G2:G" & lRow)
        .Range("H2").Formula = "=COUNTIF($G$3:$G$135,0)"
        .Range("H3").Formula = "=COUNTIF($G$3:$G$135,1)"
        .Range("H4").Formula = "=COUNTIF($G$3:$G$135,2)"
        .Range("H5").Formula = "=COUNTIF($G$3:$G$135,3)"
        .Range("H6").Formula = "=COUNTIF($G$3:$G$135,"">=4"")"
        .Range("H2:H6").Copy
        .Range("H2").PasteSpecial Paste:=xlPasteValues
        .Range("A2").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
        desc = Array("Status", "No Days Rest", "One Days Rest", "Two Days Rest", "Three Days Rest", "Four or More Days Rest")
        .Range("G:G").ClearContents
        .Range("G1").Resize(UBound(desc) + 1) = Application.Transpose(desc)
        .Range("H1") = "Qty"
        .Range("A1").Select
    End With
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
igold said:
Given your sample data, does this do what you want...

VBA Code: 
Sub Resting()

    Dim arr, desc
    Dim lRow As Long
    Dim ws As Worksheet: Set ws = ActiveSheet
   
    Application.ScreenUpdating = False
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    arr = Range("A2:F" & lRow)
    Range("A1:F" & lRow).Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range("B2:B" & lRow _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range("A2:A" & lRow _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("A1:F" & lRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
    With ws
        .Range("G2").Formula = "=if(B2=B3,A3-A2,"""")"
        .Range("G2").AutoFill Destination:=.Range("G2:G" & lRow)
        .Range("H2").Formula = "=COUNTIF($G$3:$G$135,0)"
        .Range("H3").Formula = "=COUNTIF($G$3:$G$135,1)"
        .Range("H4").Formula = "=COUNTIF($G$3:$G$135,2)"
        .Range("H5").Formula = "=COUNTIF($G$3:$G$135,3)"
        .Range("H6").Formula = "=COUNTIF($G$3:$G$135,"">=4"")"
        .Range("H2:H6").Copy
        .Range("H2").PasteSpecial Paste:=xlPasteValues
        .Range("A2").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
        desc = Array("Status", "No Days Rest", "One Days Rest", "Two Days Rest", "Three Days Rest", "Four or More Days Rest")
        .Range("G:G").ClearContents
        .Range("G1").Resize(UBound(desc) + 1) = Application.Transpose(desc)
        .Range("H1") = "Qty"
        .Range("A1").Select
    End With
   
    Application.ScreenUpdating = True
   
End Sub
Click to expand...
I'm not sure how I can test this in Excel. Do you have a suggestion?
 
Upvote 0
Do you know how to copy the macro to your workbook...
 
Upvote 0
If not see this easy walk through...

www.excelguru.ca

Adding VBA Code For The First Time User

It's always nice when you go to a forum and someone gives you a nice bit of VBA code that is supposed to accomplish your goals. But if you've never used VBA code before, it's kind of hard to know … Continue reading →
www.excelguru.ca www.excelguru.ca
 
Upvote 0
igold said:
If not see this easy walk through...

www.excelguru.ca

Adding VBA Code For The First Time User

It's always nice when you go to a forum and someone gives you a nice bit of VBA code that is supposed to accomplish your goals. But if you've never used VBA code before, it's kind of hard to know … Continue reading →
www.excelguru.ca www.excelguru.ca
Click to expand...
Here's what it does....

Oct. 29, 2021.xltm
ABCDEFG
1DateVisitorGHomeG2Column1Status
21/13/2021St. Louis Blues4Colorado Avalanche1No Days Rest
31/13/2021Vancouver Canucks5Edmonton Oilers3One Days Rest
41/13/2021Pittsburgh Penguins3Philadelphia Flyers6Two Days Rest
51/13/2021Chicago Blackhawks1Tampa Bay Lightning5Three Days Rest
61/13/2021Montreal Canadiens4Toronto Maple Leafs5OTFour or More Days Rest
71/14/2021San Jose Sharks4Arizona Coyotes3SO
81/14/2021Washington Capitals6Buffalo Sabres4
91/14/2021Carolina Hurricanes3Detroit Red Wings0
101/14/2021Vancouver Canucks2Edmonton Oilers5
111/14/2021Minnesota Wild4Los Angeles Kings3OT
121/14/2021Boston Bruins3New Jersey Devils2SO
131/14/2021Columbus Blue Jackets1Nashville Predators3
141/14/2021New York Islanders4New York Rangers0
151/14/2021Anaheim Ducks2Vegas Golden Knights5
161/14/2021Calgary Flames3Winnipeg Jets4OT
171/15/2021Washington Capitals2Buffalo Sabres1
181/15/2021St. Louis Blues0Colorado Avalanche8
191/15/2021Toronto Maple Leafs3Ottawa Senators5
201/15/2021Pittsburgh Penguins2Philadelphia Flyers5
211/15/2021Chicago Blackhawks2Tampa Bay Lightning5
221/16/2021San Jose Sharks3Arizona Coyotes5
231/16/2021Vancouver Canucks0Calgary Flames3
241/16/2021Carolina Hurricanes2Detroit Red Wings4
251/16/2021Montreal Canadiens5Edmonton Oilers1
261/16/2021Minnesota Wild4Los Angeles Kings3OT
271/16/2021Boston Bruins1New Jersey Devils2OT
281/16/2021Columbus Blue Jackets2Nashville Predators5
291/16/2021New York Islanders0New York Rangers5
301/16/2021Toronto Maple Leafs3Ottawa Senators2
Schedule


So essentially in a way it does what I want but at the same time it's not. For example, in the Xl2bb image above you can see dates ranging from 1/13 - 1/16 with the teams. I'll use the St. Louis Blues for example:

I'd like for it to go through and identify how many days there were between their last game. So using the 1/13-1/16 date range, it appears that the St. Louis Blues played two games, and those games were two days apart.

How does that sound?
 
Upvote 0
Not 100% sure what you want. Can you post an example with what your expected results are. Your quote below from your Post #1 is confusing me.

Ideally it would be great if it identified how many times instances 1-5 appeared without considering who the team is
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Calculate time between occurrences
Replies
1
Views
285
JGordon11
JGordon11
B
  • Question Question
Count the occurrence of a variable
Replies
1
Views
190
Alex Blakenburg
A
A
  • Solved
VBA code to extract a table
Replies
5
Views
237
Anthony47
A
B
  • Solved
Rank winning percentage for sport game
Replies
9
Views
2K
Peter_SSs
Peter_SSs
M
  • Solved
Looking for a more efficient method for point scoring
Replies
4
Views
184
Kaper
Kaper

Forum statistics

Threads
1,223,912
Messages
6,175,339
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top