+ Post New Thread
Results 1 to 5 of 5
How do you do....it? Thread, Excel Find & Replace... in Technical; Morning all We have just had Frog installed on site. I have linked it up to our MIS (PASS) and ...
  1. #1
    wiggum123's Avatar
    Join Date
    May 2010
    Location
    Bristol
    Posts
    58
    Thank Post
    13
    Thanked 2 Times in 2 Posts
    Rep Power
    9

    Excel Find & Replace...

    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?

  2. #2

    Join Date
    Jan 2011
    Location
    London
    Posts
    32
    Thank Post
    11
    Thanked 5 Times in 5 Posts
    Rep Power
    8
    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.

  3. #3


    Join Date
    May 2009
    Posts
    3,279
    Thank Post
    290
    Thanked 884 Times in 662 Posts
    Rep Power
    340
    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 :

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

  4. Thanks to pcstru from:

    wiggum123 (20th August 2012)

  5. #4
    wiggum123's Avatar
    Join Date
    May 2010
    Location
    Bristol
    Posts
    58
    Thank Post
    13
    Thanked 2 Times in 2 Posts
    Rep Power
    9
    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

  6. #5


    Join Date
    May 2009
    Posts
    3,279
    Thank Post
    290
    Thanked 884 Times in 662 Posts
    Rep Power
    340
    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 :

    Code:
    =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.

SHARE:
+ Post New Thread

Similar Threads

  1. MS Excel find and replace CR+LF
    By firefighting in forum Office Software
    Replies: 5
    Last Post: 20th November 2013, 08:18 AM
  2. Script to Find and Replace within an INI file.
    By timethrow in forum Scripts
    Replies: 1
    Last Post: 4th February 2012, 10:17 AM
  3. Speaker Amp replacement
    By mtdmitchell in forum AV and Multimedia Related
    Replies: 7
    Last Post: 21st September 2011, 09:22 PM
  4. Replies: 15
    Last Post: 16th September 2011, 09:03 AM
  5. Find File & Replace
    By 1202 in forum Windows 7
    Replies: 0
    Last Post: 3rd May 2009, 08:15 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •