IF column contains certain text return value of corresponding row
Daly
of the corresponding row from another column?
Column A has names, Column G has "x" that I used to mark the row where I
would like the names referenced on a different sheet? For example
Column A Column B
Ann X
Bob
Carol X
Doug X
Eric
Fred X
Greg
I only want a list that shows:
Ann
Carol
Doug
Fred
If the "x" was removed from their row, their name will not appear on the
list. Thank you so much!!
Max
Then in your other sheet,
In A2: =IF(z!G2="X",ROW(),"")
In B2: =IF(ROWS($1:1)>COUNT(A:A),"",INDEX(z!A:A,SMALL(A:A,ROWS($1:1))))
Copy A2:B2 down to cover the max expected extent of source data, say to B20?
Minimize/hide col A. Col B will return the desired results, all neatly packed
at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
T. Valko
Assume your date is on Sheet1 in the range A2:B8.
Let's create some defined named ranges...
Goto the menu Insert>Name>Define
Name: Names
Refers to: =Sheet1!$A$2:$A$8
Name: Status
Refers to: =Sheet1!$B$2:$B$8
OK out
On Sheet2 in cell A1 enter this formula. This will return the count of
records:
=COUNTIF(Status,"x")
Enter this array formula** in A2 to extract the names from Sheet1:
=IF(ROWS(A$2:A2)>A$1,"",INDEX(Names,SMALL(IF(Status="x",ROW(Names)),ROWS(A$2:A2))-MIN(ROW(Names))+1))
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
Copy the formula in A2 down to enough cells that will ensure all the names
are returned. How many cells that is only you can know! If it's possible
that every name can have a "x" then you have to copy the formula to a number
of cells that is at least equal to the total number of names in your list.
--
Biff
Microsoft Excel MVP
"Daly" <Da...@discussions.microsoft.com> wrote in message
news:419D1E3F-D084-4363...@microsoft.com...
Teethless mama
=IFERROR(INDEX(rngA,SMALL(IF(rngB="X",ROW(INDIRECT("1:"&ROWS(rngA)))),ROWS($1:1))),"")
ctrl+shift+enter, not just enter
copy down
All versions:
=IF(COUNTIF(rngB,"X")>=ROWS($1:2),INDEX(rngA,SMALL(IF(rngB="X",ROW(INDIRECT("1:"&ROWS(rngA)))),ROWS($1:2))),"")
ctrl+shift+enter, not just enter
copy down
Teethless mama
Should be: ROWS($1:1) instead of ROWS($1:2)
Ron Rosenfeld
wrote:
If your data is on Sheet2, then
=IF(COUNTIF(Sheet2!$B$1:$B$100,"X")<ROWS($1:1),"",
INDEX(Sheet2!$A$1:$A$100,LARGE(ROW($B$1:$B$100)*
(Sheet2!$B$1:$B$100="X"),ROWS($1:1))))
This formula must be **array-entered**
Make sure all your ranges are the same size.
Fill down as far as required.
----------------------------------------
To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.
--ron
Ron Rosenfeld
wrote:
>Is there a function for: If a column contains certain text, return the value
By the way, my previous solution returns the names in the reverse order of
their entry into your original table. To have them come out in the same order,
try this formula, also **array-entered**:
INDEX(Sheet2!$A$1:$A$100,LARGE(ROW($B$1:$B$100)*
(Sheet2!$B$1:$B$100="X"),COUNTIF(
Sheet2!$B$1:$B$100,"X")+1-ROWS($1:1))))
--ron
Shane Devenshire
Here is a way to do this without an array formula:
=IF(ROW(A1)<=COUNTA(Sheet1!B$1:B$9),INDEX(Sheet1!A$1:A$9,SUMPRODUCT(LARGE((Sheet1!B$1:B$9="X")*ROW(B$1:B$9),ROW(A1)))),"")
This will put them in reverse order but that may not be critical. You just
copy this formula down a number of rows equal to all of your data or further
if you wish. This assumes the first entry is on row 1.
A shorter but slower version of the formula would be:
=IF(ROW(A1)<=COUNTA(Sheet1!B:B),INDEX(Sheet1!A:A,SUMPRODUCT(LARGE((Sheet1!B:B="X")*ROW(B:B),ROW(A1)))),"")
This second formula will only work in 2007 because it references the entire
columns.
--
If this helps, please click the Yes button.
Cheers,
Shane Devenshire