How to return filtered results without using a filter

288enzo

Board Regular
Joined
Feb 8, 2009
Messages
104
Office Version
  1. 2016
Platform
  1. Windows
Hello - I have one sheet (sheet2) with data, on another sheet (sheet1) I would like to return filtered results of the data on sheet2. I'm working with Pro 2016.

Without a filter or pivot table.

Book1.xlsx
ABC
2Course #
3YES/NO1
4email1No
5email2Yes
6email3No
7email4Yes
8email5No
9email6Yes
10email7No
11email8No
12email9No
13email10No
Sheet2
Cell Formulas
RangeFormula
B4:B13B4=IF(COUNTIFS(AnnualTrackerData!L$2:L$20,A4,AnnualTrackerData!B$2:B$20,C$3),"Yes","No")
Cells with Data Validation
CellAllowCriteria
C3List1,2,3,4,5


Sheet1 would return a list from Sheet2 A:A if B=Yes.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,449
Office Version
  1. 2016
Platform
  1. Windows
Hi 288Enzo,

This should work

Book1
A
1Email
2email2
3email4
4email6
5 
Sheet1
Cell Formulas
RangeFormula
A2:A5A2=IFERROR(INDEX(Sheet2!$A$4:$A$9999,AGGREGATE(15,6,ROW(Sheet2!$A$4:$A$9999)-ROW(Sheet2!$A$3)/(Sheet2!$B$4:$B$9999="Yes"),ROW()-ROW($A$1))),"")
 
Solution

288enzo

Board Regular
Joined
Feb 8, 2009
Messages
104
Office Version
  1. 2016
Platform
  1. Windows
Hi 288Enzo,

This should work

Book1
A
1Email
2email2
3email4
4email6
5 
Sheet1
Cell Formulas
RangeFormula
A2:A5A2=IFERROR(INDEX(Sheet2!$A$4:$A$9999,AGGREGATE(15,6,ROW(Sheet2!$A$4:$A$9999)-ROW(Sheet2!$A$3)/(Sheet2!$B$4:$B$9999="Yes"),ROW()-ROW($A$1))),"")
Thanks, that worked. I had seen that formula before but didn't understand '15,6,ROW'. Can you explain that please?
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,449
Office Version
  1. 2016
Platform
  1. Windows
You're welcome.

Let me split it into its component parts.
AGGREGATE(15,6,ROW(Sheet2!$A$4:$A$9999)-ROW(Sheet2!$A$3)
/
(Sheet2!$B$4:$B$9999="Yes")
,ROW()-ROW($A$1)

AGGREGATE has many options. 15 is the SMALL option and 6 says ignore errors.
You can use the SMALL option to look for the kth smallest in a column of numbers but here I'm using the ROW(Sheet2!$A$4:$A$9999) to give me row numbers 4 to 9999 and I subtract ROW(Sheet2!$A$3) to give the correct index for INDEX(Sheet2!$A$4:$A$9999.

The (Sheet2!$B$4:$B$9999="Yes") will return a logical 1 when it finds a "Yes" in column B or logical 0 if not. That divided into the row numbers I'm searching will give a #DIV/0 error if logical zero which the 6 option ignores. If it's a logical 1 then the row divided by 1 returns the row number which the INDEX then selects.

The last option of AGGREGATE is the kth value so as we're using the SMALL option then 1 returns the lowest value (row), 2 returns the second lowest row, etc. So ROW()-ROW($A$1) starting in row 2 is 2-1 so I get the first, then in the next row 3-1 gives me the second, etc.
 

288enzo

Board Regular
Joined
Feb 8, 2009
Messages
104
Office Version
  1. 2016
Platform
  1. Windows
You're welcome.

Let me split it into its component parts.
AGGREGATE(15,6,ROW(Sheet2!$A$4:$A$9999)-ROW(Sheet2!$A$3)
/
(Sheet2!$B$4:$B$9999="Yes")
,ROW()-ROW($A$1)

AGGREGATE has many options. 15 is the SMALL option and 6 says ignore errors.
You can use the SMALL option to look for the kth smallest in a column of numbers but here I'm using the ROW(Sheet2!$A$4:$A$9999) to give me row numbers 4 to 9999 and I subtract ROW(Sheet2!$A$3) to give the correct index for INDEX(Sheet2!$A$4:$A$9999.

The (Sheet2!$B$4:$B$9999="Yes") will return a logical 1 when it finds a "Yes" in column B or logical 0 if not. That divided into the row numbers I'm searching will give a #DIV/0 error if logical zero which the 6 option ignores. If it's a logical 1 then the row divided by 1 returns the row number which the INDEX then selects.

The last option of AGGREGATE is the kth value so as we're using the SMALL option then 1 returns the lowest value (row), 2 returns the second lowest row, etc. So ROW()-ROW($A$1) starting in row 2 is 2-1 so I get the first, then in the next row 3-1 gives me the second, etc.
Thank you very much for taking the time to explain.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,787
Messages
5,638,312
Members
417,020
Latest member
MSVII

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
Top