Excel Excluding Certain Values from Duplicates

user_15196698 0
Viewed 46

My data looks like that:

Data Picture

As you can see there are duplicated values in column B that have different values in column C. I am trying to find a solution to remove some of them on certain conditions. In column A there are different names of companies but in column B there are only 3 options (pending,ready,no). I would like to be able to have in column C a value: ready(if second duplicate has no or pending), pending(if second duplicate has value no).

I tried to do it with:

=IFERROR(INDEX($B$2:$B$9;AGGREGATE(15;6;ROW($B$2:$B$9)-ROW($B$2)+1/($C$2:$C$9="no");ROWS($F$2:F2)));"")

But it does not solve my issue in the end.

excel-formula excel
1 Answers

I would add a third column that calculates the new value you want using specific logic. From your post, I am unsure of what this logic is.

  1. if there is only one occurrence of the firm name, then the value is the same,
  2. if multiple occurrences, what happens?? if there are two occurences- one ready and one pending, what value do you want? if one is no and one is yes, what value do you want?

once you have a set of logic in mind, you can use if, countif, and countifs functions to solve it.

To get a count of the firm occurences:

=COUNTIF($A$2:$A$7,A2)

To get a count of no,pending, and yes values:

=COUNTIFS($B$2:$B$7,"no",$A$2:$A$7,$A2) =COUNTIFS($B$2:$B$7,"pending",$A$2:$A$7,$A2) =COUNTIFS($B$2:$B$7,"yes",$A$2:$A$7,$A2)

you could then use a few nested ifs to perform your logic:

=if(COUNTIF($A$2:$A$7,A2)=1,B2,if(COUNTIFS($B$2:$B$7,"no",$A$2:$A$7,$A2)=1,"no",etc))

user_195833130
Related