Identify group of cells with same reference and 1 cell in group = specific text

[Abbyneedshelp]

Hello,

I'm not fab at excel, but know there is a formula for pretty much anything! So I'm hoping someone can help me.

I have a spread sheet with a list of references in rows, 1 reference can be duplicated over a number of rows with a verity of status':

Column A (Reference)_____________Column B (Status)
REF1___________________________Quote Given
REF1___________________________No Quote
REF1___________________________Live
REF2___________________________No Quote
REF2___________________________Referral Required
REF3___________________________Referral Required
REF3___________________________No Quote
REF4___________________________Referral Required
REF4___________________________No Quote
REF4___________________________Quote Given
REF5___________________________Quote Given
REF5___________________________No Quote
REF5___________________________Referral Required
REF5___________________________Live
REF6___________________________Referral Required
REF6___________________________No Quote
REF7___________________________Quote Given
REF7___________________________No Quote


What I would like to do is filter out all references where one of their status' in column B equals Live.

So maybe a formula in Column C which will say if in the group 1 stats 'Live' E.g.:

Column A (Reference)_____________Column B (Status)______________Column C
REF1___________________________Quote Given___________________Live
REF1___________________________No Quote _____________________Live
REF1___________________________Live__________________________Live
REF2___________________________No Quote
REF2___________________________Referral Required
REF3___________________________Referral Required
REF3___________________________No Quote
REF4___________________________Referral Required
REF4___________________________No Quote
REF4___________________________Quote Given
REF5___________________________Quote Given ____________________Live
REF5___________________________No Quote_______________________Live
REF5___________________________Referral Required_________________Live
REF5___________________________Live ___________________________Live
REF6___________________________Referral Required
REF6___________________________No Quote
REF7___________________________Quote Given
REF7___________________________No Quote

Then I can filter Column C to Live and delete all rows.

There may be better ways and I am open to suggestions, but as simple as possible is best for me.

Many Thanks


Abby

 

[Fishboy]

Hello,

I'm not fab at excel, but know there is a formula for pretty much anything! So I'm hoping someone can help me.

I have a spread sheet with a list of references in rows, 1 reference can be duplicated over a number of rows with a verity of status':

Column A (Reference)_____________Column B (Status)
REF1___________________________Quote Given
REF1___________________________No Quote
REF1___________________________Live
REF2___________________________No Quote
REF2___________________________Referral Required
REF3___________________________Referral Required
REF3___________________________No Quote
REF4___________________________Referral Required
REF4___________________________No Quote
REF4___________________________Quote Given
REF5___________________________Quote Given
REF5___________________________No Quote
REF5___________________________Referral Required
REF5___________________________Live
REF6___________________________Referral Required
REF6___________________________No Quote
REF7___________________________Quote Given
REF7___________________________No Quote


What I would like to do is filter out all references where one of their status' in column B equals Live.

So maybe a formula in Column C which will say if in the group 1 stats 'Live' E.g.:

Column A (Reference)_____________Column B (Status)______________Column C
REF1___________________________Quote Given___________________Live
REF1___________________________No Quote _____________________Live
REF1___________________________Live__________________________Live
REF2___________________________No Quote
REF2___________________________Referral Required
REF3___________________________Referral Required
REF3___________________________No Quote
REF4___________________________Referral Required
REF4___________________________No Quote
REF4___________________________Quote Given
REF5___________________________Quote Given ____________________Live
REF5___________________________No Quote_______________________Live
REF5___________________________Referral Required_________________Live
REF5___________________________Live ___________________________Live
REF6___________________________Referral Required
REF6___________________________No Quote
REF7___________________________Quote Given
REF7___________________________No Quote

Then I can filter Column C to Live and delete all rows.

There may be better ways and I am open to suggestions, but as simple as possible is best for me.

Many Thanks


Abby

Hi Abby, welcome to the boards.

Try out the following formula in C1 and drag-fill down. This is based on your example data and assumes the data ends in row 18. You can amend the 18 to however far down the sheet your data goes.

=IF(COUNTIFS($A$1:$A$18,A1,$B$1:$B$18,"Live")>0,"Live","")

 

[Abbyneedshelp]

Thank you so much! This is perfect

 

[Fishboy]

Thank you so much! This is perfect

Happy to help