Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Determining textual differences between cells

  1. #1

    Determining textual differences between cells



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Each week I put together a list of changes to a pre-existing database and then send off those changes to another group. Later in the week when they return the updated file I check to see if they have made all correct changes. These changes generally include adding or removing text from specific cells or repositioning columns.

    I am looking for a dynamic way to track if all necessary corrections were made. To be clear, I do not need simple conditional formatting which will tell me which cells are not alike. I know which cells are not alike. I need to be able to say in some form of coding:

    "Check to see if cell X is the same as cell Y, minus the highlighted/colored/striked-out text." Or similarly "Check to see if cell X is the same as cell Y, except with the addition of the text that was colored a specific color in cell Y."

    Any advice would be greatly appreciated.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi there, and welcome to the forum.

    I'm not sure I'm following here... you're looking for a way of tracking what changes were actually made to the file? Is that it?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    It is not clear how you get the information, is it separate sheets, the same sheet; and whether the two data items contain the checked data or not.

    Can you post an example workbook?

  4. #4
    Thank you for replying. To be more clear, I have a file which is stored on database software that needs to be updated regularly. This file can be either extracted into Word or Excel. I generally extract it into Excel because it is my assumption that an Excel spreadsheet is better at anaylzing data than a Word table, but if based on the following details you disagree please let me know.

    Steps:

    1. Extracl file into Excel
    2. Make updates in Excel file
    a. Additional text needed: new text is inserted in red lettering, e.g. The big brown dog went to the park.
    b. Too much text: text needed to be deleted is struck out (however, it is my guess that moving forward it will be easier to get Excel to recognize colors than whether or not text is struck out)
    c. A cell within a combination of a and b
    3. Iteration with updates are handed off to second team
    4. Second team manually makes all updates to database software (though this might seem like an extra step updating the software takes a lot of time and thus can be done more effictevely for a number of reasons by second team)
    5. After I am informed that all updates have been made, I then download the most current iteration of the file and manually check to see that all necessary updates have been made to the previously stipulated cells.

    The problem is that by manually going through -- or even using track changes in Word as a "Cliff Notes," the process still takes several hours to go through 300-400 pages of reports. I need a way to do this more dynamically. Any thoughts?

    My current thoughts:

    1. Cells in which language has been added: Verification is easy, A1=B1, true or false
    2. Cells in which language needs to be removed: Can a macro be developed to recognize the struck out (or potentially colored) wording and then verify if cell in column B is the same as related cell in column A, except without the specific text that needs to be removed
    3. Cells in which addition and subtraction must occur, I assume that once the above step is figured out this will be relatively easy to figure out

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Just out of curiosity...
    • You download data from the database server into an Excel file (into Workbook A)
    • You make changes to the Excel file (Workbook A)
    • You send it to somone to make changes in the database (from Workbook A)
    • You download data from the database server into an Excel file to see if all changes were made (into Workbook B)

    Is that right?

    If so, why do you need to check if the changes are made? Shouldn't you just be able to compare Workbook A's text to Workbook B's? If they match exactly, then the changes were made. If not, then something is still outstanding, so you could highlight the rows that aren't right.

    Or did I miss something?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Ken:

    No, you are correct. Once updates have been made to the file on the database software I download the latest interation of the updated file into an Excel workbook (workbook B). I then check workbook B against the original workbook (workbook A) that was sent to this other team with all necessary changes.

    I'm looking for a dynamic way to check workbook A against workbook B. As I said, for the cells in which text is added there is not problem, because workbook A will match workbook B and if it does not than I know that the correct changes were not made. However, checking cells in which text was supposed to be removed is more difficult as the new cell will obvioulsy be different than the old cell. I need to know that the specific text which was suppose to be removed was in fact removed.

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Ah, I see where you're going with this.

    Curious, your download from the database, is it all in a single column? Could you give us a workbook that shows some sample data of raw, correct, re-download data scenarios? Just a dozen records or so, but something that highlights the issues?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    I download Workbook B and then copy and paste Workbook A along side Workbook B. Generally the majority of corrections are made in 1 column -- so I am basically only concerned about comparing 2 columns (1 from Workbook A and 1 from workbook B).

    Currently text that needs to be added is put into Workbook A as red and text that is being removed is formatted as strikethrough. I am not married to either of these methods for identifying text to be added and deleted and would gladly do it differently if there's an easier way.

    Please see my attachment as a very basic example of the type of textual modifications to which I am referring.
    Attached Files Attached Files

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I knocked up a quick VBA function for this:

    Code:
    Function CleanText(rng As Range) As String
        Dim ch As Long
        Dim sTemp As String
        
        For ch = 1 To Len(rng.Text)
            If rng.Characters(Start:=ch, Length:=1).Font.Strikethrough = False Then
                sTemp = sTemp & Mid(rng.Text, ch, 1)
            End If
        Next ch
        CleanText = sTemp
    End Function
    You then use the formula in your worksheet as:
    Code:
    =CLEANTEXT(A2)
    This will sanitize all strikeouts to give you a revised pattern of what you're looking for. Be aware that trailing spaces should also be struck out as well, as you can see from the example.

    I'll also warn you that this will probably slow down large workbooks considerably, but you may be okay with that as it accomplishes your goals.

    The code needs to go in a Standard Module before you apply the formula to your worksheet.

    Hope it helps,
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #10
    Wow, thank you. The code works really well. I appreciate your help so much. On a side note, do you recommend any particular resources for teaching oneself VBA?

Page 1 of 2 1 2 LastLast

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
  •