Creating A List of Items Between Two Numbers

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 !
 
S

SteveC

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!
 
Z

zvkmpw

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!

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top