Finding a similar golf course in an excel spreadsheet

[Bear Bag]

I have an Excel of 30,000 golf courses. The sheet has row 1 with titles of the columns. Column 1 is the name of a course, columns 2 to 19 are numbered 1 to 18 for each hole. Row 2 to Row 30,000 has a course name in column 1 and the par for each hole of that course in the columns 2-19. I need a formula to check each row for an exact par sequence of 4,3,4,5,4,3,4,4,5,5,3,4,4,3,4,4,4,5. My goal is to id all courses with this exact sequence of par.

 

[etaf]

theres probably a better way BUT an AND should do that - off top of head

=AND( B2=4, C2=3 , D2=4, E2=5, F2=4, G2=3, H2=4, I2=4, J2=5, K2=5, L2=3, M2=4, N2=4, O2=3, P2=4, Q2=4, R2=4, S2=5)
copy down the 30,000 rows and you will get a TRUE each time that row matches

 

[mumps]

I'm not sure how long this would take to run on 30000 rows but you could give it a try.

Sub compareRows()
    Application.ScreenUpdating = False
    Dim rng As Range
    Set rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
    Dim holes As Range
    Dim par As String
    Dim cel As Range
    For Each course In rng
        Set holes = Range("B" & course.Row & ":S" & course.Row)
        For Each cel In holes
            par = par & CStr(cel)
        Next cel
        If par = "434543445534434445" Then
            Range("B" & course.Row & ":S" & course.Row).Interior.ColorIndex = 3
        End If
        par = ""
    Next course
    Application.ScreenUpdating = True
End Sub

 

[jimrward]

I would create a helper column with all 18 holes joined together as 1 string and drag down, then use match or vlookup with any combination you want in a cell to find matches

 

[Fluff]

Another VBA option is

Sub GolfCourse()
   Dim Ary As Variant
   Dim Cnt As Long

   For Cnt = 2 To Range("B" & Rows.Count).End(xlUp).Row
      Ary = Join(Application.Transpose(Application.Transpose(Range("B" & Cnt).Resize(, 18))), ",")
      If Ary = "4,3,4,5,4,3,4,4,5,5,3,4,4,3,4,4,4,5" Then Range("A" & Cnt).Resize(, 19).Interior.Color = vbRed
   Next Cnt

End Sub

 

[markmzz]

I have an Excel of 30,000 golf courses. The sheet has row 1 with titles of the columns. Column 1 is the name of a course, columns 2 to 19 are numbered 1 to 18 for each hole. Row 2 to Row 30,000 has a course name in column 1 and the par for each hole of that course in the columns 2-19. I need a formula to check each row for an exact par sequence of 4,3,4,5,4,3,4,4,5,5,3,4,4,3,4,4,4,5. My goal is to id all courses with this exact sequence of par.

Maybe this in cell T2 and copy down:

=--(SUMPRODUCT(--($B2:$S2={4,3,4,5,4,3,4,4,5,5,3,4,4,3,4,4,4,5}))=18)

Markmzz

 

[AhoyNC]

Another option may be either FILTER or Advance Filter under DATA on the ribbon (menu).

 

[shg]

I'd use a formula to catenate the par values for each course as suggested, and then sort on that column. That would group all courses having the same par values.

As a curiosity, there are 44,152,809 ways that a course with par 3/4/5 holes could total 72 -- the coefficient of x^72 in (x^3 + x^4 + x^5)^18

 

[Rick Rothstein]

Here is another macro (no loops) that you can consider... it outputs the names of any course whose pars match the non-delimited list of pars assigned to the Pars variable (see red highlighted text) starting in a cell you chose (I used cell V1 for this example... see the blue highlighted text).

[table="width: 500"]
[tr]
	[td]Sub GolfCourses()
  Dim Pars As String, OutputCell As Range, Arr As Variant
  [B][COLOR="#0000FF"]Pars = "434543445534434445"[/COLOR][/B]
  [B][COLOR="#FF0000"]Set OutputCell = Range("V1")[/COLOR][/B]
  OutputCell.Resize(Rows.Count - OutputCell.Row + 1).ClearContents
  On Error GoTo NoMatch
  Arr = Application.Transpose(Split(Application.Trim(Join(Application.Transpose(Evaluate(Replace("IF(B2:B#&C2:C#&D2:D#&E2:E#&F2:F#&G2:G#&H2:H#&I2:I#&J2:J#&K2:K#&L2:L#&M2:M#&N2:N#&O2:O#&P2:P#&Q2:Q#&R2:R#&S2:S#=""" & Pars & """,A2:A#,"""")", "#", Cells(Rows.Count, "A").End(xlUp).Row)))))))
  OutputCell.Resize(UBound(Arr)) = Arr
  Exit Sub
NoMatch:
  OutputCell.Value = "** No matches **"
End Sub[/td]
[/tr]
[/table]

 

[Rick Rothstein]

I'd use a formula to catenate the par values for each course as suggested, and then sort on that column. That would group all courses having the same par values.

Here is a macro that will do the what you have suggested above (in case the OP wants to go this route)...

[table="width: 500"]
[tr]
	[td]Sub GolfCourses()
  Dim Arr As Variant
  Arr = Evaluate(Replace("""'""&B2:B#&C2:C#&D2:D#&E2:E#&F2:F#&G2:G#&H2:H#&I2:I#&J2:J#&K2:K#&L2:L#&M2:M#&N2:N#&O2:O#&P2:P#&Q2:Q#&R2:R#&S2:S#", "#", Cells(Rows.Count, "A").End(xlUp).Row))
  Range("T2").Resize(UBound(Arr)) = Arr
  Range("A2:T2").Resize(UBound(Arr)).Sort Range("T2"), xlAscending
End Sub[/td]
[/tr]
[/table]