Excel Functions - Data Comparison and Manipulation

Published 11-01-2019 03:02 PM 676 Views
Microsoft

First published on MSDN on Jun 09, 2017
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

1 Comment
Occasional Visitor

Hello

Please what formula should i use to compare two cells that have more than one name. I want to compare names in two columns but the cells have both the first name, middle name and last. Is there any formula i can use to make this easy. Also some cells have the same names but some were mispelt, hence it returning a mismatch result

%3CLINGO-SUB%20id%3D%22lingo-sub-974650%22%20slang%3D%22en-US%22%3EExcel%20Functions%20-%20Data%20Comparison%20and%20Manipulation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-974650%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Jun%2009%2C%202017%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3EOk%20so%20you%20might%20ask%20what%20does%20this%20posting%20have%20to%20do%20with%20FIM%2C%20MIM%2C%20or%20Identity%20Management%20%E2%80%A6%20Well%20it%20doesn't%20not%20Directly.%20I%20was%20involved%20in%20a%20scenario%20where%20I%20am%20Synchronizing%20Data%20across%20many%20Data%20Sources%20that%20Some%20are%20Read%20only%20to%20the%20Synchronization%20Service%20and%20when%20dealing%20with%20legacy%20data%20we%20all%20know%20that%20in%20the%20past%20People%20did%20not%20always%20follow%20the%20rules%20or%20naming%20standards%20and%20accounts%20were%20created%20any%20which%20way%20the%20%E2%80%9Cadmin%E2%80%9D%20liked%2C%20You%20might%20say%20it%20was%20the%20Wild%20West%20of%20Identity%20Management.%20One%20of%20the%20reasons%20a%20lot%20of%20Customers%20are%20now%20using%20tools%20Like%20Microsoft%20Identity%20Manager%20(MIM)%20is%20because%20of%20this%20past%20behavior.%20While%20working%20with%20the%20customer%20to%20analyze%20the%20current%20environment%20they%20dumped%20out%20a%20.csv%20file%20of%20one%20of%20their%20Data%20Bases%20so%20we%20could%20look%20at%20it%20to%20determine%20how%20we%20can%20use%20this%20data%2C%20how%20we%20can%20join%20this%20data%20up%20with%20existing%20data%20and%20how%20we%20can%20take%20this%20data%20and%20provision%20it%20to%20other%20networks%2C%20if%20we%20have%20enough%20information%20without%20grabbing%20attributes%20from%20other%20sources%20etc.%20%3CBR%20%2F%3ESo%20lets%20look%20at%20Sample%20Scenario%20I%20created%20to%20represent%20the%20issue.%20%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20574px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F153892i845A04D7F051BFF7%2Fimage-dimensions%2F574x130%3Fv%3D1.0%22%20width%3D%22574%22%20height%3D%22130%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20the%20image%20above%20the%20AccountName%20is%20supposed%20to%20represent%20the%20BaseID%20and%20an%20Type%20of%20account%20if%20Defined.%20Standard%20accounts%20do%20not%20get%20a%20Type%20Listed%20and%20accounts%20Such%20as%20Admin%2C%20Service%20Etc.%20%3CBR%20%2F%3EIn%20the%20customers%20scenario%20Some%20accounts%20were%20created%20correctly%20but%20others%20were%20not%20and%20they%20wanted%20a%20quick%20function%20to%20show%20what%20all%20BaseID%20should%20have%20been%20if%20created%20correctly.%20%3CBR%20%2F%3E1st%20I%20created%20a%20new%20Column%20BaseID2%20%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20590px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F153893i4633437C472AA3AB%2Fimage-dimensions%2F590x116%3Fv%3D1.0%22%20width%3D%22590%22%20height%3D%22116%22%20%2F%3E%3C%2FSPAN%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E2nd%20I%20clicked%20on%20the%20the%201cell%20under%20BaseID2%20in%20this%20example%20F2%20%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20593px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F153894iFD48FD4AB1F83DFD%2Fimage-dimensions%2F593x171%3Fv%3D1.0%22%20width%3D%22593%22%20height%3D%22171%22%20%2F%3E%3C%2FSPAN%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20I%20typed%20the%20following%20Function%20in%20the%20Cell%20Block%20%3CBR%20%2F%3E%3DIF(ISERROR(FIND(CONCATENATE(%22-%22%2CC2)%2CA2))%3DTRUE%2CB2%2CSUBSTITUTE(B2%2CCONCATENATE(%22-%22%2CC2)%2C%22%22))%20%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20604px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F153895i8BBCBFA4A6CEBC91%2Fimage-dimensions%2F604x84%3Fv%3D1.0%22%20width%3D%22604%22%20height%3D%2284%22%20%2F%3E%3C%2FSPAN%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3ENow%20when%20you%20click%20on%20Enter%20the%20Correct%20BaseID%20if%20needed%20to%20be%20corrected%20will%20be%20displayed%2C%20or%20if%20the%20BaseID%20was%20already%20correct%20it%20would%20be%20coppied%20over%20to%20BaseID2%20%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20605px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F153896iDA7B763D5291BCE5%2Fimage-dimensions%2F605x124%3Fv%3D1.0%22%20width%3D%22605%22%20height%3D%22124%22%20%2F%3E%3C%2FSPAN%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3EFinally%20to%20update%20this%20for%20all%20accounts%20in%20the%20new%20Column%2C%20in%20this%20example%20F%20(BaseID2)%20Copy%20the%20function%20and%20paste%20using%20the%20updated%20function%20for%20each%20row%20%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20615px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F153897i98505F98C78EEB77%2Fimage-dimensions%2F615x116%3Fv%3D1.0%22%20width%3D%22615%22%20height%3D%22116%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-974650%22%20slang%3D%22en-US%22%3E%3CP%3EFirst%20published%20on%20MSDN%20on%20Jun%2009%2C%202017%20Ok%20so%20you%20might%20ask%20what%20does%20this%20posting%20have%20to%20do%20with%20FIM%2C%20MIM%2C%20or%20Identity%20Management%20%E2%80%A6%20Well%20it%20doesn't%20not%20Directly.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-974650%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAnthonyMarsiglia%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1712382%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Functions%20-%20Data%20Comparison%20and%20Manipulation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1712382%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22lia-message-subject-wrapper%20lia-component-subject%20lia-component-message-view-widget-subject-with-options%22%3E%3CDIV%20class%3D%22MessageSubject%22%3E%3CDIV%20class%3D%22MessageSubjectIcons%20%22%3E%3CH1%20id%3D%22toc-hId-2048238331%22%20id%3D%22toc-hId-2048088444%22%3EHello%3C%2FH1%3E%3CH1%20id%3D%22toc-hId--503918630%22%20id%3D%22toc-hId--504068517%22%3EPlease%20what%20formula%20should%20i%20use%20to%20compare%20two%20cells%20that%20have%20more%20than%20one%20name.%20I%20want%20to%20compare%20names%20in%20two%20columns%20but%20the%20cells%20have%20both%20the%20first%20name%2C%20middle%20name%20and%20last.%20Is%20there%20any%20formula%20i%20can%20use%20to%20make%20this%20easy.%20Also%20some%20cells%20have%20the%20same%20names%20but%20some%20were%20mispelt%2C%20hence%20it%20returning%20a%20mismatch%20result%3C%2FH1%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
Version history
Last update:
‎Feb 20 2020 01:15 PM
Updated by: