Share this content
0
5
839

Excel - 'Where else does this appear'

Say I have a timesheet.  I put in 'Mr A Bloggs' and wonder, 'What other entries were made for this client?'.

I know I can filter, but assume that is impractical for my purpose.  I could also highlight duplicates, but that would highlight every duplicate (so I believe).  I could even just search, but that's a little fiddly.

Any way I can highlight duplicates in a row based on one cell?  So consider B100 and highlight entries in B matching it?

Replies

Please login or register to join the discussion.

avatar
By BKD
10th Apr 2012 12:26

Conditional formatting

.

Thanks (0)
10th Apr 2012 12:34

Thanks

BKD wrote:

.

Care to elaborate?  As I say, I considered the use of 'Highlight duplicate values' but that would highlight anything that isn't unique, which would be the entire list.

 

Thanks (0)

A Macro

I came up with this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range, l As Range
    If Target.Column = 2 Then
        Set l = Range("A1").SpecialCells(xlCellTypeLastCell)
        If IsEmpty(Target.Value) Then
            For Each c In Range(Cells(1, Target.Column), Cells(l.Row, Target.Column))
                c.Interior.Pattern = xlNone
            Next
        Else
            For Each c In Range(Cells(1, Target.Column), Cells(l.Row, Target.Column))
                If Target.Value = c.Value Then
                    With c.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 65535
                    End With
                Else
                    c.Interior.Pattern = xlNone
                End If
            Next
        End If
    End If
End Sub

For the current sheet, if anything changes in column B, it will highlight (in yellow) both the cell that has changed and any other cells in column B that has the same value. If a cell is cleared, it will remove all highlighting.

Using numeric references, rather than "human" references means that the column range can be expanded by simply amending the "If Target.Column = 2" line.  It also means it's easy to adapt to work on a row-wise basis if that's what you want.  You've referred to rows, but then used a column reference.

If you're always going to use a fixed range it can be simplified significantly with "human" references.

Thanks (0)
avatar
By BKD
10th Apr 2012 14:42

What I was thinking of ...

If 'Bloggs' is in cell B100, then just use conditional formatting to highlight any other cell in column B (or anywhere else in the sheet for that matter) that matches the content of B100.

But it's so simple I'm wondeirng if I've misunderstood your objective.

I haven't tried Steve's macro, but it may be a much more elegant solution.

Thanks (0)
avatar
By ACDWebb
10th Apr 2012 14:47

An extra column with the formula

=COUNTIF($A:$A,An)

Where

A is the column with the names, or possibly duplicate references (say client number); andn is the row number for the row the formula is in

and copy that down. Mind that is possibly no better than Contitional Formatting duplicates, but at least narrow down those entries that have duplicates. You can filter for >1 and then pick the particular client.

 

Still not great though

Thanks (0)