SORT FILTER Two Columns when either has a criteria

[GeeWhiz7]

Hi,

Based on something Fluff helped me with in this post, I have an original list of items that get put into an array of their total non-recursive combinations.

Combinations without zeros - 2 columns

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

www.mrexcel.com

Fluff helped me to get rid of cases that had zeros in the list as my source data (not fruit) from users occasionally had zeros. Now what I notice in use is there are other bad data that I want to filter out.

See mini-sheet for idea. I have tried SORT(FILTER()), IF(AND()), but can't quite get what I want.

Goal:
Filter either as a new array or in-place, the combinations (cols D&E), to only include the good values of fruit and omit "junk" or "tax credit"

Constraints:

• Needs to be non-vba solution.
• Needs to keep the viable combinations with each other (ie a row with good/good data needs to be sorted to stay together)
• Needs to be able to work on any number of rows, but always just two columns.

Example:

Book3
	A	B	C	D	E	F	G	H
1		List		Combinations			Desired Result
2		Apple		Apple	Orange		Apple	Orange
3		Orange		Apple	Melon		Apple	Melon
4		Melon		Apple	tax credit		Orange	Melon
5		tax credit		Apple	junk
6		junk		Apple	junk
7		junk		Orange	Melon
8				Orange	tax credit
9				Orange	junk
10				Orange	junk
11				Melon	tax credit
12				Melon	junk
13				Melon	junk
14				tax credit	junk
15				tax credit	junk
16				junk	junk
Sheet1

Thank you for your help!

 

[Fluff]

How about

=LET(Rng,FILTER(B2:B50,(B2:B50<>0)*(B2:B50<>"tax credit")*(B2:B50<>"junk")),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))

 

[GeeWhiz7]

How about

=LET(Rng,FILTER(B2:B50,(B2:B50<>0)*(B2:B50<>"tax credit")*(B2:B50<>"junk")),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))

Hi Fluff, this works and I had thought of using your original code, but I am having issues with the number of combinations. Originally I thought a list of 35-55 might work, but with an input list of 50 there are close to 1200 combination rows. So, I had a little bit of a workaround using your code from before and hand editing out/repasting values.

Do you think there is a way to do this without the limitation no entry #s?

 

[Fluff]

The only way I know to do permutations like that is with the Base function, which is limited to 36

 

[GeeWhiz7]

The only way I know to do permutations like that is with the Base function, which is limited to 36

Okay, thanks for looking. If I can figure out how to do it from the original function code in the original post, I will post it here.