• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Duplicate Values (not Conditional format)

M

manoj_th

Member
Hello Sir,


I want to highlight the values which are repeated continuously.

Please find the link for the excel file and do the needful.


http://www.2shared.com/file/jsdbaiZ_/Book1.html
 
Hi, I am still learning excel..so I am sure there will be much better answers following this. But give this a try..

Assuming you have the data from B2:B10


In cell c2 type this formula


=OR((IF(B3=B2,TRUE,FALSE)),(IF(B2=B1,TRUE,FALSE)))


copy this formula from c2:c10


In conditional formatting using formula, type =c3 and format as per your requirement


Hope i have made you understand...


http://www.2shared.com/file/D3AqE9wx/Book1.html
 
Dear Manoj_th,


Can you give more explanation regarding your question? I am not expert in excel, you can try this formula to get unique values & repetitions. Assume that your data is from B2:B10,you can use this formula in a helper column to get the unique values =IF(COUNTIF($B$2:B2,B2)=1,1,""), if you need to count the number of repetitions then you can use =COUNTIF($B$2:B2,B2) which will check particular cells, repetitions times. Try this, i think it will helpful to you
 
This is probably slower than using conditional formats, but should work.

[pre]
Code: 
Sub ColorDupes()
Dim MyRange As Range

'Before running macro, select cells you want to apply macro to
Set MyRange = Selection

Application.ScreenUpdating = True

For Each c In MyRange
If WorksheetFunction.CountIf(MyRange, c.Value) > 1 Then
c.Interior.ColorIndex = 3 'Color red
Else
c.Interior.ColorIndex = 0 'No color
End If
Next c

Application.ScreenUpdating = True

End Sub
[/pre]
 
Not sure what you mean by that...how is the total range different from your "need". Macro is supposed to only by applied to the section of cells you have selected before running the macro.
 
Hi Luke


Yes, Its highlighting all the duplicate values and not as per my need request you to check the attached file, rectangle box.


Regards,

Manoj
 
Ah, I think I understand better. How's this?

[pre]
Code: 
Sub ColorDupes()
Dim MyRange As Range

'Before running macro, select cells you want to apply macro to
Set MyRange = Selection

Application.ScreenUpdating = True

For Each c In MyRange
If c = c.Offset(1, 0) Or c = c.Offset(-1, 0) Then
c.Interior.ColorIndex = 3 'Color red
Else
c.Interior.ColorIndex = 0 'No color
End If
Next c

Application.ScreenUpdating = True

End Sub
[/pre]
 
You must log in or register to reply here.
Back
Top