command to display specific rows based on value in one column

[thedunna]

Greetings all: This is my project. I have a worksheet with 100 rows of data. Each row has 20 columns with the final column displaying a result. What I would like to do is create a command by which I can display and compare only rows with a defined value in column A (which would be from 0-40). Example: I would like to show only data where column A is 0-12, another where column A is 13-20, and another where column A is greater than 20. Can anyone please help? I've always had great results using this forum, thanks in advance. Jim

[nathansav]

You can do this by using an array formula. I have attached an example for you to look at, the middle columns will do what you want.

[TMS]

Why can't you just use Autofilter?


Regards, TMS

[nathansav]

___________________________

[TMS]

@nathansav: nice. +1

A couple of slight amendments:

The formula in E2 could be:

=IFERROR(INDEX($A$2:$A$26,SMALL(IF($B$2:$B$26=E$1,ROW($A$2:$A$26)),ROWS($B$2:$B2))-1),"") committed with Ctrl-Shift-Enter.

Then, dragged across, only the column header reference changes.

And the Named Ranges could be made Dynamic Named Ranges so that the Data Validation drop down lists only show non-blank entries:

CPU_PARTS: =DATABASE!$F$2:INDEX(DATABASE!$F:$F,COUNTIF(DATABASE!$F:$F,">_"))
MOTHERBOARD_PARTS: =DATABASE!$G$2:INDEX(DATABASE!$G:$G,COUNTIF(DATABASE!$G:$G,">_"))
RAM_PARTS: =DATABASE!$E$2:INDEX(DATABASE!$E:$E,COUNTIF(DATABASE!$E:$E,">_"))


Regards, TMS

[thedunna]

I probable wasn't all that clear. I have attached a make-shift sample worksheet showing some data and what I am trying to accomplish. The displaying of results could be formatted differently (even on side-to-side worksheets) if I could figure out a way to leave out empty rows. I hope this helps... JimSample Medal Play.xlsx

[nathansav]

You would need to modify it Try dissecting what the examples do, and it will become clearer. F1 is good, as is Google.