Combination with no repeat in Excel including reducing rows-CodePudding

My dataset looks like this

AAAA
BBBB
CCCC
DDDD
EEEE
FFFF

I want to delete the first and second row and then I want to delete the first and third line and then the first and fourth and so on. Next, second and third line, second and fourth line and so on.

CCCC   BBBB   BBBB
DDDD   DDDD   CCCC
EEEE   EEEE   EEEE
FFFF   FFFF   FFFF

Does anyone have an idea how I can implement it in Excel?

CodePudding user response：

Much more suited to VBA, though given that your dataset is limited to six items this can also be achieved with Office 365 formulas:

=LET(ζ,A1:A6,ξ,SEQUENCE(5556,,1111),λ,FILTER(ξ,MMULT(N(ISNUMBER(FIND(SEQUENCE(,6),ξ))),SEQUENCE(6,,,0))=4),TRANSPOSE(INDEX(ζ,UNIQUE(MAKEARRAY(ROWS(λ),4,LAMBDA(α,β,SMALL(0 MID(INDEX(λ,α),SEQUENCE(,4),1),β)))))))

CodePudding user response：

Please, test the next code. It will process the range you show, located in "A1:A" & x and returns starting from "C2":

Sub RemoveIncrementedRows()
  Dim arr, arrRes, i As Long, k As Long, c As Long, lastR As Long
  
  'the range to be processed should be in "A1:A"
  lastR = Range("A" & rows.count).End(xlUp).row
  arr = Range("A2:A" & lastR).Value2 'place the range in an array for faster iteration
  ReDim arrRes(1 To UBound(arr) - 1, 1 To UBound(arr) - 1) 'ReDim the result array
  For k = 1 To UBound(arrRes)
    For i = 1 To UBound(arr)
           If i <> k Then
               c = c   1
               arrRes(c, k) = arr(i, 1)
          End If
    Next i
    c = 0 'reinitialize the variable to keep the result array rows
  Next k
  'drop the result array content at once (in "C2")
  Range("C2").Resize(UBound(arrRes), UBound(arrRes, 2)).Value2 = arrRes
End Sub

It is able to process more values situated in "A:A", calculating the last row.

I suppose that "AAAA", "BBBB", etc. are there only like examples and you will use different strings. Otherwise, an array containing that strings could also be automatically built...