Copying rows identified by countifs function to seperate worksheet

  • Thread starter Thread starter JRD
  • Start date Start date
J

JRD

In excel 2007 is there a way of copying rows identified by the countifs
function, into a seperate sheet

For example:

A B C
1 John Yes Top
2 Peter No Bottom
3 Jack Yes Top
4 Paul No Bottom
5 Mike No Top
6 Harry Yes Bottom

If I had the following function: =countifs(B:B, "Yes", C:C, "Top"), the
answer would be 2, but is there then a way to copy the rows where both B is
yes and C is top (i.e. rows 1 and 3) in their entirety to a new worksheet.
This is just a short example. In reality my countifs functions contain more
than 2 criteria (sometimes 6 or 7).

Many thanks

John
 
This simple dynamic play should work in any version of Excel
Assume your source data as posted is in sheet: x, cols A to C, data in row1
down
In another sheet:
In A1: =IF(AND(x!B1="Yes",x!C1="Top"),ROW(),"")
In B1: =IF(ROW()>COUNT($A:$A),"",INDEX(x!A:A,SMALL($A:$A,ROW())))
Copy B1 to D1. Select A1:D1, fill down to cover the max expected extent of
source data, eg down to D100. Minimize/hide col A. Cols B to D returns the
required lines satisfying the criteria spelled out in col A, all neatly
bunched at the top. Easily modify col A to suit whatever complex
multi-criteria that you may have. Inspiring? hit the YES below
 
Thanks Max

What if the source date is in sheet: x, cols A to C, data in row 2 down

Also, is there a way in which I can search for things specified in another
cell, i.e. instead of putting "Yes" in the formula to search for "Yes" in
column B, can I use a spare cell, say E1 to enter what I want to search for
i.e. the following formula:

In A1: =IF(AND(x!B1=E1,x!C1="Top"),ROW(),"")

Thanks

John
 
.. can I use a spare cell, say E1 to enter what I want to search for
i.e. the following formula:
In A1: =IF(AND(x!B1=E1,x!C1="Top"),ROW(),"")
Click to expand...

Yes, of course, naturally. Just fix the points with $ signs before you copy
down, eg for the above, use in A1: =IF(AND(x!B1=$E$1,x!C1="Top"),ROW(),"")
What if the source date is in sheet: x, cols A to C, data in row 2 down
Click to expand...
You could use this set in the extract sheet. This assumes that you wish to
extract it in row2 down:
In A2: =IF(AND(x!B2="Yes",x!C2="Top"),ROW(),"")
In B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(x!A:A,SMALL($A:$A,ROWS($1:1))))
Rest of the construct similar

For extraction constructs that do not start in row 1, I'd use ROWS($1:1) to
replace ROW() which is row sensitive. ROWS($1:1) ensures that the series
always starts at 1 irrespective of the row that the formula is placed in.
 
Thank you Max, works brilliantly!

Lets say that E1 in the extract sheet contains the text: elective, and that
I wanted to copy across from source to extract sheet those rows where column
B contains somewhere in the cell elective, but not necessarily just elective

Could I use something like:

In A1: =IF(AND(x!B1="*" & E1 & "*",x!C1="Top"),ROW(),"")

or does the "*" symbol not work with the AND function like it does with the
countifs function?

Also if I wanted anything in B column (don't ask why!) can I do the following:

In A1: =IF(AND(x!B1="*",x!C1="Top"),ROW(),"")

Many thanks once again

John
 
1. > =IF(AND(x!B1="*" & E1 & "*",x!C1="Top"),ROW(),"")
Use: =IF(AND(ISNUMBER(SEARCH(E1,x!B1)),x!C1="Top"),ROW(),"")

2. > =IF(AND(x!B1="*",x!C1="Top"),ROW(),"")
Use: =IF(AND(x!B1<>"",x!C1="Top"),ROW(),"")

The above should work for you. Close it off here.
Start new threads for any new queries
 
Back
Top