CODEWITHSUNDEEP
excelexcel-formula
Prospidnick
Prospidnick

Reputation: 133

CountIf Index Match mark unique

enter image description here

Above is an example in Column F i have a data set that potentialy contains duplicate values (highlighted green), on the corresponding row in column G it notes whether that value is a "Col" or a "Del" In column H is where i want the result, the result being;

If there is more than one instance in column F and the corresponding value in Column G contains 'Col' and a 'Del' then mark the first record with a 'Y' else leave blank.

I'm trying to find how may times we made both a delivery and collection from the same site and not just a delivery or a collection.

Many thanks

Upvotes: 0

Views: 177

Answers (2)

OverflowStacker
OverflowStacker

Reputation: 1338

If i understand you question correctly, you can use this

=IF(AND(COUNTIF($A$2:$A$11,$A2)>1,COUNTIF($A$1:$A1,$A2)<1),"Y","")

enter image description here

just if there are multiple occurances of an item or:

=IF(AND(COUNTIF($A$2:$A$11,$A2)>1,COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,"col")>0,COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,"del")>0,COUNTIF($A$1:$A1,$A2)<1),"Y","")

enter image description here

for the first occurence of a site with at least one delivery and collection.

UPDATE In the second formula the first COUNTIF is not necessary as this condition is also checked by the next two COUNTIFS.

=IF(AND(COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,"col")>0,COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,"del")>0,COUNTIF($A$1:$A1,$A2)<1),"Y","")

The first COUNTIFS counts the number of collections at the current site, the second COUNTIFS is for delivery. The last COUNTIF expands, when you pull it down. It gives a FALSE, if the current site was already above in the list.

Upvotes: 1

basic
basic

Reputation: 11968

I can offer the following option::

=IF(SUMPRODUCT(--(SUM(--(A2=$A$2:$A$16)*--("C"=$B$2:$B$16))>=1)
+--(SUM((--(A2=$A$2:$A$16)*--("D"=$B$2:$B$16)))>=1))
*--(COUNTIF($A$2:A2,A2)=1)>1,"Y","")

enter image description here

Upvotes: 0

Related Questions