Find and highlite all double in Grid

In your query that calls this data....do you have a unique Identifier for Each Row?

I built a sample of this data and run with the following query...

SELECT *,
( SELECT COUNT(*) FROM CARRIERS B WHERE B.CARRIER_ID = A.CARRIER_ID AND B.ORIGIN01 = A.ORIGIN01 AND B.DESTINATION01 = A.DESTINATION01 ) AS DUPE
  FROM CARRIERS A

use the DUPE column to indicate more than 1...

if you have an identifier then...

SELECT *,
( SELECT COUNT(*)
    FROM CARRIERS B
   WHERE B.CARRIER_ID = A.CARRIER_ID
     AND B.ORIGIN01 = A.ORIGIN01
     AND B.DESTINATION01 = A.DESTINATION01
     AND B.RATE_ID < A.RATE_ID) AS DUPE
  FROM CARRIERS A

Then highlight only rows where DUPE = 1

wow, ok, i will try this now. Does it have to be sorted first in the data base?

What i am using at this moment to import in my GRID is this:

Dim xlObject     As Excel.Application
Dim xlWB         As Excel.Workbook
Dim NoOfRows     As Long
Dim NoOfColumns  As Long

    On Error GoTo MyErrHandler
   
    With CommonDialog1
        .CancelError = True
        .Filter = "Microsoft Excel files (xlam, xlsx, xltm, xlt, xlsm, xltx, xls, txt, csv)"
        .InitDir = "C:\Documents and Settings\all users\Desktop"
        .ShowOpen
        If Not .FileName = "" Then
            Set xlObject = New Excel.Application
            Set xlWB = xlObject.Workbooks.Open(.FileName)

            Clipboard.Clear
            xlObject.Cells.Copy     ' Copy all cells in active worksheet.
            FetchNoRowCol xlObject.ActiveWorkbook.ActiveSheet, NoOfRows, NoOfColumns
            With MSHFlexGrid1
               .Redraw = False     'Dont draw until the end, so we avoid that flash
               .Rows = NoOfRows
               .Cols = NoOfColumns
               .Row = 0            'Paste from first cell
               .Col = 0
               .RowSel = .Rows - 1 'Select maximum allowed (your selection shouldnt be greater than this)
               .ColSel = .Cols - 1
               .Clip = Replace(Clipboard.GetText, vbNewLine, vbCr) 'Replace carriage return with the correct one
               .Col = 1            'Just to remove that blue selection from Flexgrid
               .Redraw = True      'Now draw
            End With
            xlObject.DisplayAlerts = False 'To avoid "Save woorkbook" messagebox
            xlWB.Close
            xlObject.Application.Quit
            Set xlWB = Nothing
            Set xlObject = Nothing
        End If
    End With
    
    Dim r As Long, txt As String, total As Long
For r = 1 To MSHFlexGrid1.Rows - 1
  If Len(MSHFlexGrid1.TextMatrix(r, 3)) Then total = total + 1

Next r
lblTotalrecord = CStr(total)

   
    Exit Sub

MyErrHandler:
    Err.Clear

Select allOpen in new window

So your grid does not come from a database but rather an excel sheet
are you able to make changes to your excel sheet?
if so ...add a column that checks for the dupes...
if you have a sample of it I could show you how...

Yes and NO.

Yes i could do something in the excel sheet but since this excel sheet comes from somebody else, i want my VB6 tool to fix it.

Once fix, i have a button to export in Excel to send back to the requester.

if the data in the excel is not sorted...
then do an sort with the excel object
add a column to the excel object
and run through the rows...adding the DUPE value when the previous columns match...

Not sure to fully understand.

Do i need to sort in the excel sheet or in the grid?

If you can sort in the sheet that would be preferable...
but if you can not control that then sort in the grid

Hello again,

Ok, now i did the filter:

Private Sub MSHFlexGrid1_DblClick()
  With MSHFlexGrid1
        .ColSel = 8
        .Sort = flexSortStringAscending
        .ColSel = 6
        .Sort = flexSortStringAscending
         .ColSel = 3
        .Sort = flexSortStringAscending
    End With
End Sub

Select allOpen in new window

What do i need to do know that i highlight all duplicate?

Wilder:
I am building a test project for you...
what is in your process "FetchNoRowCol" ?

What do you meen by FetchNoRowCol?

Not sure to understand.

Well from your code above you are calling this function...not important now..I made a workaround

Oh ok,

Thanks

In your excel sheet...
Are the 3 columns you gauge against always in the same position?

Yes, the 3 columns are always in the same position.

Thanks, i will try this now and let you know.

Thanks again for your help.

I have a variable not define on:

Set xlSH = Nothing

I left that in there by mistake...I was experimenting with a sheet object...\
just delete that line of code
:)

Ok,

I will do some test cause i have a file with 2 duplicate and it only highlight one of them when i use this code.

Probably just a small adjustment.

Wow, this is great.

Thanks

I can only do up to 7500 rows.

But i will manage.

I guess this is the max.

Thanks again