Two formulas needed

[SplatP1]

Hello,

I need help with 2 formulas please.
Unfiltered data is in Sheet1!B2:BB1000
Sheet2!A3 is a drop down list of all country names (Sheet1!AW2:AW1000)

First formula needed for Sheet1!A2:
If the dropdown country on Sheet2!A3 matches the country in Sheet1!AW2, then put a "1" in Sheet1!A2, if not, return a "0"

Second formula needed for Sheet2!A4:
If Sheet1!A:A contains a "1", then show all matching rows (all data) on Sheet2!B:BB

Hope it makes sense!
Thanks in advance.
Owen

 

[SplatP1]

That works

Now one last thing, is there a way to list them all on Sheet 2 without the empty rows?
So if they are filtered and there are 50 results, then only the 50 are shown with no empty rows?

 

[tyija1995]

Here is a solution that uses some VBA code with a command button:

VBA codes:

Sub defaultRows()
    Rows("2:1000").Select
    Selection.EntireRow.Hidden = False
End Sub

Sub tidyRows()
    Dim i As Integer
        For i = 2 To 1000
            If Sheets("Sheet1").Cells(i, 1).Value = 0 Then
                Rows(i).EntireRow.Hidden = True
            End If
        Next i
End Sub

Insert a command button in sheet 2,

Private Sub CommandButton1_Click()
    defaultRows
    tidyRows
End Sub

Then just click the command button to filter the rows - you can use the defaultRows procedure individually to unhide the rows back to default.