Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for duplicates: VBA
Hi
Does anyone know a neat VBA way of checking for duplicates in a column. I use OFFSET to get to a particular cell but then need to check whether the one (or more) below it contain identical data (numeric)... This is a scoring system and I'm trying to check for ties. I OFFSET to 7th place which shows 7 in the cell but then need to check the next cell down to see if that is 7 (and maybe the next one too). Hope that makes sense. Any suggestions gratefully received! Jim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for duplicates: VBA
I use the COUNTIF worksheet function - e.g.
CountTies = WorksheetFunction.Countif(YourCell.EntireColumn,Yo urCell.Value) If this comes out 1, then there are that many "tied" scores for that value. "Jim" wrote: Hi Does anyone know a neat VBA way of checking for duplicates in a column. I use OFFSET to get to a particular cell but then need to check whether the one (or more) below it contain identical data (numeric)... This is a scoring system and I'm trying to check for ties. I OFFSET to 7th place which shows 7 in the cell but then need to check the next cell down to see if that is 7 (and maybe the next one too). Hope that makes sense. Any suggestions gratefully received! Jim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for duplicates: VBA
With ActiveCell If .Offset(7,0).Value = .Offset(8,0).Value Then Msgbox "same" End If End With With ActiveCell If .Offset(7,0).Value = .Offset(8,0).Value And _ .Offset(7,0).Value = .Offset(9,0).Value Then Msgbox "same" End If End With -- HTH RP (remove nothere from the email address if mailing direct) "Jim" wrote in message ... Hi Does anyone know a neat VBA way of checking for duplicates in a column. I use OFFSET to get to a particular cell but then need to check whether the one (or more) below it contain identical data (numeric)... This is a scoring system and I'm trying to check for ties. I OFFSET to 7th place which shows 7 in the cell but then need to check the next cell down to see if that is 7 (and maybe the next one too). Hope that makes sense. Any suggestions gratefully received! Jim |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for duplicates: VBA
Jim - Here is a macro I use to run down a column and check for duplicate
numbers. If a set of duplicates are found, the first one is changed to "AAA" and the second is colored red. A formula is then inserted at the bottom to count how many "AAA" cells - or how many duplicates - were found. Righ now, it's hard-coded to count down Col. C and put the formula at the bottom of Col. D. HTH Ed Sub FindDups() ' ' FindDups Macro Dim FirstItem As String, SecondItem As String Dim Offsetcount As Long ' Select column Range("C1").Select ScreenUpdating = False ' Run down column and compare TIR numbers FirstItem = ActiveCell.Value SecondItem = ActiveCell.Offset(1, 0).Value Offsetcount = 1 ' If the numbers are the same Do While ActiveCell < "" If FirstItem = SecondItem Then ' Turns the second one red ActiveCell.Offset(Offsetcount, 0).Interior.Color = RGB(255, 0, 0) ' Replaces first cell value with AAA ActiveCell.FormulaR1C1 = "AAA" Offsetcount = Offsetcount + 1 SecondItem = ActiveCell.Offset(Offsetcount, 0).Value Else ActiveCell.Offset(Offsetcount, 0).Select FirstItem = ActiveCell.Value SecondItem = ActiveCell.Offset(1, 0).Value Offsetcount = 1 End If Loop ScreenUpdating = True 'Puts formula to count AAA values in D column ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "=COUNTIF(C:C,AAA)" ActiveCell.Select ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],""AAA"")" ActiveCell.Select Range("C65536").End(xlUp).Offset(1, 1).Select End Sub "Jim" wrote in message ... Hi Does anyone know a neat VBA way of checking for duplicates in a column. I use OFFSET to get to a particular cell but then need to check whether the one (or more) below it contain identical data (numeric)... This is a scoring system and I'm trying to check for ties. I OFFSET to 7th place which shows 7 in the cell but then need to check the next cell down to see if that is 7 (and maybe the next one too). Hope that makes sense. Any suggestions gratefully received! Jim |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for duplicates: VBA
Many thanks for the quick replies. I think Bob's or Ed's suggestions will
bear fruit. Thanks again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking for Duplicates within a Workbook | Excel Worksheet Functions | |||
checking for duplicates | Excel Worksheet Functions | |||
Checking for duplicates - think this is simple | Excel Discussion (Misc queries) | |||
Checking entire row for duplicates | Excel Programming | |||
Checking for duplicates? | Excel Programming |