Search code examples
excelexcel-formulanestedmax

Max formula returning 0 based on values from another formula

I am trying to make the following work:

A               B   C   D       
BK-8811436091   57  1   Unique      
BK-8811436091   57  1   Duplicate       
BK-8811436091   57  1   Duplicate       
BK-8811436091   58  1   Unique      
BK-8811436091   57  1   Duplicate       
BK-8811436091   59  1   Unique      
BK-8811436091   57  1   Duplicate       
BK-8811436091   58  1   Duplicate       
BK-8811436092   54  1   Unique      
BK-8811436092   56  1   Unique      
BK-8811436092   58  1   Unique      
BK-8811436092   57  1   Unique      
BK-8811436091   57  1   Duplicate       
BK-8811436091   58  1   Duplicate       
BK-8811436092   57  1   Duplicate

  • If column A+B have a unique combination it should give 1 in column C

  • If column A+B have a duplicate it should receive the same number as it did before (e.g. row 2 should be 1 in column C)

  • if column A is equal but column B is different, the number in column C should get + 1 (e.g. row 4 should be 2 in column C and consequently a 3 in row 6)

  • if there is an entirely unique column A+B this should get a 1 again (e.g. row 9 should get a 1).

I have been attempting this with the following formula in column C (which gives wrong answers right now):

=IF(AND(D2="unique";A2&B2<>A1&B1);B2-B2+1;IF(AND(D2="Duplicate";A2&B2=A1&B1);MAX(IF($A$2:A2=A2;$C$2:C2));IF(AND(A2&B2<>A1&B1;D2="unique");MAX(IF($A$2:A2=A2;$C$2:C2)+1);MAX(IF($A$2:A2=A2;$C$2:C2)))))

I don't think it's entirely correct yet but I feel I should be able to manage it with this train of thought. One of the problems that are occuring is that the max formula is returning 0 values even though there are quite obviously 1's in column C --> max returning 1 + 1 should lead to two.

Does anyone know what I am doing wrong here? I am no programmer / coder so please correct me if my formatting of this question is wrong.

Kind regards,

VHes

Solution

  • Result

    This is my approach to find duplicates & unique values., based on your excel versions you can choose the formulas & If you have large set of data, CountIf is not the best option. Instead, use Power Query.