Combination with no repeat in Excel including reducing rows

Question

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?

Just to get an idea of the number of combinations involved, is your dataset fixed at six items, as per your example, or could it be potentially more? If so, how much more? — Jos Woolley, Oct 12 '22 at 06:33

score 1 · Answer 1 · answered Oct 12 '22 at 07:21

1

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),β)))))))

answered Oct 12 '22 at 07:21

Jos Woolley

8,564
2
4
9

Thank you! May you explain your idea? – Aria Oct 12 '22 at 08:02
`SEQUENCE(5556,,1111)` generates an array of integers from 1111 to 6666 inclusive. This array is then reduced such that it comprises the 15 unique combinations of four items from the set of integers from 1-6. This reduced array is then used to index the range `A1:A6`. – Jos Woolley Oct 12 '22 at 08:15

FaneDuru · Answer 2 · 2022-10-12T10:03:19.600

1

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...

edited Oct 12 '22 at 10:03

answered Oct 12 '22 at 09:55

FaneDuru

38,298
4
19
27

I upvoted it but then noticed it only deleted one row at a time rather than 2 so may need a mod? – Tom Sharpe Oct 12 '22 at 10:30
@Tom Sharpe Yes, but the range to be processed starts **from the second row**, so the first row is also removed... :) Try checking the result against the requirement. I observed that it can be removed in that (simple) way. – FaneDuru Oct 12 '22 at 10:59
OP does go on to say ...[delete] second and third line, second and fourth and so on... so I guess they do mean all 15 combinations as suggested by @Jos Woolley, some of which do include the first line. – Tom Sharpe Oct 12 '22 at 12:17
@Tom Sharpe He exactly said **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**. So, first is deleted for all cases. You can see that **in his/her example** (of the first three processing results), where the above logic/understanding looks to be confirmed (I think...). I also think that OP should test the code and confirm (or infirm) that the code does what (I understood that) he/she wishes... – FaneDuru Oct 12 '22 at 12:25
Yes OP needs to express an opinion - I have no more to add. – Tom Sharpe Oct 12 '22 at 12:26
@Tom Sharpe I could see the question, I (think I) understood it in the way I tried explaining and I had a solution in mind. I played a little with VBA to put it in practice. I cannot even imagine why OP needs that, I only try following the question in words and his examples about what he wants, which looks to completely cover my understanding. But. after our discussion, I cannot be sure (anymore) that he doesn't need something else and couldn't accurately explain it... – FaneDuru Oct 12 '22 at 12:43
@Tom Sharpes solution is the output which I have in mind – Aria Oct 14 '22 at 12:34
@Aria Then, you did not know how to clearly explain it... – FaneDuru Oct 14 '22 at 12:50

score 1 · Answer 3 · answered Oct 12 '22 at 20:43

I've been playing around with this for a while and wondered if I could take OP's instructions literally and exclude the first and second, first and third rows etc. from the full set of 90 rows to leave 60 rows, then reshape the array into 15 sets of four:

=LET(n,6,r,4,C,COMBIN(n,r),range,A2:A7,
rows,SEQUENCE(n*C,1,0),
pairs,SEQUENCE(n*n,1,0),
filterPairs,FILTER(pairs,MOD(pairs,n)>QUOTIENT(pairs,n)),
filter,INDEX(filterPairs,QUOTIENT(rows,n)+1),
filterRows,FILTER(rows,(MOD(rows,n)<>QUOTIENT(filter,n))*(MOD(rows,n)<>MOD(filter,n))),
select,MOD(filterRows,n)+1,
WRAPCOLS(INDEX(range,select),r))

Combination with no repeat in Excel including reducing rows

3 Answers3