Find equal ranges in a list

TheA1cellshouldhaveavalueof4(four),asitisthenumberofitemstocompare(donotputanothervaluehereperhour,seethecommentsbelow)

ThecellD1displaysthenumberofthelastrowwithdatainyourtable(automatically)

TheBcolumnisjustforseparatingthedatavisually.

TheCcolumnhasadefaultsettingforeachsetoffourvaluestobecompared,includingthevalueofthelineitselfandthefollowingthreevalues(adapthereifyouuseanotherquantityofitemstocompare,seethecommentsbelow).

TheDcolumndisplaysthedatalinenumber

TheFcolumndisplayseachoccurrenceintheformyourequested

TheHcolumnshowsaspecificcell

TheIcolumndescribesthefunctionofthiscellordisplaysitsformula

Notes

ColumnDshowsasingleoccurrenceperline,thenextoccurrenceimmediatelyfollowingthecurrentline,withnootheroccurrencesiftheyexist,howeveriftheyexist,thelinewiththesecondoccurrencewillpointtothelineofthethirdoccurrenceandsoon...

Herearetheformulastocopyandpaste:

=MÁXIMO(D2:D99999)=A2&"|"&A3&"|"&A4&"|"&A5

=LIN()

=SE(OU(NÃO(ÉNÚM(INDIRETO("A"&(D2+$A$1-1))));ÉERROS(PROCV(C2;INDIRETO("C"&(D2+1)&":D"&$D$1);2;FALSO)));"";PROCV(C2;INDIRETO("C"&(D2+1)&":D"&$D$1);2;FALSO))

=SE(ÉNÚM(E2);"A"&D2&":A"&(D2+$A$1-1)&" = A"&E2&":A"&(E2+$A$1-1);"")

Do the same test for a spreadsheet with a lot of data, I have spreadsheets that are much more complex than this one and with numerous data (rows and columns) and numerous formulas and there is no waiting or delay for the processing of each new data inserted active automatic calculation), it may be the same for you.

I do not think it's trivial (maybe even possible) to do this directly with Excel formulas since it involves an interactive search. So I think the best way is even using VBA.

An example function that does this is as follows:

Function findEqual(ByRef oRange As Range, ByVal iComparingSize As Integer) As String

    ' Só funciona com uma só coluna no range
    If oRange.Columns.Count <> 1 Then
         findEqual = CVErr(xlErrValue)
         Exit Function
    End If

    Dim iComp1 As Integer, iComp2 As Integer
    Dim i As Integer
    Dim bEqual As Boolean

    ' "Ponteiro" 1: varre do começo ao fim do intervalo dado
    For iComp1 = 1 To (oRange.Rows().Count - iComparingSize)

        ' "Ponteiro" 2: varre da linha seguinte à do ponteiro 1 ao fim do intervalo dado
        For iComp2 = iComp1 + 1 To (oRange.Rows().Count - iComparingSize)

            ' Compara os n valores seguintes a cada "ponteiro"
            bEqual = True
            For i = 0 To iComparingSize - 1
                If oRange.Cells(iComp1 + i).Value <> oRange.Cells(iComp2 + i).Value Then
                    bEqual = False
                    Exit For
                End If
            Next

            ' Se forem todos iguais, encontrou o que buscava!
            ' Logo, retorna uma string com os endereços dos dois intervalos comparados
            If bEqual Then
                findEqual = oRange.Cells(iComp1).Address() + ":" + oRange.Cells(iComp1 + iComparingSize - 1).Address() + " = "
                findEqual = findEqual + oRange.Cells(iComp2).Address() + ":" + oRange.Cells(iComp2 + iComparingSize - 1).Address()
                Exit Function
            End If

        Next

    Next

    ' Se chegou aqui, não encontrou nada igual
    findEqual = "Não há intervalos iguais!"

End Function

To use this function, place it in a new module inside the spreadsheet code, and call it in a cell passing as the first parameter the interval with the data to be parsed (in its example, A2:A17 ) and as the second parameter, the size of the search window (that is, how many elements need to be equal - in its example, 4 ). For example, put in the cell H10 the following:

=findEqual(A2:A17;4)

It produces the expected result:

Thefunctionworkswiththreeloops.Thefirsttwoarelike"pointers" that mark the beginning of the regions being compared, and the third (more internal) loop is what actually compares.

Note that although this algorithm works, it tends to perform poorly for very long lists. In addition, this sample function returns a string for display, but a function that returns an array with the two found regions may be more useful to you. I do not know, it will depend on its use. In this case, colleague's comment @Thalles does the whole thing. sense: it may be important for you to rethink or rediscuss your need / problem from scratch.