Hi

I am trying to create a list of products in one column that are rated either
good or excellent in another column on the same sheet. So I need the list to
display in one column based upon the values of two other separate columns.

For example, I have 3 columns in total, column A is where I want the product
names to be displayed, column B is the text I want to display in column A
only if the corresponding cell in column C contains the words 'excellent' or
'good'. So if cell B1 contains the text 'widget 1' and the text in cell C1
contains the text 'excellent' or 'good' then 'widget 1' should be shown in
cell A1. Ideally i want the list to only contain the qualifying products
without empty cells caused by products that don't meet the criteria.

Is there a formula to create this? Any ideas would be much appreciated as
I've spent hours trying to find the solution.

Thanks
Simon

Hi

This should do it, and is not case sensitive

=IF(OR(C1="excellent",C1="good"),B1,"")

Regards
Nelly

"pieman3" wrote:

Hi

I am trying to create a list of products in one column that are rated either
good or excellent in another column on the same sheet. So I need the list to
display in one column based upon the values of two other separate columns.

For example, I have 3 columns in total, column A is where I want the product
names to be displayed, column B is the text I want to display in column A
only if the corresponding cell in column C contains the words 'excellent' or
'good'. So if cell B1 contains the text 'widget 1' and the text in cell C1
contains the text 'excellent' or 'good' then 'widget 1' should be shown in
cell A1. Ideally i want the list to only contain the qualifying products
without empty cells caused by products that don't meet the criteria.

Is there a formula to create this? Any ideas would be much appreciated as
I've spent hours trying to find the solution.

Thanks
Simon

Try this...

Let's assume your data is in the range B1:C20

Create these defined names
Goto the menu InsertName Define

Name: Item
Refers to: $B$1:$B$20

Name: Status
Refers to: $C$1:$C$20

Name: Rating
Refers to: ={"good","excellent"}

Enter this array formula** in A1:

=IF(ROWS(A$1:A1)<=SUM(COUNTIF(Status,Rating)),INDE X(Item,SMALL(IF(Status=Rating,ROW(Item)),ROWS(A$1: A1))-MIN(ROW(Item))+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 down until you get blanks.

--
Biff
Microsoft Excel MVP


"pieman3" wrote in message
...
Hi

I am trying to create a list of products in one column that are rated
either
good or excellent in another column on the same sheet. So I need the list
to
display in one column based upon the values of two other separate columns.

For example, I have 3 columns in total, column A is where I want the
product
names to be displayed, column B is the text I want to display in column A
only if the corresponding cell in column C contains the words 'excellent'
or
'good'. So if cell B1 contains the text 'widget 1' and the text in cell C1
contains the text 'excellent' or 'good' then 'widget 1' should be shown in
cell A1. Ideally i want the list to only contain the qualifying products
without empty cells caused by products that don't meet the criteria.

Is there a formula to create this? Any ideas would be much appreciated as
I've spent hours trying to find the solution.

Thanks
Simon

Thanks Nelly, I tried this and it did copy the text like you said. The only
problem was that it copied blanks from cells that didn't meet the rating
criteria. I can use this elsewhere in my sheet though so many thanks for your
advice :-)

"nelly" wrote:

Hi

This should do it, and is not case sensitive

=IF(OR(C1="excellent",C1="good"),B1,"")

Regards
Nelly

"pieman3" wrote:

Hi

I am trying to create a list of products in one column that are rated either
good or excellent in another column on the same sheet. So I need the list to
display in one column based upon the values of two other separate columns.

For example, I have 3 columns in total, column A is where I want the product
names to be displayed, column B is the text I want to display in column A
only if the corresponding cell in column C contains the words 'excellent' or
'good'. So if cell B1 contains the text 'widget 1' and the text in cell C1
contains the text 'excellent' or 'good' then 'widget 1' should be shown in
cell A1. Ideally i want the list to only contain the qualifying products
without empty cells caused by products that don't meet the criteria.

Is there a formula to create this? Any ideas would be much appreciated as
I've spent hours trying to find the solution.

Thanks
Simon

Biff, thanks so much for your excellent post. Your forumla works great and
I've learned something new as well, didn't know about 'names'.

Thanks again for your help, much appreciated :-)

"T. Valko" wrote:

Try this...

Let's assume your data is in the range B1:C20

Create these defined names
Goto the menu InsertName Define

Name: Item
Refers to: $B$1:$B$20

Name: Status
Refers to: $C$1:$C$20

Name: Rating
Refers to: ={"good","excellent"}

Enter this array formula** in A1:

=IF(ROWS(A$1:A1)<=SUM(COUNTIF(Status,Rating)),INDE X(Item,SMALL(IF(Status=Rating,ROW(Item)),ROWS(A$1: A1))-MIN(ROW(Item))+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 down until you get blanks.

--
Biff
Microsoft Excel MVP


"pieman3" wrote in message
...
Hi

I am trying to create a list of products in one column that are rated
either
good or excellent in another column on the same sheet. So I need the list
to
display in one column based upon the values of two other separate columns.

For example, I have 3 columns in total, column A is where I want the
product
names to be displayed, column B is the text I want to display in column A
only if the corresponding cell in column C contains the words 'excellent'
or
'good'. So if cell B1 contains the text 'widget 1' and the text in cell C1
contains the text 'excellent' or 'good' then 'widget 1' should be shown in
cell A1. Ideally i want the list to only contain the qualifying products
without empty cells caused by products that don't meet the criteria.

Is there a formula to create this? Any ideas would be much appreciated as
I've spent hours trying to find the solution.

Thanks
Simon

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"pieman3" wrote in message
...
Biff, thanks so much for your excellent post. Your forumla works great and
I've learned something new as well, didn't know about 'names'.

Thanks again for your help, much appreciated :-)

"T. Valko" wrote:

Try this...

Let's assume your data is in the range B1:C20

Create these defined names
Goto the menu InsertName Define

Name: Item
Refers to: $B$1:$B$20

Name: Status
Refers to: $C$1:$C$20

Name: Rating
Refers to: ={"good","excellent"}

Enter this array formula** in A1:

=IF(ROWS(A$1:A1)<=SUM(COUNTIF(Status,Rating)),INDE X(Item,SMALL(IF(Status=Rating,ROW(Item)),ROWS(A$1: A1))-MIN(ROW(Item))+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 down until you get blanks.

--
Biff
Microsoft Excel MVP


"pieman3" wrote in message
...
Hi

I am trying to create a list of products in one column that are rated
either
good or excellent in another column on the same sheet. So I need the
list
to
display in one column based upon the values of two other separate
columns.

For example, I have 3 columns in total, column A is where I want the
product
names to be displayed, column B is the text I want to display in column
A
only if the corresponding cell in column C contains the words
'excellent'
or
'good'. So if cell B1 contains the text 'widget 1' and the text in cell
C1
contains the text 'excellent' or 'good' then 'widget 1' should be shown
in
cell A1. Ideally i want the list to only contain the qualifying
products
without empty cells caused by products that don't meet the criteria.

Is there a formula to create this? Any ideas would be much appreciated
as
I've spent hours trying to find the solution.

Thanks
Simon