Filter based on condition
- Thread starter Techtrend
- Start date
p45cal
Well-Known Member
The following seems to work on your sample file well. Note that it doesn't return WOCKPHARMA because although you've highlighted K13, 100 is not double 75 or more. I've written a little macro to do this automatically but it can be done manually:
1.Convert the two tables at the top (they do need to be in the same order by the way) into one Table by selecting them both (A1:K15), and in the Insert tab of the Ribbon, choose Table from the Tables section on the left. Then in the dialogue box tick the box My table has headers and click OK.
This will alter the headers and add one header between the original 2 tables. It will also add colouring, which you can remove if you want (the macro does do this).
2. Add this formula to cell P2:
=OR((J2/D2)>=2,(K2/E2)>=2)
3. Copy cells G1:K1 (the headers of the original second table) and paste to G20
4. Go to the data tab ofthe ribbon and choose Advanced from the Sort & Filter section. In the dialogue box that comes up,
choose Copy to another location,
for the List range: bit select the whole new table (it might look like: Sheet1!$A$1:$K$15),
for the Criteria range: select P1 : P2 (this is the formula and a blank cell above it)
for the Copy to: bit select the headers you copied earlier ($G$20:$K$20)
Click OK.
You should now have your list.
5. Tidy up:
Convert that table to a normal range by selecting a cell within it and going to the Table tools, Design tab of the ribbon and choosing Convert to range.
6. Delete the formula in P2.
The above does leave the headers of the second table 'adjusted'. as well as leaving the header between the two tables.
The macro does the same but returns the sheet more like its original state.
Click the button in the attached to run the macro.
I'm going to check it works in Excel 2003 too (since you've posted an .xls file rather than a .xlsx file…)
1.Convert the two tables at the top (they do need to be in the same order by the way) into one Table by selecting them both (A1:K15), and in the Insert tab of the Ribbon, choose Table from the Tables section on the left. Then in the dialogue box tick the box My table has headers and click OK.
This will alter the headers and add one header between the original 2 tables. It will also add colouring, which you can remove if you want (the macro does do this).
2. Add this formula to cell P2:
=OR((J2/D2)>=2,(K2/E2)>=2)
3. Copy cells G1:K1 (the headers of the original second table) and paste to G20
4. Go to the data tab ofthe ribbon and choose Advanced from the Sort & Filter section. In the dialogue box that comes up,
choose Copy to another location,
for the List range: bit select the whole new table (it might look like: Sheet1!$A$1:$K$15),
for the Criteria range: select P1 : P2 (this is the formula and a blank cell above it)
for the Copy to: bit select the headers you copied earlier ($G$20:$K$20)
Click OK.
You should now have your list.
5. Tidy up:
Convert that table to a normal range by selecting a cell within it and going to the Table tools, Design tab of the ribbon and choosing Convert to range.
6. Delete the formula in P2.
The above does leave the headers of the second table 'adjusted'. as well as leaving the header between the two tables.
The macro does the same but returns the sheet more like its original state.
Click the button in the attached to run the macro.
I'm going to check it works in Excel 2003 too (since you've posted an .xls file rather than a .xlsx file…)