What formula captures specified rows of data?

[Brian G Scully]

I need to capture rows of information from a worksheet, where the only
specific
identification in the row would be (say) a word "Hot".

I expect to have a quantity of rows of data (say) 20 rows, in one column
each row would say either "Cold", "Warm" or "Hot"

I would like to create a 2nd sheet that captures ONLY the "HOT" rows?

Is this possible? Can anyone point me to reference material or examples
please.

Thank you in advance for your time...

Kind Regards,

Brian

I am using MS Excel XP

 

[Brian G Scully]

Declan - Thank you so much for your help - this is fab!! I never thought
that I could make sense of this at all. I would like to make it more
efficient, if possible.

I think I prefer Option2, though I would like to be able to have the
solution start on any row in Sheet 2 (not necessarily on row 2). At the
moment it looks to me as if Row 1 has to remain blank (no data) and if I try
to insert rows above Row 1, therefore moving my solution down the page - it
disappears.

I am planning to use this to bring together data from 9 different workbooks
- it is in a sales enviroment and all I am interested in reviewing is the
"Hot" prospects! It works great capturing data from another workbook.

I am fascinated to understand what the word "SMALL" does in the formula - I
have tried to "read" the formula to make sense of it - I get some of it but
much is way beyond my level of understanding. If I could push you to write
out the formula in words I would be so grateful.

Thank you again for all your help and kind regards,

Brian

 

[DOR]

Glad to hear it is working, albeit, after a fashion. And yes, I did
not design it to be impervious to insertions, which is something I
normally do for myself, but thought it might complicate the formulas
more than necessary. However, we can do it. Put the following formula
in sheet 2, cell A10, where row 10 is the first row you want to use on
sheet 2 initially - substitute another value for 10 if you want to put
it elsewhere initially:

=IF(COUNTIF(Sheet1!$A:$A,"hot")<ROW()-ROW($10:$10)+1,"",INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$B:$B,ROW()-ROW($10:$10)+1),Sheet1!$B:$B)))

Drag down and across. Now you can insert or delete rows, except for
the first one in the list, to your heart's content, so that the list no
longer starts in row 10. You can also copy/insert a row in the middle
of the list if you wish without affecting the result.

As for the formula in words, here is my best attempt, which tends to be
from the inside out:

The expression ROW()-ROW($10:$10)+1, which occurs in two places,
generates a number which we can call N, where N is the list row number
within the list being created, e.g., the first row in the list is row
one, even though it may be on row 10 of the spreadsheet, second row is
row 2, etc. It does this by subtracting the sheet row number of the
first row in the list, ROW($10:$10), from the current sheet row number,
ROW(), and adding one.

The SMALL function uses N to generate a number, which we will call R,
and which is the Nth smallest value in the helper column in sheet 1,
e.g. if we are on the first row of the new list, it extracts the first
smallest value, which happens to be the sheet row number of the first
"hot" prospect on sheet 1. If we are on the fourth row it extracts the
fourth smallest value, which will be the sheet of the fourth hot
prospect on sheet 1, and so on.

The INDEX function uses R to index into sheet 1 and pull the value from
the Rth row in each column of sheet 1.

Finally, if N is greater than the number of hot prospects, insert a
blank (null) in the cell, else do all of the above.

To make it (slightly) more efficient, even though it may not be worth
while unless your prospects are on the thousands, put this formula
anywhere you can find to put it conveniently, EXCEPT in the the header
of your helper column in sheet 1, which must not contain a number:

=COUNTIF(Sheet1!$A:$A,"hot")

Then change the formula above to

=IF(hotcount<ROW()-ROW($10:$10)+1,"",INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$B:$B,ROW()-ROW($10:$10)+1),Sheet1!$B:$B)))

where "hotcount" is an absolute reference to the cell in which you put
the COUNTIF formula.

You could also extract the ROW()-ROW($10:$10)+1 expression and place it
in, say, col A of sheet 2 but I doubt that you will notice any
difference unless you are dealing with thousands of "hot" prospects,
which, almost by defintion, could hardly be true.

Hope this helps.

Regards

Declan O'R