Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Brian G Scully
 
Posts: n/a
Default What formula captures specified rows of data?

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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DOR
 
Posts: n/a
Default What formula captures specified rows of data?

Brian,

One way is to put the following array formula in every column of the
second sheet corresponding to a column in sheet1 and drag/copy it down,
assuming the temperature column is column A, headers in row 1 and 20
data rows sheet 1, and headers in row 1 of sheet 2.

In sheet 2 A2 put:

=IF(COUNTIF(Sheet1!$A$2:$A$21,"hot")<ROW(1:1),"",I NDEX(Sheet1!A:A,SMALL(IF(Sheet1!$A$2:$A$21="Hot",R OW(Sheet1!A$2:A$21),""),ROW(1:1))))

Confirm with Ctl-Shift-enter.

Another, more processing time-efficient approach, is to put a helper
column in Sheet1, say, column B and in B2 put:

=IF(A2="hot",ROW(),"") and copy down.

Then in A2 of sheet 2, put

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

and copy down - this is not an array formula, so you just enter it
normally. Just make sure you don't put a number in sheet1 B1.

You can now delete any columns in sheet2 that you don't need.

Adjust row numbers to suit your layout.

There are ways to make it more efficient - if you are interested, just
respond.

HTH

Declan O'R

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Brian G Scully
 
Posts: n/a
Default What formula captures specified rows of data?

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" wrote:

Brian,

One way is to put the following array formula in every column of the
second sheet corresponding to a column in sheet1 and drag/copy it down,
assuming the temperature column is column A, headers in row 1 and 20
data rows sheet 1, and headers in row 1 of sheet 2.

In sheet 2 A2 put:

=IF(COUNTIF(Sheet1!$A$2:$A$21,"hot")<ROW(1:1),"",I NDEX(Sheet1!A:A,SMALL(IF(Sheet1!$A$2:$A$21="Hot",R OW(Sheet1!A$2:A$21),""),ROW(1:1))))

Confirm with Ctl-Shift-enter.

Another, more processing time-efficient approach, is to put a helper
column in Sheet1, say, column B and in B2 put:

=IF(A2="hot",ROW(),"") and copy down.

Then in A2 of sheet 2, put

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

and copy down - this is not an array formula, so you just enter it
normally. Just make sure you don't put a number in sheet1 B1.

You can now delete any columns in sheet2 that you don't need.

Adjust row numbers to suit your layout.

There are ways to make it more efficient - if you are interested, just
respond.

HTH

Declan O'R


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DOR
 
Posts: n/a
Default What formula captures specified rows of data?

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(She et1!$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(She et1!$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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
sharing rows of data across multiple worksheets within a workbook deedle93 Excel Worksheet Functions 2 August 18th 05 01:24 AM
Matching Date From Two Groups wsteel Excel Worksheet Functions 3 June 25th 05 02:58 AM
Hiding Rows if the linked rows are blank KG Excel Discussion (Misc queries) 9 May 18th 05 02:32 AM
Pivot Table for survey data w/ questions as Rows & poss answrs as pfwebadmin Excel Discussion (Misc queries) 0 May 17th 05 02:31 PM
Sort pages? David Excel Discussion (Misc queries) 15 May 13th 05 11:33 PM


All times are GMT +1. The time now is 01:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"