+ Post New Thread
Results 1 to 12 of 12
Scripts Thread, Matching to fields then replace once field if differnt in Coding and Web Development; This might sound strange but can anybody thing of a way to script this. I have a copy of MDL_user ...
  1. #1

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,714
    Thank Post
    269
    Thanked 1,116 Times in 1,012 Posts
    Rep Power
    345

    Matching to fields then replace once field if differnt

    This might sound strange but can anybody thing of a way to script this. I have a copy of MDL_user from my moodle database which has all my lea's usernames in - half of mine our different to them and I am hoping to move this in house. Basicly I want the script to search csv1 and read the first name, surname fields and samaccountname - then check csv2 and if the first name and surname match replace the username.

    Currently I have 2 spreadsheet one with the headings givenname,sn and samaccountname

    the second
    id,auth,confirmed,policyagreed,deleted,mnethostid, username,password,idnumber,firstname,lastname,emai l,emailstop,icq,skype,yahoo,aim,msn,phone1,phone2, institution,department,address,city,country,lang,t heme,timezone,firstaccess,lastaccess,lastlogin,cur rentlogin,lastip,secret,picture,url,description,ma ilformat,maildigest,maildisplay,htmleditor,ajax,au tosubscribe,trackforums,timemodified,trustbitmask, imagealt,screenreader

    Any Ideas?

  2. #2

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,182
    Thank Post
    1,285
    Thanked 1,029 Times in 729 Posts
    Rep Power
    658
    Is this something you're going to be doing very regularly? As in daily? If not, you're probably gonna be better off just opening both up in Excel and using a VLookup

  3. #3

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,714
    Thank Post
    269
    Thanked 1,116 Times in 1,012 Posts
    Rep Power
    345
    Not regularly just once - but its about 3000 users - I don't fancy it to much!

  4. #4

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,182
    Thank Post
    1,285
    Thanked 1,029 Times in 729 Posts
    Rep Power
    658
    It's a simple procedure in Excel:

    Create a new workbook, import CSV1 to Sheet1, CSV2 to Sheet2.
    Assuming CSV1 contains your in-house usernames, and A=Surname, B=Forename, C=Username, and that Sheet2 contains the Moodle usernames you want to look up (for arguments sake, we'll assume the columns are the same), do this:

    1) Insert a column at the start of Sheet2, and insert this formula to amalgamate the surname and forename (this will be column A, moving your other columns 1 place to the right)
    =B1&C1

    2) On Sheet1, after the last column, add this formula:
    =IF(ISNA(VLOOKUP(A1&B1,Sheet2!$A$1:$D$2,4,FALSE)), C1,VLOOKUP(A1&B1,Sheet2!$A$1:$D$2,4,FALSE))

    3) Drag the formulae down to every used row and voila! Done.


    Obviously you'll need to edit the cell references in those formulae to match your CSV's, but that's the method I'd go for, takes 5 minutes and much simpler than writing a script

  5. Thanks to LosOjos from:

    glennda (16th February 2011)

  6. #5

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,714
    Thank Post
    269
    Thanked 1,116 Times in 1,012 Posts
    Rep Power
    345
    Ok - I've got that and it works on the first page

    How can i change that so that it I can put that formula in username field of the second one and it matches back to the first! I tried to edit and seem to just keep getting the username from the second sheet!

    On sheet 2 I have username in field g and firstname in k and surname in field l

    I am trying to put it in row H so i can then delete row G once I save as a csv so it doesn't ruin the formula

    Cheers

  7. #6

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,182
    Thank Post
    1,285
    Thanked 1,029 Times in 729 Posts
    Rep Power
    658
    on Sheet1, add a column at the start that will concatenate the surname and forename (i.e. =B1&C1)
    on Sheet2, use this forumla:
    =IF(ISNA(VLOOKUP(I1&K1,Sheet1!$A$1:$C$2,4,FALSE)), G1,VLOOKUP(A1&B1,Sheet2!$A$1:$D$2,4,FALSE))

    EDIT:
    it's the same process either way, I suppose understanding the formula would help you more than me just telling you.

    The first step; adding a column to the start and concatenating surname and forename; is to create a unique ID for each that both sheets will have in common, allowing the lookup to be performed. It has to be the first column, as VLookup's will only work if the column they are looking up is the first in the range defined.

    The formula in the second step does a number of things. We first check to see if the vlookup throws up an error (the ISNA() part). If it does, this means that no match was found, so we grab the existing value (at C1 in the example). If it doesn't we have a match, so then we grab the result of the vlookup.

    The vlookup itself takes a number of arguments: first, we give it a value to lookup (the surname & forename concatenated in the same order to give us our unique ID). We then tell Excel where our table is that we're looking up from, making sure the first column contains our unique ID's and that the range at least goes as far as the column we want to return a value from. The 3rd argument tells Excel which column from the previously defined table to return the value from. The final column tells Excel that it must be an exact match, and not just similair.
    Last edited by LosOjos; 16th February 2011 at 02:18 PM.

  8. #7

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,714
    Thank Post
    269
    Thanked 1,116 Times in 1,012 Posts
    Rep Power
    345
    I can't seem to get it working

    I have attached a sample spreadsheet just so you can see what i am trying to do clearer.

    I want to be able to look at sheet1 firstname, surname and then match it to sheet 2 and if the username is different in sheet 2 replace it

    make sense?
    Attached Files Attached Files

  9. #8

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,182
    Thank Post
    1,285
    Thanked 1,029 Times in 729 Posts
    Rep Power
    658
    Have a look at the edited version, I've highlighted the columns I've added in red

    EDIT: to replace the username, it's just a case of copying the new column and pasting as values in to the username column - you could automate this with a macro, but it's more time and effort than it's worth to be honest
    Attached Files Attached Files

  10. Thanks to LosOjos from:

    glennda (17th February 2011)

  11. #9

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,714
    Thank Post
    269
    Thanked 1,116 Times in 1,012 Posts
    Rep Power
    345
    Yes I'm not worried about deleting the first column - I just needed it to be the other way around as they are not going to be in the same order. If that makes sense as it might be that joe bloggs is in row3 on spreadsheet 1 but row 6 on spreadsheet 2!

    Thanks I'll give it a play.

  12. #10

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,182
    Thank Post
    1,285
    Thanked 1,029 Times in 729 Posts
    Rep Power
    658
    Quote Originally Posted by glennda View Post
    Yes I'm not worried about deleting the first column - I just needed it to be the other way around as they are not going to be in the same order. If that makes sense as it might be that joe bloggs is in row3 on spreadsheet 1 but row 6 on spreadsheet 2!

    Thanks I'll give it a play.
    It doesn't matter what order they are in, that's what the vlookup is for, it will return the values from the row it finds a match for (in this case based on Surname & Forename). One thing to watch out for though is that if there are 2 Joe Bloggs, then whichever comes first in the list you're looking at (Sheet1) will be returned and not the other one, so just check any that you know you have more than one of

  13. #11

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,714
    Thank Post
    269
    Thanked 1,116 Times in 1,012 Posts
    Rep Power
    345
    Yes I meant when doing it the other way around like the first time? if i had just copy and pasted the result across it would have been random!

  14. #12

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,182
    Thank Post
    1,285
    Thanked 1,029 Times in 729 Posts
    Rep Power
    658
    Quote Originally Posted by glennda View Post
    Yes I meant when doing it the other way around like the first time? if i had just copy and pasted the result across it would have been random!
    I'm with you now! It's been a long term! lol

SHARE:
+ Post New Thread

Similar Threads

  1. connect outlook to an exchange server on a differnt domain ip range
    By vote-for-pedro in forum Windows Server 2000/2003
    Replies: 0
    Last Post: 7th July 2010, 12:39 PM
  2. Sun Matching Grant
    By linescanner in forum Hardware
    Replies: 18
    Last Post: 20th October 2009, 11:35 PM
  3. Sun Matching Grant
    By linescanner in forum Hardware
    Replies: 3
    Last Post: 19th May 2009, 08:27 AM
  4. Replies: 1
    Last Post: 3rd October 2008, 11:36 AM
  5. OWA Alias Matching
    By apeo in forum Windows
    Replies: 2
    Last Post: 17th December 2007, 03:37 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
  •