Excel Find & Replace...

[wiggum123]

Morning all

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...

 

Any ideas?

[andypitts]

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.

[pcstru]

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 :

 

Sub UnqBlock()

   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
           Else
               If tRange(iIdx, 1).Value = "" Then
                   tRange(iIdx, 1).Value = cell.Value
                   finished = True
               End If
           End If
           iIdx = iIdx + 1
       Wend
                   
   Next
   

End Sub

 

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.

[wiggum123]

Cool

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!

 

Ben

[pcstru]

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 :

 

=Vlookup(A1,AD!$A$1:$C$1000,2, false)

 

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.