+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 17
Scripts Thread, Script / Macro Request - Match row A to Row B - Or other solution? in Coding and Web Development; Hi all, We have all 900 students in 4 houses.... each house has a security group and each students has ...
  1. #1

    Join Date
    Aug 2007
    Posts
    811
    Thank Post
    98
    Thanked 64 Times in 46 Posts
    Rep Power
    25

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

    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
    Last edited by burgemaster; 20th July 2011 at 10:18 AM.

  2. #2

    Join Date
    Mar 2011
    Location
    Bournemouth
    Posts
    280
    Thank Post
    16
    Thanked 74 Times in 64 Posts
    Rep Power
    21
    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.

  3. Thanks to ChrisMiles from:

    burgemaster (20th July 2011)

  4. #3

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,686
    Thank Post
    334
    Thanked 513 Times in 481 Posts
    Rep Power
    178
    Could you post a few examples, just trying something and want to see if it works :P (Like your JBlogg one)

    Thanks,
    Steve

  5. Thanks to Steve21 from:

    burgemaster (20th July 2011)

  6. #4

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,686
    Thank Post
    334
    Thanked 513 Times in 481 Posts
    Rep Power
    178
    An example using a Levenshtein function:

    TestExample.PNG

    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

  7. #5

    Join Date
    Aug 2007
    Posts
    811
    Thank Post
    98
    Thanked 64 Times in 46 Posts
    Rep Power
    25
    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

  8. #6

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,686
    Thank Post
    334
    Thanked 513 Times in 481 Posts
    Rep Power
    178
    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)

    TestAD.PNG

    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

  9. #7

    Join Date
    Aug 2007
    Posts
    811
    Thank Post
    98
    Thanked 64 Times in 46 Posts
    Rep Power
    25
    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!!

  10. #8

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,686
    Thank Post
    334
    Thanked 513 Times in 481 Posts
    Rep Power
    178
    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

  11. #9

    Join Date
    Aug 2007
    Posts
    811
    Thank Post
    98
    Thanked 64 Times in 46 Posts
    Rep Power
    25
    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!

  12. #10

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,686
    Thank Post
    334
    Thanked 513 Times in 481 Posts
    Rep Power
    178
    Quote Originally Posted by burgemaster View Post
    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 :P

    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

  13. Thanks to Steve21 from:

    burgemaster (20th July 2011)

  14. #11

    Join Date
    Aug 2007
    Posts
    811
    Thank Post
    98
    Thanked 64 Times in 46 Posts
    Rep Power
    25
    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

  15. #12

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,686
    Thank Post
    334
    Thanked 513 Times in 481 Posts
    Rep Power
    178
    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

  16. #13

    Join Date
    Aug 2007
    Posts
    811
    Thank Post
    98
    Thanked 64 Times in 46 Posts
    Rep Power
    25
    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

  17. #14

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,686
    Thank Post
    334
    Thanked 513 Times in 481 Posts
    Rep Power
    178
    Quote Originally Posted by burgemaster View Post
    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 :P

    Steve

  18. #15

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,686
    Thank Post
    334
    Thanked 513 Times in 481 Posts
    Rep Power
    178
    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

SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. Converting VHS videos to either DVD or file format
    By ctbjs in forum How do you do....it?
    Replies: 8
    Last Post: 17th May 2013, 10:38 PM
  2. Request: Create a MSI to fix Adobe Updater Error
    By linkazoid in forum How do you do....it?
    Replies: 4
    Last Post: 18th May 2011, 02:10 PM
  3. Request: Creating a MSI to deploy Java Config
    By linkazoid in forum How do you do....it?
    Replies: 5
    Last Post: 26th February 2011, 09:18 AM
  4. Any free video to flash converters or ideas on video?
    By Quackers in forum Web Development
    Replies: 15
    Last Post: 26th June 2007, 03:23 PM
  5. To dual boot or not...
    By richard in forum General Chat
    Replies: 6
    Last Post: 28th February 2007, 02:32 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •