Search 2 Columns For A Value and Retrieve Values Needed

[mjgcancio]

Hi all.

I'm having a difficulty in figuring out this.

I have the attached spreadsheet with 2 sheets, "ALL" and "GAME TABLES".
In sheet "ALL" I have the values needed to fill sheet "GAMES TABLE", in which the criteria for searching teams are cells D1 and F1.

The tables needed to fill are in ranges:

("I:N")-All games Home Team (Home and Away)
("P:U")-All games Away Team (Home and Away)
("W:AB")-All games Home team when playing Home
("AD:AI")-All games Away team when playing Away

I've googled for a solution and found the following code:


Sub LoopThroughColumns() Dim ws1, ws2 As Worksheet
Dim lastRow As Long, i, j As Long
Dim valueA1 As String

' Set the worksheet object
Set ws1 = ThisWorkbook.Worksheets("ALL") ' Replace "Sheet1" with your sheet name
Set ws2 = ThisWorkbook.Worksheets("GAME TABLES") ' Replace "Sheet2" with your sheet name

' Get the last row in column A
lastRow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row

' Get the value of cell D1
valueD1 = ws2.Range("D1").Value

' Loop through the columns E and F
For i = 5 To 6 ' Change the column numbers as per your requirement
' Loop through the rows
For j = 2 To lastRow
' Check if the value in cell D1 matches the value in column E
If ws1.Cells(j, 1).Value = valueA1 Then
' Retrieve the values from columns B and C
Dim valueB As Variant
Dim valueC As Variant

valueB = ws1.Cells(j, i).Value
valueC = ws1.Cells(j, i + 1).Value

' Do something with the retrieved values
' For example, print them in the Immediate Window
Debug.Print "Value of column B: " & valueB
Debug.Print "Value of column C: " & valueC
End If
Next j
Next i
End Sub




But now I'm stuck in advancing for the rest, and could use a little help.

Thanks in advanced, best regards
MC

[p45cal]

In the attached, the ALL sheet data has been converted to a proper Excel table; this makes the solutions continue to work when you add/remove data to/from tht table automatically.
3 offerings:
1. The GAME TABLES macro sheet contains an Advanced Filter solution. Note that there are hidden rows (2 to 5) on the sheet, and a button to run the macro.
2. The GAME TABLES formulae sheet contains 4 formulae in cells I2, P2, W2 & AD2. This sheet should automatically update itself.
3. The GAME TABLES PQ sheet contains 4 Power Query tables. These need Refreshing if the data on sheet ALL changes; you can do this either by clicking the Refresh All button in the Queries & Connections section on the Data tab of the ribbon, or by right-clicking each table and choosing Refresh. We could add a button to do this. You will also note that cell D1 is a named range called HomeTeam and that cell F1 is a named range called AwayTeam. These are necessary for the Power Queries to work.

[mjgcancio]

Many, many thanks. It worked like a charm. :yes

The solutions were amazing.

Best wishes and regards,
MC