Back coloring an entire row if a duplicate is found in a dynamic range
Back coloring an entire row if a duplicate is found in a dynamic range
(OP)
I need to determine if the current row (iRow), column A, is duplicated anywhere in the range of A11:A & Range("E7").Value.
E7 will contain a value that will indicate the end of the range. This could realistically be from 11 to maybe 10,000. It will change every time.
The SetGLRRowColor() sub creates the desired back color and is working fine.
So to reiterate, I need to replace my 1=1 with some logic to determine if the value in column A in that row (iRow), is a duplicate anywhere in the range. I can't work with just a true/false result that duplicates exist, I need a true/false result and the line number, one row at a time.
Here is my code:
E7 will contain a value that will indicate the end of the range. This could realistically be from 11 to maybe 10,000. It will change every time.
The SetGLRRowColor() sub creates the desired back color and is working fine.
So to reiterate, I need to replace my 1=1 with some logic to determine if the value in column A in that row (iRow), is a duplicate anywhere in the range. I can't work with just a true/false result that duplicates exist, I need a true/false result and the line number, one row at a time.
Here is my code:
CODE
'check for duplicate customer numbers
For iRow = 11 To Range("E7").Value
If iRow = 11 Then
'do nothing, duplicates not possible on first row
Else
If 1 = 1 Then 'need code here
Call SetGLRRowColor("YES", "Duplicate Account", iRow)
Else
Call SetGLRRowColor("NO", "", iRow)
End If
End If
Next iRow
Macola and SAP Business One Consultant
Check out our Macola tools:
www.gainfocus.biz/exceladdin.html
RE: Back coloring an entire row if a duplicate is found in a dynamic range
RE: Back coloring an entire row if a duplicate is found in a dynamic range
Macola and SAP Business One Consultant
Check out our Macola tools:
www.gainfocus.biz/exceladdin.html
RE: Back coloring an entire row if a duplicate is found in a dynamic range
CODE -->
RE: Back coloring an entire row if a duplicate is found in a dynamic range
Will that highlight the entire row? Or just the cell?
I need the entire row highlighted. I have the code to do that with my SetGLRRowColor() subroutine. But I do not know how to return a true/false on duplicates and the line number.
Any ideas?
Macola and SAP Business One Consultant
Check out our Macola tools:
www.gainfocus.biz/exceladdin.html
RE: Back coloring an entire row if a duplicate is found in a dynamic range
RE: Back coloring an entire row if a duplicate is found in a dynamic range
Is the data in column A sorted? If yes, then you can build a single loop and test if value in row i equals to values in row i-1 or i+1, if yes - this row has duplicates, otherwise no.
If the data is not sorted, I would pick the data in range to variant array (v=Range("A11:A" & Range("E7")) create helper array with duplicate marks (double loop required to test: For i=1 to N and, inside, For j=i+1 to N. Skip marked as duplicated items found in loop with i), and finally loop through the helper array and to format corresponding rows.
combo
RE: Back coloring an entire row if a duplicate is found in a dynamic range
The best way to prevent duplicates is to not allowed them to happen in the first place.
Just my opinion.
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: Back coloring an entire row if a duplicate is found in a dynamic range
No, just the cell - but, as I say, it was just a simple example. here's a fractionally more complicated version that does entire row ...
CODE -->
Sub Example2() Dim CondFormat As FormatConditions Set CondFormat = Range("$A$11:$C" & Range("E7").Value).EntireRow.FormatConditions With CondFormat If .Count > 0 Then .Delete ' clean up existing conditional formatting applied to range .Add xlExpression, , "=COUNTIF($A$11:$A$" & Range("E7") & ",$A11)>1" .Item(1).Interior.Color = RGB(255, 255, 0) End With End Sub
RE: Back coloring an entire row if a duplicate is found in a dynamic range
FWIW here was my solution:
CODE
Macola and SAP Business One Consultant
Check out our Macola tools:
www.gainfocus.biz/exceladdin.html
RE: Back coloring an entire row if a duplicate is found in a dynamic range
CODE
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: Back coloring an entire row if a duplicate is found in a dynamic range
Couple of questions/thoughts
1) You are calling
Call SetGLRRowColor("NO", "Duplicate Old Account", 11)
for every duplicate found. By which I mean if you had
1
1
then it would be called twice. And in the extreme case that 10000 (your top level estimate) all happened to be duplicates, this would be called 10000 times. Is that really what you want to do?
2) And
'do nothing, duplicates not possible on first row
Well, see previous question.
Call SetGLRRowColor("NO", "Duplicate Old Account", 11)
is doing the same thing as
Call SetGLRRowColor("NO", "Duplicate Old Account", iRow)
if iRow was 11.
3) Have you checked performance with 10000 rows? You are iterating each line ...
Just some food for thought
RE: Back coloring an entire row if a duplicate is found in a dynamic range
And yes, I want to check all 10,000 lines. A simple "there are duplicates" message does not help.
Macola and SAP Business One Consultant
Check out our Macola tools:
www.gainfocus.biz/exceladdin.html
RE: Back coloring an entire row if a duplicate is found in a dynamic range
yes, I understood that (and the potential conditional formatting solution does that without a VBA loop). It was setting the exact same line 10000 times that seemed off
RE: Back coloring an entire row if a duplicate is found in a dynamic range
CODE -->
combo
RE: Back coloring an entire row if a duplicate is found in a dynamic range
RE: Back coloring an entire row if a duplicate is found in a dynamic range
Macola and SAP Business One Consultant
Check out our Macola tools:
www.gainfocus.biz/exceladdin.html
RE: Back coloring an entire row if a duplicate is found in a dynamic range
RE: Back coloring an entire row if a duplicate is found in a dynamic range
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!