I have an excel data with a column containing customer IDs (A), another containing account numbers (B), another containing the ratings of each account number (C) and another blank column containing contagion rating of the accounts (D) (this is the column to be filled with a function). Note that a customer ID can have several account numbers. Also note that the rating is from 1 to 5 with 1 being the best and 5 being the worst. Also note that the rating is by account number and not by customer ID. Now i want to apply a contagion rule such that excel looks through the ratings of accounts belonging to the same customer ID and return in the column D for all the accounts belonging to the same customer ID the worst rating amongs the account numbers. For example if customer ID 0012 is linked to five different accounts with ratings of say 3, 4, 2, 5, and 4, under column D excel should out 5 which is the worst rating for the respective rows of the account numbers. There will be another customer with say ID 0014 linked to five 3 account numbers with ratings 2, 1 and 3. excel should recognized that this is a different customer ID and apply 3 in column D for the account numbers for this customer ID and so on and so forth.
Also note that i want to apply this function and copy through the rest of the rows noting that i you move down the rows the customer ID changes with a set of different account numbers with their respective different ratings.
Also note that i want to apply this function and copy through the rest of the rows noting that i you move down the rows the customer ID changes with a set of different account numbers with their respective different ratings.