Excel Functions–Data Comparison and Manipulation

Ok so you might ask what does this posting have to do with FIM, MIM, or Identity Management … Well it doesn't not Directly. I was involved in a scenario where I am Synchronizing Data across many Data Sources that Some are Read only to the Synchronization Service and when dealing with legacy data we all know that in the past People did not always follow the rules or naming standards and accounts were created any which way the “admin” liked, You might say it was the Wild West of Identity Management. One of the reasons a lot of Customers are now using tools Like Microsoft Identity Manager (MIM) is because of this past behavior. While working with the customer to analyze the current environment they dumped out a .csv file of one of their Data Bases so we could look at it to determine how we can use this data, how we can join this data up with existing data and how we can take this data and provision it to other networks, if we have enough information without grabbing attributes from other sources etc.

So lets look at Sample Scenario I created to represent the issue.

 

In the image above the AccountName is supposed to represent the BaseID and an Type of account if Defined. Standard accounts do not get a Type Listed and accounts Such as Admin, Service Etc.

In the customers scenario Some accounts were created correctly but others were not and they wanted a quick function to show what all BaseID should have been if created correctly.

1st I created a new Column BaseID2

 

2nd I clicked on the the 1cell under BaseID2 in this example F2

 

Next I typed the following Function in the Cell Block

=IF(ISERROR(FIND(CONCATENATE("-",C2),A2))=TRUE,B2,SUBSTITUTE(B2,CONCATENATE("-",C2),""))

 

Now when you click on Enter the Correct BaseID if needed to be corrected will be displayed, or if the BaseID was already correct it would be coppied over to BaseID2

 

Finally to update this for all accounts in the new Column, in this example F (BaseID2) Copy the function and paste using the updated function for each row