Filter list using Excel formula

Question

I have a list of companies in column A of an Excel sheet and in column B I have a characteristic of the company. In another Excel sheet I want to get the list of companies in the first sheet, filtered by one of the characteristics (that could change, therefore I need an Excel formula to do this). I know that if I use the following formula: =IF(Sheet1!A1="Criteria",Sheet1!A1;"") and then copy the formula downwards, it works, but I would get blank cells in between. Any ideas?

Thanks in advance.

Answer 1

Assuming your company list is Sheet1!A2:A6 and characteristics list is Sheet1!B2:B6. Your criteria can be input in cell Sheet2!A1 and you can type the below formula in Sheet2!A2

{=IFERROR(INDEX(Sheet1!$A$2:$A$6,SMALL(IF(Sheet1!$B$2:$B$6=Sheet2!$A$1,ROW($A$2:$A$6)-ROW($A$1),""),ROW(A2)-1)),"")}

press CTRL+SHIFT+ENTER, then copy it through the output range Sheet2!A2:A6.

Answer 2

I'd suggest you use a PivotTable...because this is exactly the type of situation they were designed for. Turn your source list into an Excel Table using the Table icon from the Insert tab (or simply select a cell in your data and use the CTRL + T shortcut). Then make a PivotTable out of it, put the criteria field in the Page area, and whatever other columns you want in the ROWS area. Turn off Subtotals and change layout to Tabular in the Design tab, then copy your worksheet as many times as you have names. Then filter each PivotTable to only show individual names.

Even better, create the first PivotTable, then use the Show Pagefields functionality to automatically create new sheets each with a version of the PivotTable automatically filtered on each criteria. (Analyze>Options>Show Report Filter Pages). Check out YouTube for examples of both PivotTables in general and the Show Report Filter Pages magic trick in particular...look for videos from Mike Girven (ExcelIsFun) or Debra Dalgleish (Contextures) or Bill Jelen (Mr Excel) for starters.

PivotTables may seem daunting at first, but they are easy to master, and turn you into an Excel ninja.

Answer 3

Use

=INDEX(B:B, AGGREGATE(15, 6, ROW(A$1:INDEX(A:A, MATCH("zzz", A:A)))/(A$1:INDEX(A:A, MATCH("zzz", A:A))="criteria"), ROW(1:1)))

... and fill down.