Results 1 to 13 of 13
  1. #1
    Star Lounger SoonerJim's Avatar
    Join Date
    Aug 2011
    Location
    Temple, Texas USA
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Changing multiple names to pseudo-names down a column

    I have a worksheet with a list of patient names in column A. The number of instances of each name is variable -- might be one, might be a hundred. I can do a find a replace; for example, change 20 instances of patient name Smith to Patient01, change ten instances of patient name Jones to Patient02, and so on. I can do it with find and replace but it is very tedious. Does anyone know of a macro or other Excel feature I could use to speed this up? Thanks.

    Jim Whitt
    Central Texas VA
    Temple, TX

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    A quick solution would be to put the names a second time in another column. Remove the duplicates (an option on the Data tab).
    In a column parallel to the unique names, place the pseudo names. If they're Patient1, etc., you can just enter one of them and fill down.
    Then, in the next column, fill down: =VLOOKUP(A2,$B$2:$C$31,2,FALSE)
    Where column A contains the REAL names, column B contains the unique names, and column C contains the Patient pseudo-names.
    I tested this with 30 names starting in row 2 (row 1 had headers).

    If you don't want the real names, you can then do a copy and paste special values on the pseudos and then delete the other columns.
    Last edited by kweaver; 2013-12-20 at 16:19.

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Based on KW's idea of comparing 2 columns, the easiest way I can think of to set which names from column A gets replaced by which name in the unique column is to click to select values. Easier than highlighting or typing, you can easily form the associations between the find and replace parameters. Here is a workbook that functions normally until you press the Find/Replace mode button. The Find/Replace form will open allowing you to enter the names by clicking the cells (refer to images).
    INSTRUCTIONS:
    1. Click on Find/Replace Mode button
    2. The Find/Replace Dialogue box will appear.
    3. With the find textbox selected (yellow border), click on a name to find in column A. The Find Textbox will automatically populate with the name.
    4. With the Replace with textbox selected (yellow border), click on the substitution name in column D. The Replace with textbox will automatically populate with the name.
    5. Click the Replace button to initiate all replacements.
    6. Click Cancel to close the form and exit the Rind/Replace mode.

    HTH,
    Maud

    FindReplace1.png FindReplace2.png

    Code:
    Public Sub FindReplace()
    On Error Resume Next
    Application.ScreenUpdating = False
    '--------------------------------
    'DECLARE AND SET VALUES
    Dim rng As Range
    Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = Range("A2:A" & Lastrow)
    '--------------------------------
    'SEARCH AND REPLACE VALUES
    With UserForm1
    For Each OriginalName In rng
        If OriginalName.Value = .TextBox1.Value Then
            OriginalName.Value = .TextBox2.Value
        End If
    Next OriginalName
    '--------------------------------
    'CLEANUP
    .TextBox1.Value = ""
    .TextBox2.Value = ""
    End With
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,179
    Thanks
    47
    Thanked 983 Times in 913 Posts
    Set up a counter "Pnum" from 001 to 999.
    Read the names from A and if the name does not equal "PatientXXX", where XXX is any 3 digits, then search/replace and replace with "PatientPnum+1".
    Increment the counter and move to the next row.
    No need to select anything, just run the macro.

    I'll let you do the coding.

    cheers, Paul

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Paul

    Perhaps I read this wrong. Does the poster wish to choose the specific PatientXXX to the same names in column A or assign a random (but the same) PatientXXX to all the Johns per say? If it does not matter which Patientxxx gets assigned to a specific group of same names, then it gets quite simple as you propose.

    Maud

    Code:
    Public Sub AssingPatientNum()
    Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    num = 1
    For I = 2 To Lastrow
        PatientName = Cells(I, 1)
        If Left(PatientName, 7) = "Patient" Then GoTo skip
        For J = 2 To Lastrow
            If Cells(J, 1) = PatientName Then
                Cells(J, 1) = "Patient" & Format(num, "##000")
            End If
        Next J
        num = num + 1
    skip:
    Next I
    End Sub

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I read the post as assigning a PatientXXX name to real names to apparently hide their identity.
    That's why I thought matching real names with the PatientXXX would work with a VLOOKUP (or as you propose, Maud, a macro).

  7. #7
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,179
    Thanks
    47
    Thanked 983 Times in 913 Posts
    I assumed the data was being anonymised so every real name had to be replaced with a number, but the same person must be the same anonymous number for the data to be valid.

    cheers, Paul

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Its obvious that each instance of the same name must have the same Patientxxx but my question is, does the poster want to assign which xxx or can the formula/macro assign it randomly to the group of names?

  9. #9
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,179
    Thanks
    47
    Thanked 983 Times in 913 Posts
    I would have thought this was a better way to find the number of rows.

    Lastrow = ActiveSheet.UsedRange.Rows.Count

    cheers, Paul

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    That will work, however, you run the risk of non-significant data added at the end included in the looping that may alter the code output such as "Total=" in a different column. Only in a controlled instance, such as when the code adds the lines or when it does not matter what is in the last row, will I use the UsedRange method. An example would be to append a block of text below the last row.

    Maud

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    FWIW,

    In my experience .UsedRange can be flaky at best. If used directly after a SAVE it is usually reliable but if used after edits which add/delete rows/columns it can be flaky. Thus I do not depend on it. Of course it may be fixed in newer versions. I haven't tested this because I have methods that always work (like Maud's in post #5) so I just keep using them.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Paul,

    Here is an example of unexpected values when using the UsedRange method to find the last row. Consider the image below. Last row is calculated by 2 different methods. First is a custom routine simulating UsedRange which looks at the last row of each column using the formula that RG and I favor. It then takes the max value as the last used row. Method 2 is the formula that you offered. When the code is run, there is a difference in the results.

    Method 1 reports a value of 16 which is the last row. Method 2 reports an unexpected value of 18 which is not correct. As its name infers, UsedRange look at the number of rows that have been used (3 to 20 inclusive) which is a total of 18 used rows, however, 18 is not the last row. Empty cells with cell borders and comments will be factored in as being used. Even if you remove the value, formatting or the comment from the cell, it may unreliably still be used which can lead to serious code errors. If so, the only way to correct this is to delete the rows.

    Code:
    Public Sub LastUsedRow()
    Dim s(10) As Variant
    '--------------------------------
    'METHOD 1 - SIMULATE USEDRANGE
    For J = 1 To 10
        s(J) = Cells(Rows.Count, J).End(xlUp).Row
    Next J
    Lastrow1 = WorksheetFunction.Max(s)
    '--------------------------------
    'METHOD 2
    Lastrow2 = ActiveSheet.UsedRange.Rows.Count
    '--------------------------------
    MsgBox "Method 1: Lastrow1 = " & Lastrow1 & Chr(13) & _
           "Method 2: Lastrow2 = " & Lastrow2
    End Sub
    Lastrow1.png

  13. #13
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,179
    Thanks
    47
    Thanked 983 Times in 913 Posts
    Thanks for the clarification, but I have used it to limit the number or rows/columns I'm working with rather than a hard and fast number and I always test the cells to see if there is any work to do.

    cheers, Paul

Posting Permissions

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