Thread: excel (detecting duplicate data)

  1. #1
    essence of digital xddxogm3's Avatar
    Join Date
    Sep 2003
    Posts
    589

    excel (detecting duplicate data)

    I have an excel workbook with multiple worksheets.
    I want to be able to detect if any duplicate data has been inputed into all of the worksheets.
    If this is detected, I want it to highlight the duplicate cell.
    Is there a way to have this check the data after every entry?
    I was fooling around with conditional formating and the countif function, but it is highlighting the wrong cells and I can not get it to detect data from other worksheets.
    any help would be appreciated.
    thanks.
    "Hence to fight and conquer in all your battles is not supreme excellence;
    supreme excellence consists in breaking the enemy's resistance without fighting."
    Art of War Sun Tzu

  2. #2
    {Jaxom,Imriel,Liam}'s Dad Kennedy's Avatar
    Join Date
    Aug 2006
    Location
    Alabama
    Posts
    1,065
    You might attempt to sort the data after an entry is made, then look for the new entry and check to see if it already exists.

    Depending on how closely you want to watch for duplicate data, you may look into using a database in stead of Excel.

  3. #3
    essence of digital xddxogm3's Avatar
    Join Date
    Sep 2003
    Posts
    589
    Here is what I have done.
    I think I can only do this in VBA Macro Editor.
    Every time I execute the below I get a
    Compile Error:
    Sub or Function not defined
    and it points at the function header to the code below.
    does anyone have any ideas?
    Code:
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    
    For Sheet = 1 To 6
    
        Worksheet(Sheet).Activate
      
        totalRows = ActiveSheet.UsedRange.Rows.Count
        
        For Row1 = totalRows To 2 Step -1
        
            CellValue = Cells(Row1, 7).Value
            
            For Row2 = Row1 - 1 To 2 Step -1
        
                If CellValue = Cells(Row2, 7).Value And CellValue <> "" Then
                
                    Cells(Row2, 7).Color = RGB(255, 0, 0)
                               
                End If
            
            Next Row2
        Next Row1
    
    Next Sheet
    
    End Sub
    Last edited by xddxogm3; 01-09-2007 at 11:51 PM.
    "Hence to fight and conquer in all your battles is not supreme excellence;
    supreme excellence consists in breaking the enemy's resistance without fighting."
    Art of War Sun Tzu

  4. #4
    essence of digital xddxogm3's Avatar
    Join Date
    Sep 2003
    Posts
    589
    here is the new code.
    please someone take a look and tell me what i'm doing wrong thanks.

    it compiles with no errors, but i have a logic problem.
    Code:
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
       
       
        If ActiveCell.Value <> "" Then
       
        Dim Sheet1 As Integer
        Dim totalRows1 As Integer
        Dim Row1 As Integer
        Dim Sheet2 As Integer
        Dim totalRows2 As Integer
        Dim Row2 As Integer
            
        CellValue = ActiveCell.Value
                    
        For Sheet1 = 1 To 6
            
            totalRows1 = Worksheets(Sheet1).UsedRange.Rows.Count
            For Row1 = totalRows1 To 2 Step -1
                     
                For Sheet2 = 1 To 6
                                             
                    totalRows2 = Worksheets(Sheet2).UsedRange.Rows.Count
                        
                    For Row2 = totalRows2 To 2 Step -1
        
                        Worksheets(Sheet2).Cells(Row2, 8).Value = Worksheets(Sheet2).Cells(Row2, 7).Value
                        
                        If CellValue = Worksheets(Sheet2).Cells(Row2, 7).Value Then
                            Worksheets(Sheet2).Cells(Row2, 7).Interior.ColorIndex = 15
                            
                        Else
                            Worksheets(Sheet2).Cells(Row2, 7).Interior.ColorIndex = 40
                        End If
                                                                                
                    Next Row2
                    
                Next Sheet2
                
            Next Row1
        Next Sheet1
        
        End If
    End Sub
    "Hence to fight and conquer in all your battles is not supreme excellence;
    supreme excellence consists in breaking the enemy's resistance without fighting."
    Art of War Sun Tzu

  5. #5
    Hurry Slowly vart's Avatar
    Join Date
    Oct 2006
    Location
    Rishon LeZion, Israel
    Posts
    6,788
    I don't see why do you need 2 loops if the Sheet1 is not used at all

    Could you explain your algorithm in English?
    Like
    1. Take the new entered value
    2. For every sheet in the document
    3. For every line in the sheet
    4. Take value from the column 7
    5. etc
    All problems in computer science can be solved by another level of indirection,
    except for the problem of too many layers of indirection.
    – David J. Wheeler

  6. #6
    Ethernal Noob
    Join Date
    Nov 2001
    Posts
    1,901
    doesn't access have a query function that does that.

  7. #7
    essence of digital xddxogm3's Avatar
    Join Date
    Sep 2003
    Posts
    589
    here is my most recent code with comments
    sorry this project is exclusively for excel.
    i'm not allowed to use access.
    i wish i could use VB, but that isn't an option either.
    basically this is what is going on.
    i have a spreadsheet that has rows of data.
    i'm trying to make sure no duplicates are inputed into the spreadsheet.
    i the subroutin below with a few logic errors in it.
    it searches the sheet for duplicates, and once it does it searches through it again to highlight all duplicates. i want it to do this as i press enter after entering the data that is needed into the worksheet, but it only works if i click on the cell after the data was already entered. does anyone know of an event that triggers when you press enter in a excel cell? i was unable to identify it in the list the vba editor gives me.

    Code:
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
      
        If ActiveCell.Value <> "" Then   'check to make sure cell is not null
       
            Dim Sheet As Integer         'loop counter for sheets
            Dim totalRows As Integer     'counter for total rows
            Dim Row As Integer           'row counter
            Dim dupCount As Integer      'duplicate cell counter
        
            dupCount = 0                 'set to zero
            
            CellValue = ActiveCell.Value  'aquire data in selected cell
                
                
            'nested loop determines any duplicates
            For Sheet = 1 To 6        'for loop to cycle through worksheets
                totalRows = Worksheets(Sheet).UsedRange.Rows.Count  'acquire total rows on current worksheet
                For Row = totalRows To 2 Step -1    'for loop to cycle through rows in the worksheet
                    If CellValue = Worksheets(Sheet).Cells(Row, 7).Value Then 'if duplicate found increment dupCount
                        dupCount = dupCount + 1    'increment dupCount
                    End If
                Next Row
            Next Sheet
                   
            Worksheets(Sheet - 1).Cells(Row + 1, 8).Value = dupCount 'post dupCount for debuging
        
            'nested loop colors all duplicates
            If dupCount > 1 Then
                For Sheet = 1 To 6        'for loop to cycle through worksheets
                    totalRows = Worksheets(Sheet).UsedRange.Rows.Count  'acquire total rows on current worksheet
                    For Row = totalRows To 2 Step -1    'for loop to cycle through rows in the worksheet
                        If CellValue = Worksheets(Sheet).Cells(Row, 7).Value Then 'if duplicate found increment dupCount
                            Worksheets(Sheet).Cells(Row, 7).Interior.ColorIndex = 3 'paint cell red with duplicates found.
                        End If
                    Next Row
                Next Sheet
            End If
        End If
    
    End Sub
    Last edited by xddxogm3; 01-11-2007 at 10:59 PM.
    "Hence to fight and conquer in all your battles is not supreme excellence;
    supreme excellence consists in breaking the enemy's resistance without fighting."
    Art of War Sun Tzu

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Replies: 48
    Last Post: 09-26-2008, 03:45 AM
  2. question about a working linked list
    By cold_dog in forum C++ Programming
    Replies: 23
    Last Post: 09-13-2006, 01:00 AM
  3. Replies: 4
    Last Post: 06-14-2005, 05:45 AM
  4. C diamonds and perls :°)
    By Carlos in forum A Brief History of Cprogramming.com
    Replies: 7
    Last Post: 05-16-2003, 10:19 PM
  5. C Programming Question
    By TK in forum A Brief History of Cprogramming.com
    Replies: 13
    Last Post: 07-04-2002, 07:11 PM