Two formulas needed

SplatP1

Board Regular
Joined
Jun 15, 2010
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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
 
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?
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Here is a solution that uses some VBA code with a command button:

VBA codes:

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

Code:
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,

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,179
Members
453,021
Latest member
Justyna P

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