Creating A List of Items Between Two Numbers

  • Thread starter Thread starter SteveC
  • Start date Start date
S

SteveC

I have two worksheets in the same workbook, "Filter" and "Data"

In worksheet Filter, Cell A1 is 40%, Cell B1 is 50%, Cells C1 to C100 is
where your "magic formula" goes, referencing Cells A1 and B1.

On worksheet Data, There is percentile data in BA13:BA6000
On worksheet Data, There are names in D13:D6000 --

In worksheet Filter, Cells C1:C100 I want to create a list of all names
(worksheet Data Column D) that contain values (worksheet Data Column BA)
between the range of numbers provided (worksheet Filter, cells A1 and B1).

Thank you !
 
One more thing.

On worksheet Filter, Cell D1 is "Category" data (apples, bananas, pears)

On worksheet Data, category data is in Column B

I only want to find the a list of names, for a specifc category, that are
within the range specified between worksheet Filter Cells A1 and B1.

thanks!
 
One more thing.  

On worksheet Filter, Cell D1 is "Category" data (apples, bananas, pears)

On worksheet Data, category data is in Column B

I only want to find the a list of names, for a specifc category, that are
within the range specified between worksheet Filter Cells A1 and B1.

thanks!
Click to expand...

Let's use column E on the Filter sheet as a helper column.

Put what's below in the Filter sheet.

In E1, put
=IF(AND(Data!B13=$D$1,Data!BA13>=$A$1,Data!BA13<=$B$1),
1,"")

In E2, put
=IF(AND(Data!B14=$D$1,Data!BA14>=$A$1,Data!BA14<=$B$1),
MAX(E$1:E1)+1,"")

Select E2 and copy down to row 5988.

In C1 put
=IF(ROW()>MAX(E:E),"",
OFFSET(Data!$D$13,MATCH(ROW(),E:E,0)-1,0))
and copy down to row 5988.

If names can repeat, and if you need to suppress duplicates, then use
Data > Filter > Advanced > unique only
on column C.

Modify to suit.
 
Back
Top