Finding rows that contain the same values as each other between two columns

Question

Each row in my workbook is a building ID. Each column in my workbook is an item ID.

The cell at the intersection of each item and each building contains a number. This number indicates the quantity of that item in that building. If the building does not have the item, a zero is placed in the intersecting cell.

I need to know which buildings are identical in inventory, that is, which buildings have the same items and the same quantities of said items.

To do this I wrote a script that copies the entire range of intersections for each building to the clipboard, and then pastes it into a cell I refer to as a DNA cell. This DNA cell is just a long, concatenated string of all the intersection numbers.

At the end of the process I would examine which ones had duplicate DNA strings and go from there.

I got it to work, however it is extremely slow, even with screen updating off. Granted I am doing a worksheet with 214 columns and 150 rows, but I still feel like it should go faster.

Is there a better way to accomplish what I want? Basically I need to, for each row, find out if the values in all the cells between D and the last column in that row are identical to any other row.

So if row 75 and row 96 both had the same values in every column between D and the last column, they would be considered "alike" and I would flag them.

My code is below:

        ' start excel application 
    oXL = New Excel.Application
    oXL.Visible = True
    ' Get a new workbook.

    oXL.Application.ScreenUpdating = False
    oWB = oXL.Workbooks.Add


    oSheet = oWB.ActiveSheet

    oWB.Worksheets(1).range("a1").select()
    oWB.Worksheets(1).Paste()

    oSheet.UsedRange.Copy()
    oWB.Worksheets.Add()
    Dim transposeSheet As Worksheet

    oWB.Worksheets("Sheet2").range("a1").select()
    oWB.Worksheets("Sheet2").range("a1").PasteSpecial(Transpose:=True)

    transposeSheet = oWB.ActiveSheet

    With transposeSheet




        .Columns("c:c").Insert(Microsoft.Office.Interop.Excel.Constants.xlRight)
    End With

    Dim lastRow As Long
    Dim lastCol As String
    Dim xlCells As Excel.Range = transposeSheet.Cells
    Dim xlTempRange As Excel.Range = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
    lastRow = xlTempRange.Row
    lastCol = xlTempRange.Column
    Dim columnLetter As String = ColumnIndexToColumnLetter(lastCol)
    lastCol = columnLetter

    Dim DNAholder As String
    Dim c As Range

    For Each c In transposeSheet.Range("c6:c" & lastRow)

        isLooping = True

        If stopPlz = True Then

            stopPlz = False
            isLooping = False
            oXL.Application.ScreenUpdating = True
            Exit For
        End If

        transposeSheet.Range("d" & c.Row & ":" & lastCol & c.Row).Copy()
        DNAholder = Clipboard.GetText
        c.Value = DNAholder

    Next c

    isLooping = False
    oXL.Application.ScreenUpdating = True
End Sub

What do you NEED to do with the results of this. Do you need it in the spreadsheet or something else? The best way is to work with this like a database table, not through interop (nor using the clipboard). Try something like this: http://stackoverflow.com/questions/14261655/best-fastest-way-to-read-an-excel-sheet-into-a-datatable — Steve, Jan 06 '16 at 15:10
Basically I am going to end up displaying to the user two things: Groups of buildings that have the exact same products and same quantity of each product, and buildings that just have the same products. — user47368, Jan 06 '16 at 15:26

Finding rows that contain the same values as each other between two columns

0 Answers0