+ Reply to Thread
Results 1 to 8 of 8

Excel 2007 : Comparing columns where values aren't quite exact matches

  1. #1
    Registered User
    Join Date
    07-11-2012
    Location
    KCMO
    MS-Off Ver
    Excel 2007/2010
    Posts
    4

    Comparing columns where values aren't quite exact matches

    Hi,
    I just joined in hopes of getting some help and hoping to help others as well. I tried searching the forums so if this has already been solved, I do apologize.

    I currently have a spreadsheet with two columns and I'm trying to highlight items in column A that are in column B. The only problem is that in a number of cases, the values aren't going to match exactly due to different references by different departments in the company. So, Server1 is the same as Server1z. I started to manually search & highlight, but figured that there has to be a better way. Because Column A has just under 200 rows, and Column B has just over 4000 rows, it'd take too long to do manually.

    Here's an example of the spreadsheet:

    Column A Column B
    Backup Client Name DB Server Name
    Server1z Server1
    Server2z Server3
    Server3z Server16
    Server10z Server18
    Server16z Server20
    Server20z
    Server25z

    Based on this example, I'd like for Server1z, Server3z, Server16z & Server20z to be highlighted in Column A because everything but the z matches from column B. All the solutions I've found here an elsewhere online seem to have to have exact matches for values.

    I appreciate any help you can provide.

    -Mike

  2. #2
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: Comparing columns where values aren't quite exact matches

    Hello there,

    Here is a macro that should do the trick

    Please Login or Register  to view this content.
    To insert it into your worksheet press Alt+F8. In the macro name box clear the contents if there is anything in it and then type the work HighlightMacro.

    Next select the Create option.

    Copy the above code and paste it in between then text Sub HighlightMacro and End Sub.

    Anything that is green is a comment I placed in the code to help you understand.

    Close VBA.

    To Run the code press Alt+F8, select the Highlight Macro and then select run.

    Let me know if you have any questions and if this works for you!

    Thanks!

    RVASQUEZ

  3. #3
    Registered User
    Join Date
    07-11-2012
    Location
    KCMO
    MS-Off Ver
    Excel 2007/2010
    Posts
    4

    Re: Comparing columns where values aren't quite exact matches

    RVASQUEZ, thanks so much for the quick reply! I added in the macro as outlined and was able to get it to work. The only thing that was off was the highlighted entries were in column B, and I was needing to get column A highlighted. I got my numbers reversed and column A has the 4000 lines and B is the 200. What I'm trying to do is see which DB Servers (column B) appear in column A to identify the non-db servers. I tried editing the macro, but wasn't able to get it to highlight column A.

    Is it a fairly easy edit to get this macro to highlight column A instead of B?

    Thanks so much for the reply!

    -Mike

  4. #4
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: Comparing columns where values aren't quite exact matches

    Hello there,

    Without seeing your data I'm not quite sure, but try this.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-11-2012
    Location
    KCMO
    MS-Off Ver
    Excel 2007/2010
    Posts
    4

    Re: Comparing columns where values aren't quite exact matches

    RVASQUEZ, that's closer! I'm attempting to upload the spreadsheet that I'm working with. If you look at B2, you can see then in A3 the value is the same but with the added Z at the end. I would like to have A3 highlighted, and the same for B3/A6, and so on.

    Thanks so much!

    -Mike
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: Comparing columns where values aren't quite exact matches

    Alrighty, I knew it couldn't have been that easy lol. Here is the updated code.

    Please Login or Register  to view this content.
    I've attached the workbook with the updated code as well.

    Let me know how this works!

    Thanks!

    RVASQUEZ
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-11-2012
    Location
    KCMO
    MS-Off Ver
    Excel 2007/2010
    Posts
    4

    Re: Comparing columns where values aren't quite exact matches

    RVASQUEZ, thank you, thank you, thank you! That is exactly what I was looking for! Thank you so much!!!!

  8. #8
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: Comparing columns where values aren't quite exact matches

    No problem!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1