Comparing two sets of cells within a row and highlighting same-row duplicates in Excel

Question

I've got a somewhat specific task I'm trying to accomplish in Excel that appears to be beyond my depth. I've got a dataset with two five-word lists per each row/observation, like this example:

What I'm hoping to accomplish is to highlight all of the words that are in both 5-word lists within a single row. The amount of overlap between these two five-words lists varies from one row to the next. And I'm not concerned with identifying any duplicate entries across different rows. That is, it'd be great if it's possible to create a macro that would give this:

I've searched quite a bit on this site and using google to figure out how to create a macro to do this. I've found a number of similar macros, but every one that I've found is geared towards identifying all of the duplicates between two entire sheets, or two entire columns, or something similar, which doesn't quite match what I'm trying to do. Based on the macros I have been able to find, I get the sense that doing what I want should be possible, but I don't know enough about visual basic to edit the macros I've found to suit my needs.

If it's possible to program a macro to do this, it'd save me quite a bit of time, since otherwise I'm looking at doing this duplicate-identification manually for two current datasets that I have (each with 150-200 observations), plus I plan on collecting data in the future that would require this same procedure. Beyond that, any macro that's capable of helping me here may be able to help others with similar needs.

Thanks in advance for any help you're able to provide!

Have you looked at using conditional formatting? – Alex P Jan 24 '14 at 18:04 — Alex P, Jan 24 '14 at 18:04

score 0 · Accepted Answer · answered Jan 24 '14 at 18:18

0

Try conditional formatting.

Select "table 2" (e.g. I2:Mn where n is the last row) with I2 (upper left corner) being the active cell. Then use this formula:

=OR(I2=$C2:$G2)

answered Jan 24 '14 at 18:18

Ron Rosenfeld

53,870
7
28
60

This worked great -- it accomplished precisely what I wanted. Thanks so much for your help, Ron! – user3232971 Jan 28 '14 at 15:48

score 0 · Answer 2 · answered Jan 24 '14 at 18:45

0

I think using Conditional Formatting is maybe a simpler solution but one way to do this using VBA is as follows:

Sub HighlightDuplicates()
    Dim masterList(), highlightList(), rw As Long, col As Long

    masterList = Range("C1:G150")
    highlightList = Range("I1:M150")

    For rw = 1 To UBound(masterList, 1)
       For col = 1 To UBound(masterList, 2)
            If highlightList(rw, col) = masterList(rw, col) Then
                Cells(rw, col).Offset(0, 8).Interior.Color = vbRed
            End If
        Next col
    Next rw
End Sub

Here we read both lists in as arrays and then iterate over them to check for matches.

The Offset(0, 8) is a bit of a magic number (yikes!) which gets the correct cell highlighted based on your layout.

answered Jan 24 '14 at 18:45

Alex P

12,249
5
51
70

Thanks for your help, Alex! Conditional formatting did indeed turn out to be an easy solution. For some reason I couldn't get this VBA script to achieve the same effect: when I ran it, it resulted in this: http://i.imgur.com/Lk5CLk0.png. It could very well be something I'm doing wrong, but seeing as I was able to accomplish my goal using conditional formatting it's not that big of a deal. Thanks again! – user3232971 Jan 28 '14 at 15:56
Good stuff. I think you've got the simplest solution which is great. – Alex P Jan 28 '14 at 17:55

Comparing two sets of cells within a row and highlighting same-row duplicates in Excel

2 Answers2