Duplicate Stipulations and Naming

[WarpathMechanic]

After hours of searching the excel bible that is Chandoo.org and racking my brain trying to figure this one out. My boss has finally cut me a curveball I just cant get through on my own. I turn to the senseis of rows and columns for guidance.


Here is what I have:


Location ID Location Name Policy Number Customer Benefit 2010 Benefit 2011


Here are the stipulations I have to over come:


If 2 Customers names are the same but the Policy Numbers are different it only counts as 1


If 2 Customers names are the same but the Location IDs are different they count individually (2)


If it has a benefit in 2010 but not in 2011 = Existing Term


If it has a benefit in 2011 but not in 2010 = New Active


If it has no benefit for either 2010 or 2011 = New Term


If it has a benefit for both 2010 and 2011 = Existing Active


If there is more than one row for each name (as stated by the criteria above) each row must have one of the 4 benefit labels mentioned.


I have tried:


SUMPRODUCT

SUMIFS

IF

INDEX

MATCH

COUNTIF


Now I am the first to admit that I am NO MASTER when it comes to these formulas but for the life of me I either get too many variables or I cannot get the result needed (in short, a proper count based on the criteria above of the total number of policies and their status based on the criteria above)


If needed I will post the sheet but I am hesitant as it has some data that may need not be public.

 

[Luke M]

It's not explictly stated, but are the first two statements mutually exclusive?

If 2 Customers names are the same but the Policy Numbers are different it only counts as 1


If 2 Customers names are the same but the Location IDs are different they count individually (2)

By that I mean, can you have 2 customer names that are the same, policy numbers are different, and location ID's are also different? In which case, does it count as 1 or 2? Also, and this is critical, is the data sorted?


For the latter 4 conditions, what type of value in those columns? Numbers? Y/N?, True/False? I think the formula would be something like:

=IF(F2="Yes",IF(G2="Yes","Existing Active","Existing Term"),IF(G2="Yes","New Active","New Term"))

Sounds like we may need to add some other conditions in once the first part is understood, but we'll get to that later.


A posted workbook with a sample of dummy data would be helpful, just to get a better idea what the data types are and the layout.

 

[NARAYANK991]

Hi ,


I just created a sample worksheet with the following data :


Location Location Policy Customer Benefit Benefit First Occurrence

---ID--- --Name-- Number ---Name--- ----2010--- ---2011--

---J City J 1105 Customer C 1562 1667 1

---B City B 1110 Customer G 1529 1

---C City C 1109 Customer B 1579 1856 1

---H City H 1110 Customer E 1712 1

---D City D 1103 Customer D 1764 1955 1

---I City I 1110 Customer D 1687 1

---E City E 1108 Customer D 1869 1

---D City D 1104 Customer D 1829 0

---D City D 1108 Customer C 1841 1737 1

---D City D 1106 Customer D 1784 0

---D City D 1109 Customer H 1

---H City H 1108 Customer C 1938 1

---B City B 1102 Customer C 1619 1763 1

---F City F 1103 Customer B 1

---A City A 1102 Customer I 1528 1813 1

---B City B 1108 Customer E 1

---H City H 1105 Customer I 1876 1

---C City C 1110 Customer A 1897 1522 1

---J City J 1104 Customer A 1632 1

---F City F 1106 Customer B 0


The column named "First Occurrence" has the following formula ( the headings are in row 5 , while the data starts from row 6 ) :


=N(COUNTIFS($D$6:D6,D6,$A$6:A6,A6)=1)


This gives a 1 where the occurrence of the Customer Name and Location ID is the first occurrence in a set of duplicates , and 0 for every subsequent occurrence.


So wherever the "First Occurrence" is 0 , no benefit category will be applicable. For all others , the benefit category rules will apply.


Is this what you are looking for ?


Narayan

 

[Luke M]

Yes, that helps. Will this formula work?

=IF(H6=1,IF(F6>0,IF(G6>0,"Existing Active","Existing Term"),IF(G6>0,"New Active","New Term")),"")