Highlight Duplicate Numbers in a column with both numbers and text.

[nikneven]

Ok. I have a column of data. Half the entries in the column will be unique numbers (IP addresses) and half will be the text "DHCP" Since the word "DHCP" will obviously repeat, I am looking for a macro to highlight duplicate numbers and ignore the word "DHCP".

The macro I am using in other columns to highlight dupilcate entries is:

Sub TestForMachineNameDups()

        Dim LLoop As Integer
        Dim LTestLoop As Integer
        Dim LClearRange As String

        Dim Lrows As Integer
        Dim LRange As String
        Dim LChangedValue As String
        Dim LTestValue As String

        'Test first 200 rows in spreadsheet for uniqueness
        Lrows = 200
        LLoop = 2

        'Clear all flags
        LClearRange = "E2:E" & Lrows
        Range(LClearRange).Interior.ColorIndex = xlNone
        Range("E1").Value = ""

        'Check first 200 rows in spreadsheet
        While LLoop <= Lrows
            LChangedValue = "E" & CStr(LLoop)

            If Len(Range(LChangedValue).Value) > 0 Then

                'Test each value for uniqueness
                LTestLoop = 2
                While LTestLoop <= Lrows
                    If LLoop <> LTestLoop Then
                        LTestValue = "E" & CStr(LTestLoop)
                        'Value has been duplicated in another cell
                        If Range(LChangedValue).Value = Range(LTestValue).Value Then
                            'Set the background color to red
                            Range(LChangedValue).Interior.ColorIndex = 3
                            Range(LTestValue).Interior.ColorIndex = 3
                            Range("E1").Value = "Multiple Machine Name Error."

                        End If

                    End If

                    LTestLoop = LTestLoop + 1

                Wend

            End If

            LLoop = LLoop + 1
        Wend

    End Sub

Any help would be greatly appreciated.

 

[Peter_SSs]

nikneven

1. Am I correct in assuming the 'numbers' are not actually numbers but text strings like 208.77.188.166?

2. Do you really need a macro for this?

Could you just use normal Conditional Formatting with a condition like that shown below?

Is the data layout and result below what you have/want?

Excel Workbook
	A
1	12.3.456.5
2	DHCP
3	1.1.2.3
4	DHCP
5	2.2.2.3
6	DHCP
7	12.3.456.5
8	DHCP
9	3.3.3.3
10	DHCP
11	1.5.6.4
12	DHCP
13	1.5.6.4
14	DHCP
Format IPs

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =AND(COUNTIF(A:A,A1)>1,ISNUMBER(LEFT(A1,1)+0))	Abc

 

[jindon]

Conditional Format ?

=And(E2<>"DHCP",CountIf(E:E,E2)>1)

 

[nikneven]

Actually that's a very good idea, and now I feel like an idiot. The only thing that i couldn't do with a conditional format is set an alert cell to bring up a message if there are duplicates. Is there a way to have cell A1 = "duplicate" if there are duplicates in A2:A200

 

[Peter_SSs]

Actually that's a very good idea, and now I feel like an idiot. The only thing that i couldn't do with a conditional format is set an alert cell to bring up a message if there are duplicates. Is there a way to have cell A1 = "duplicate" if there are duplicates in A2:A200

Hmm, there may be a better way, but this is what I have come up with so far. The formula in A1 should be entered without the {} but then confirmed with Ctrl+Shift+Enter, not just Enter. If entered correctly, Excel will enclose the formula with the {}.

Excel Workbook
	A
1	Duplicate IPs
2	12.3.456.5
3	DHCP
4	1.1.2.3
5	DHCP
6	2.2.2.3
7	DHCP
8	12.3.456.5
9	DHCP
10	3.3.3.3
11	DHCP
12	1.5.6.4
13	DHCP
14	1.5.6.4
15	DHCP
16
Format IPs

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Cell Value not equal to =""	Abc
A2	1. / Formula is =AND(COUNTIF(A:A,A2)>1,ISNUMBER(LEFT(A2,1)+0))	Abc

 

[nikneven]

That works wonderfully. Thank you so much for your help! I think I am going to change all of my validation to this, much less resource intensive!

Thanks again.

 

[nikneven]

One more question. In switching all of my duplicate checking over to conditional formatting, how would I deal with checking 2 columns. Let me explain, Column B is First Names, Column C is last names. They both start at B2 and C2. How do I highlight duplicates but only if the full names match, ie, highlighting all the John Smiths, but not all the Johns, or all the Smiths?

And then again, in B1, an alert that says Duplicate Name, but again, only if the full first and last name is duplicated.

Thanks

 

[Peter_SSs]

I don't really know much about your sheet(s) so I'm not sure if this may become a problem but before you rush off and change everything to this type of Conditional Formatting, you should be aware of the following.

Conditional Formatting is volatile and the more complex and resource-heavy formulas you use in Conditional Formatting the more this issue is likely to become relevant. The bottom line is that Conditional Formatting can cause your sheet speed performance to drop-off.

If you have a large amount of data that is not changing regularly, it may be just as good or better to just run your macro code when needed, rather than have CF recalculating everytime you 'blink'. Now Excel can handle a fair bit, and I'm not trying to push you away from CF, but I just wanted you to be aware of this issue and take note as you introduce the Conditional Formatting to your sheets.

Now to your last question, and considering the above, I would attack this issue by using helper columns this way.

1. In two vacant columns (I have used D:E but it could be any columns) enter the formulas shown, adjusting the 2000 in the formulas to be a number greater than the number of rows you are likely to use. The row 2 formulas are copied down to whatever row you adjusted the 2000 in the formulas to.

2. Select B2:Cxx where xx is the adjusted 2000 figure and apply the CF shown for cell B2.

3. Apply the formula and CF as shown to B1.

4. Hide columns D:E

This method should minimise any effect of the volatility of the CF.

Excel Workbook
	B	C	D	E
1	Duplicate Names			TRUE
2	Tom	Jones	Tom|Jones	TRUE
3	Jen	Smith	Jen|Smith	FALSE
4	Ann	Jones	Ann|Jones	FALSE
5	Tom	Jones	Tom|Jones	TRUE
6	Jen	Black	Jen|Black	FALSE
7	Tom	Brown	Tom|Brown	FALSE
8			|	FALSE
9			|	FALSE
Conditional Format Duplicates

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	1. / Cell Value not equal to =""	Abc
B2	1. / Formula is =$E2	Abc