I have a list of 100 customers which are ranked from 1 to 100.
The list is subject to change every 2 months. Instead of manually updating the customer visits, I try to build a formula to automatically show "1" to indicate a customer visit in a specific month based on the its ranking. Two scenarios might cause error:
- Those are from 1 to 50 should have 2 customer visits per year, the second visit is 6 months after first visit. Customer ranked 1 has visits in Jan and Jun, customer ranked 2 has visits in Feb and Jul, customer ranked 3 has visits in Mar and Aug, customer ranked 8 has visits in Jan and Jun. The same applied to the others in top 50 as shown in below screenshot
- Those are from 51 to 100 should have 1 customer visit per year. Customer whose rank 51 has a visit in Jan, customer whose rank 52 has a visit in Feb, customer whose rank 53 has a visit in Mar so on
The list is subject to change every 2 months. Instead of manually updating the customer visits, I try to build a formula to automatically show "1" to indicate a customer visit in a specific month based on the its ranking. Two scenarios might cause error:
- Scenario 1: a customer falls out of top 50 (reduce from 2 visits to 1 visit). Take customer ranked 21 (visits in Jul and Dec) as an example, if this one decide to decrease their buying significantly and next time updating the list, it will be ranked 56, then the sheet should still only show the visit in Jul which was something done in the past, instead of having the visit in Jun
- Scenario 2: a customer goes into top 50 (have 1 more visit scheduled in). This scenario is easier, let's say customer ranked 70 becomes the 10th biggest customer then in addition to the visit planned for Aug 2022, there should be 1 more planned for Jan 2023.
