Excel Find & Replace...
We have just had Frog installed on site. I have linked it up to our MIS (PASS) and AD. Frog uses our MIS as the bible and uses AD to authenticate, unfortunately the majority of the usernames in our MIS bear no resemblance to the usernames in AD (or anything else really). We have implemented a system for creating usernames now but that still leaves us with a thousand or so users with random usernames. Aaanyway, I need to change Frogs usernames to match those in AD.
I have 2 excel sheets, one with Frogs UID, Surname, Forename, email and Username and the other sheet with AD's Surname, Forename, email and Username. Is there a way of merging the two. So Excel could match the surname and forename and use the username cell from the AD list to replace the username cell in the Frog list...
Assuming that there are no forename/surname discrepancies between Frog and AD, could you not sort both worksheets by surname, forename and then do a big copy and paste of the usernames. Or maybe you could sort by the email address if these match across both worksheets. As long as each row is in the same order across both worksheets you don't need to worry about string matching.
Copy both sheets into the same workbook for ease of use.
Insert a column before the first used column and Use the "concatenate" function to create a key - which will be the same in both sheets - i.e. concatenate the surname and forename. Do this for both existing sheets, frog and AD.
You can now use that as a key for vlookup, so you can lookup from one sheet to see if data is in another. You could work this from the two sheets, personally I'd create a third, name it "tmp" and use this little macro :
By selecting all the key attributes (as a block selection) on first the AD sheet, then run the macro, then repeat for the Frog sheet, you will end up with a list on the tmp sheet of all the unique keys. From that sheet, you can then use vlookup into the AD and Frog sheets and create a composite of the data from both.
Dim sRange, tRange As Range
Set sRange = Selection
Set tRange = Worksheets("tmp").Range("A1:A10000")
Dim iIdx As Integer
Dim finished As Boolean
' Determine unique values in a block of data
For Each cell In sRange
iIdx = 1
finished = False
While iIdx < 10000 And Not finished
If tRange(iIdx, 1).Value = cell.Value Then
finished = True
If tRange(iIdx, 1).Value = "" Then
tRange(iIdx, 1).Value = cell.Value
finished = True
iIdx = iIdx + 1
Both sheets are in the same workbook, one named Frog and one named AD.
As you suggested I have created a third sheet called tmp and concatenated the forename and surname in both sheets.
I have run the macro on both sheets with just the newly created full name column selected. How do I now use VLOOKUP to match these back? Sorry, I will do some googling in the meantime, I haven't really used Vlookup since college!
In sheet tmp, Column A will have the name key. Let's say the AD sheet is called AD and has the names in A1, email in B1, samaccountname in C1.
So in tmp B1, to lookup the AD email information we use :
To lookup samaccountname, the third parameter needs to be 3 (info from the third column returned).
Vlookup used this way will return the N/A error if it does not find the exact name match in the data range. You can use that to your advantage : create two columns to represent if the data is in the sheet and use :
=IF(ISERROR(Vlookup(A1,AD!$A$1:$C$1000,2, false)), 0,1)
You will then have two columns with 1 or 0 to tell you if that name is in the Frog data, AD data or both.