Checking for duplicates - think this is simple

[steve.fay]

Hi. I'm not much an Excel guy, so I'm hoping someone can help me
fairly quickly. Thanks so much in advance!

I have several columns of numbers, say 10 columns of numbers with the
same number of rows (again, let's keep it simple and say 10 rows -
although I need to run this a few times against different data, so
although it will always be 10 columns, it might be 9 rows in one case,
11 rows in another).

So in my 10x10 grid of numbers, I want to see if A1 has any duplicates
in A2-A10. If it does, I want to either to mark A1 with a "*" or I
could also insert a column between A and B and make B1 = "*". But then
I want to also check A2 for any duplicates in A1 and A3-A10. And do
the same for A3 - checking for any duplicates in A1-A2 or A4-A10. Make
sense? I sure hope so.

The problem is, I want to do the same thing for column B, column C,
etc.

So column A could have numbers like:

70 *
73
68 *
70 *
79
68 *
....

Is there any way to do this? I guess I could set up
IF(A1=A2,"*",IF(A1=A3,"*",IF...) but that'd be a nightmare, I think.

Any help would guarantee a Christmas this year!

Thanks again so much!
Steve

 

[Guest]

Well, in the interest of saving Christmas, I think Conditional Formatting
might work best for you here. That way, no need to add extra columns.

Highlight your range of data
Select "Conditional Formatting" from the Format Menu
Change "Cell Value Is" to "Formula Is"
Enter the formula: =COUNTIF(A$1:A$10,A1)>1
Select your formatting preference (i.e. bold, red background, etc..)
Click OK

HTH,
Elkar

 

[Dave Peterson]

How about a formula like to check for any duplicates in that range. (I shifted
things down one row to put this formula in Row 1.)

=IF(COUNT(A2:A100)=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&"")),
"No Duplicates","Some Duplicates")

This checks all that range looking for any duplicates.

If you really want to see if each cell (A1, A2, ..., A10) has any duplicates

put this in B1:
=countif(A:A,a1)
and drag down.

If you see a number different than 1, it has a duplicate.

Or...

=if(countif(a:a,a1)>1,"*","")

if you want that indicator.

 

[Dave Peterson]

You have more replies in .misc.

Hi. I'm not much an Excel guy, so I'm hoping someone can help me
fairly quickly. Thanks so much in advance!

I have several columns of numbers, say 10 columns of numbers with the
same number of rows (again, let's keep it simple and say 10 rows -
although I need to run this a few times against different data, so
although it will always be 10 columns, it might be 9 rows in one case,
11 rows in another).

So in my 10x10 grid of numbers, I want to see if A1 has any duplicates
in A2-A10. If it does, I want to either to mark A1 with a "*" or I
could also insert a column between A and B and make B1 = "*". But then
I want to also check A2 for any duplicates in A1 and A3-A10. And do
the same for A3 - checking for any duplicates in A1-A2 or A4-A10. Make
sense? I sure hope so.

The problem is, I want to do the same thing for column B, column C,
etc.

So column A could have numbers like:

70 *
73
68 *
70 *
79
68 *
...

Is there any way to do this? I guess I could set up
IF(A1=A2,"*",IF(A1=A3,"*",IF...) but that'd be a nightmare, I think.

Any help would guarantee a Christmas this year!

Thanks again so much!
Steve

 

[Dave Peterson]

Oops. I didn't notice that this was crossposted.

Sorry.

 

[Guest]

Steve

I am not sure how easy that is to do with Excel worksheet functions.

This VBA code will work. Simply select your range e.g. A1:G10 and then run
the macro:

Sub FindDuplicates()
Dim cols As Long, rws As Long, i As Long, j As Long
Dim rng As Range
Dim Target As Variant

cols = Selection.Columns.Count
rws = Selection.rows.Count

On Error Resume Next
For j = 1 To cols
For i = 1 To rws
Target = Cells(i, j)
If TypeName(Target) = "Double" Then
If
Application.WorksheetFunction.CountIf(Range(Selection.Cells(1, j), Cells(rws,
j)), Target) > 1 Then
For Each rng In Range(Selection.Cells(1, j), Cells(rws,
j))
If rng = Target Then
rng = rng & " *"
End If
Next rng
End If
End If
Next i
Next j

End Sub

Not the best piece of code, but it does work and avoids nested 'IF'
functions etc.

Regards


Alex

 

[steve.fay]

Dave/Elkar,

Thanks so much for the replies. Dave, I used your simpilest approach,
considering my simple intellectual skills. It seems to work
perfectly - thanks so much!

I just wish I could do this in Access directly. That's where the data
is actually at. I suppose I'll just bring over the different
competitions into different spreadsheets and do this formula for each
individual column. I wish there was an easier way.

Thanks so much - this is great, though!
Steve

 

[steve.fay]

Alex,

I'm not sure if you'll read this or not, but how easy would it be to
add this code to my VB6 program that does my scoring? Would I be able
to add this subprocedure and just run it?

Thanks!
Steve

 

[Dave Peterson]

I don't speak the Access. But you may want to post in one of the Access
newsgroups to see if there's a way to do it there.

 

[Guest]

Steve

Much of VBA / VB6 is similar so I would try it...see what happens.

I am mainly on the VBA side and not VB6 so cannot be confident in my answer
to your question...

Regards


Alex