Combinations without zeros - 2 columns

[GeeWhiz7]

Hi folks,

I have been using a neat bit of code I found on Mr. Excel to take two columns of information and return an array of the possible, non-recursive combinations.
The code is below.

Below that is a mini-sheet to help what I am trying to get to.

I would like to have only the array with combinations that don't have a zero in either list like the mini-sheet illustrates.
Unfortunately because the original list comes from other users workbooks and get dynamically fed into a master list which sometimes contains zeros or blanks, I can't easily modify the original list represented in A column example.

I can't seem to figure out how to modify the original VBA to do this, but maybe you will know.
Alternatively, I have tried SEQUENCE, FILTER and some other options, but nothing that quite hits the mark.

Thank you

Public result() As Variant
 
Function Combinations(rng As Range, n As Single)
Dim b As Single
 
rng1 = rng.Value
b = WorksheetFunction.Combin(UBound(rng1, 1), n)
 
ReDim result(b, n - 1)
Call Recursive(rng1, n, 1, 0, 0)
 
For g = 0 To UBound(result, 2)
     result(UBound(result, 1), g) = ""
Next g
 
Combinations = result
 
End Function


Function Recursive(r As Variant, c As Single, d As Single, e As Single, h As Single)
Dim f As Single
 
For f = d To UBound(r, 1)
     
        result(h, e) = r(f, 1)
     
        If e = (c - 1) Then
                 
            For g = 0 To UBound(result, 2)
                result(h + 1, g) = result(h, g)
            Next g
            h = h + 1
        Else
            Call Recursive(r, c, f + 1, e + 1, h)
        End If
     
Next f
     
End Functio

n

Book3.xlsb
	A	B	C	D	E	F	G
1	User	2-item combinations no repeats				Desired Result
2	Input
3
4	List	1	2		Seq.	1	2
5	Apple	Apple	Orange			Apple	Orange
6	Orange	Apple	Banana			Apple	Banana
7	Banana	Apple	0			Orange	Banana
8	0	Apple	0
9	0	Orange	Banana
10		Orange	0
11		Orange	0
12		Banana	0
13		Banana	0
14		0	0
Sheet1

Cell Formulas
Range	Formula
B5:C15	B5	=Combinations(A5:A9,2)
Dynamic array formulas.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1	User	2-item combinations no repeats
2	Input
3
4	List	1	2
5	Apple	Apple	Orange
6	Orange	Apple	Banana
7	Banana	Orange	Banana
8	0
9	0
Data

Cell Formulas
Range	Formula
B5:C7	B5	=LET(Rng,FILTER(A5:A9,A5:A9<>0),r,ROWS(Rng),b,BASE(SEQUENCE(r^2,,0),r,2),f,FILTER(b,LEFT(b)<>RIGHT(b)),m,MID(f,{1,2},1)+1,by,BYROW(m,LAMBDA(br,CONCAT(SORT(br,,,1)))),rr,ROWS(by),FILTER(INDEX(Rng,m),MMULT((SEQUENCE(rr)>=SEQUENCE(,rr))*(by=TRANSPOSE(by)),SEQUENCE(rr,,,0))=1))
Dynamic array formulas.

 

[GeeWhiz7]

How about

Fluff.xlsm
	A	B	C
1	User	2-item combinations no repeats
2	Input
3
4	List	1	2
5	Apple	Apple	Orange
6	Orange	Apple	Banana
7	Banana	Orange	Banana
8	0
9	0
Data

Cell Formulas
Range	Formula
B5:C7	B5	=LET(Rng,FILTER(A5:A9,A5:A9<>0),r,ROWS(Rng),b,BASE(SEQUENCE(r^2,,0),r,2),f,FILTER(b,LEFT(b)<>RIGHT(b)),m,MID(f,{1,2},1)+1,by,BYROW(m,LAMBDA(br,CONCAT(SORT(br,,,1)))),rr,ROWS(by),FILTER(INDEX(Rng,m),MMULT((SEQUENCE(rr)>=SEQUENCE(,rr))*(by=TRANSPOSE(by)),SEQUENCE(rr,,,0))=1))
Dynamic array formulas.

Wow Fluff, this works perfectly. I suspect I may never understand the totality of the code itself as LET and LAMBDA are new to me. Thank you for this help!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[GeeWhiz7]

Wow Fluff, this works perfectly. I suspect I may never understand the totality of the code itself as LET and LAMBDA are new to me. Thank you for this help!

You're welcome & thanks for the feedbac

Hi Fluff, wondering if I can ask you another related question. I notice when I try to increase the Rng size past a point then the whole return array comes back as #Value. I was wondering if there was a limitation I should be considering.

 

[Fluff]

How many rows (without 0s) are you talking about?

 

[GeeWhiz7]

How many rows (without 0s) are you talking about?

Around 35-55 or so.

 

[GeeWhiz7]

Around 35-55 or so.

I think I have a solution...if I only increase the range one row at a time and push enter then it works so I can just build it up that way. Not sure why it doesn't do more in one go, but at least it works. Thanks again for your help.

 

[Fluff]

That is a problem, the base function can only handle up to 36, although that will need a tweek to the code for anything above 10

 

[Fluff]

This will handle upto 36 rows of values

=LET(Rng,FILTER(A5:A50,A5:A50<>0),r,ROWS(Rng),b,BASE(SEQUENCE(r^2,,0),r,2),f,FILTER(b,LEFT(b)<>RIGHT(b)),m,MID(f,{1,2},1),mm,IF(CODE(m)>64,CODE(m)-54,m+1),by,BYROW(m,LAMBDA(br,CONCAT(SORT(br,,,1)))),rr,ROWS(by),FILTER(INDEX(Rng,mm),MMULT((SEQUENCE(rr)>=SEQUENCE(,rr))*(by=TRANSPOSE(by)),SEQUENCE(rr,,,0))=1))