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 ...
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
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
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
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
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.
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
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!
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
LinkBack URL
About LinkBacks
Originally Posted by glennda 
