Script / Macro Request - Match row A to Row B - Or other solution?

[burgemaster]

Hi all,

 

We have all 900 students in 4 houses.... each house has a security group and each students has been added to the group where needed.

BUT.. They have now decided that we now only need 3 houses and all students have been rehoused into 3 on the MIS system.

 

We now need to somehow link an exported list from our MIS system (with the new list of Names and Houses) to an exported list from AD.

 

Unfortunely the names on the MIS system do not always match the names in AD.. e.g. John.Bloggs vs Jonothan.Bloggs.

 

Does anyone please know how I can match up two lists in Excel to the best fit.

Maybe with a % score of how well they matched? so we could then manually check the lower % matches?

 

Otherwise we are going to have to go through 800 students adding them to security groups !

 

Thanks in advance

 

eg.

AD MIS HOUSE

08Jon.bloggs | Jonathon Bloggs | Sebright

Edited July 20, 2011 by burgemaster

[ChrisMiles]

I don't think youll be able to compare them in the way you think. The way I would do it is to split the usernames into 3 columns of year first name and last name in excel and then compare the last name and perhaps the first 3 characters of the first name, this cshould cover most of them, then do the ones that are left manually.

 

This is the main reason why I insist on the usernames using the name from sims, however, to avoid any issues like this, I write each students admission number and UPN to the employeeID and employeeNumber AD user properties when they are created so I can compare them that way without even bothering with names.

[Steve21]

Could you post a few examples, just trying something and want to see if it works (Like your JBlogg one)

 

Thanks,

Steve

[Steve21]

An example using a Levenshtein function:

 

 

As you can see the closer the name is in terms of "changingletters" the lower score it gets. Even if you put "alternative" names in. However you'll note the last one "Athon" gets the best score, even though it's not the same name (well could be), if you want to solve that it's easy enough just to filter by those who start with J, aka athon could auto get +10 or some random score.

 

Steve

[burgemaster]

Thanks for your time!!

 

Please see attached example with fake students....

 

If I can match them all up I can then use UMRA to automatically do all the AD shizzle.

 

Sample_Students.xls

[Steve21]

Obviously need to make it pretty etc, but to show the basics: (Colouring I did, and I'm assuming you'd like it formatted someway different)

 

 

Just to show it works if you use Levenshtein Distance to sort it.

 

But as I said, how would you wnat it formatted in the end? email next to user? or does it matter? etc etc

 

Steve

[burgemaster]

Wow that certainly looks like what we need

 

The end product we would want the correct MIS name on the same line as the AD username.

The aim would for me to have the correct house next to the correct AD name as then UMRA can import it and make the changes.

 

Thanks in advance!!

[Steve21]

Try this and see if it's working ok for you, or if something needs to change:

 

test111.zip

 

(Note it's macro enabled, so it will need authorising etc etc)

 

You'll need to change the numbers in the macro to however many students there are to loop through. (If you try how it's setup now, should work)

 

Any problems shout,

Steve

[burgemaster]

Steve thats awesome !

100% there! Could I please ask where or on what page i would replace/paste in the complete long list of 800 students?

Also would it fail if there were names in the AD list that werent in the MIS list? (maybe they have left and us not deleted the account)

 

So gratefull!

[Steve21]

Steve thats awesome !

100% there! Could I please ask where or on what page i would replace/paste in the complete long list of 800 students?

Also would it fail if there were names in the AD list that werent in the MIS list? (maybe they have left and us not deleted the account)

 

So gratefull!

 

It's all on that first page, the rest was just me testing stuff

 

All you'd need to do, is paste the 800list into the columns, aka "sheet2" (really first sheet )

 

H1 needs to be the name of the first student.

 

Then in macro (if you know how to edit them) you need to change this line:

 

Loop Until cellnumber = 8

 

To the number of students aka 800.

 

 

In terms of "if the students don't exist" it won't fail, "however" it will take the "nearest" person to it and copy the data. It's not doing anything smart, other than seeing who is nearest to the match. Which 99.9% should work all the time If one is missing it'll take 2nd closest etc etc. (or should ). All its doing is counting the number of "changes" needed to get to the actual name. Thus a large number = not close. etc etc.

 

As a disclaimer, It's not exactly tested throughly so something may break

 

Steve

[burgemaster]

Steve thats worked really well!

I am going through year by year and so far it has incorrectly matched about 5 per year. These were also acceptable as sometimes the Ad name was C.Jones instead of Chris etc.

Really gratefull for this, it will hopefully save me hours of work.

 

One last thing if this is very easy to add, I have a perfectly matched list over on the right hand side. But I have more AD accounts than MIS account, I cant see which of the names it didnt match up.

Is there a way that any names from E that are not from column N could be highlighted or something? So I can then delete them from AD?

If not or any hassle dont worry!!

 

Many thanks for all your help on this. Really gratefull

[Steve21]

Honestly that really confused me :S Do you mean you're missing some, or some have matched with a different AD?

 

Any chance you could show me an example? Can PM my email if you don't want to post data publically.

 

Steve

[burgemaster]

Sorry,

 

eg. MIS has the names, bob & james. But in AD we have names bob,james, david and Tom.....

 

Bob and James get matched but David and Tom probably left and we didnt remove their accounts from AD. Could these names not on the MIS list get flagged up?

 

 

ALSO: For anyone else using this, you need to as well as editing the "Loop Until", also need to change the F$ value in row G

[Steve21]

Sorry,

 

eg. MIS has the names, bob & james. But in AD we have names bob,james, david and Tom.....

 

Bob and James get matched but David and Tom probably left and we didnt remove their accounts from AD. Could these names not on the MIS list get flagged up?

 

The issue is, there's no actual way of telling what is or isn't a close name. It's simply matching the nearest, as such they'll always get a match (or should unless you did something bad!!!! )

 

Oops forgot to mention the F$ one Just was doing an easier way to rank the searches >.< my bad!

 

I guess you "could" filter a large number, and ignore it, so it won't "always" add someone, but that's getting a bit iffier

 

Steve

[Steve21]

Although saying that, I guess the easiest way (if I understood what you mean) is make a counter near each email, and when it's copied show how many times its been copied, then if one was copied 2-3 times, you can easily check for those repeated ones?

 

Steve

[burgemaster]

Thanks Steve21,

Now all AD accounts are now matched to our MIS system.

Each account has the Student ID in the "EmployeeId" field and are in the correct house Security Group

 

Thanks again!

[Steve21]

Thanks Steve21,

Now all AD accounts are now matched to our MIS system.

Each account has the Student ID in the "EmployeeId" field and are in the correct house Security Group

 

Thanks again!

 

No prob, no prob! Hope it helped.

 

Steve