Matching to fields then replace once field if differnt

[glennda]

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,email,emailstop,icq,skype,yahoo,aim,msn,phone1,phone2,institution,department,address,city,country,lang,theme,timezone,firstaccess,lastaccess,lastlogin,currentlogin,lastip,secret,picture,url,description,mailformat,maildigest,maildisplay,htmleditor,ajax,autosubscribe,trackforums,timemodified,trustbitmask,imagealt,screenreader

 

Any Ideas?

[LosOjos]

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

[glennda]

Not regularly just once - but its about 3000 users - I don't fancy it to much!

[LosOjos]

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

[glennda]

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

[LosOjos]

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.

Edited February 16, 2011 by LosOjos

[glennda]

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?

userswap.xls

[LosOjos]

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

userswap.xls

[glennda]

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.

[LosOjos]

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

[glennda]

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!

[LosOjos]

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