How to generate all possible combinations from a list without having duplicates?

I am trying to create an automatically generated list with every possible combination.

Example of generated list:

This is the current formula I am using:

=INDEX(SPLIT(TOCOL(TOCOL(TOCOL(TOCOL(TOCOL(
TOCOL(H2:H10, 1) &"×"&
TOROW(H2:H10, 1))&"×"&
TOROW(H2:H10, 1))&"×"&
TOROW(H2:H10, 1))&"×"&
TOROW(I2:I4, 1))&"×"&
TOROW(J2:J3, 1)),"×"))

However, this creates duplicate groupings of the fruits. These are all the same but presented in a different order:

• Apple, Apple, Apple, Banana
• Apple, Apple, Banana, Apple
• Apple, Banana, Apple, Apple
• Banana, Apple, Apple, Apple

I would like just one instance of 3 apples and a banana. How do I go about to fix this?

Try this Google Sheets formula. It is based on the OP.

=INDEX(SPLIT(TOCOL(TOCOL(TOCOL(
TOROW(SORT(UNIQUE(BYROW(BYROW(INDEX(SPLIT(TOCOL(TOCOL(TOCOL(
TOCOL(H2:H10, 1) &"×"&
TOROW(H2:H10, 1))&"×"&
TOROW(H2:H10, 1))&"×"&
TOROW(H2:H10, 1)),"×")),LAMBDA(ROW,transpose(sort(Transpose(ROW),1,true)))),LAMBDA(ROW,join("×",ROW)))),1,true), 1))&"×"&
TOROW(I2:I4, 1))&"×"&
TOROW(J2:J3, 1)),"×"))

This formula creates a data list of 2970 rows, compared to the 39366 rows (including duplicates) generated by the original formula in the question.

Logic

The formula consists of several stages:

1. Modify the original formula to return only the combinations of Column H (Fruit). This returns 6561 rows of data.

INDEX(SPLIT(TOCOL(TOCOL(TOCOL(
TOCOL(H2:H10, 1) &"×"&
TOROW(H2:H10, 1))&"×"&
TOROW(H2:H10, 1))&"×"&
TOROW(H2:H10, 1)),"×"))

2. Sort each "fruit" row alphabetically.

BYROW(INDEX(SPLIT(TOCOL(TOCOL(TOCOL(
TOCOL(H2:H10, 1) &"×"&
TOROW(H2:H10, 1))&"×"&
TOROW(H2:H10, 1))&"×"&
TOROW(H2:H10, 1)),"×")),LAMBDA(ROW,transpose(sort(Transpose(row),1,true))))

Converts this extract:

to this:

3. Join the columns in preparation for getting unique values.

BYROW(BYROW(INDEX(SPLIT(TOCOL(TOCOL(TOCOL(
TOCOL(H2:H10, 1) &"×"&
TOROW(H2:H10, 1))&"×"&
TOROW(H2:H10, 1))&"×"&
TOROW(H2:H10, 1)),"×")),LAMBDA(ROW,transpose(sort(Transpose(row),1,true)))),LAMBDA(ROW,join("×",ROW)))

Sample outcome

4. Get the unique values (sorted)

The number of unique combinations = 495 (compared to 6561-including duplicates).

sort(unique(BYROW(BYROW(INDEX(SPLIT(TOCOL(TOCOL(TOCOL(
TOCOL(H2:H10, 1) &"×"&
TOROW(H2:H10, 1))&"×"&
TOROW(H2:H10, 1))&"×"&
TOROW(H2:H10, 1)),"×")),LAMBDA(ROW,transpose(sort(Transpose(row),1,true)))),LAMBDA(ROW,join("×",ROW)))),1,true)

5. Split the sorted Unique values

index(split(sort(unique(BYROW(BYROW(INDEX(SPLIT(TOCOL(TOCOL(TOCOL(
TOCOL(H2:H10, 1) &"×"&
TOROW(H2:H10, 1))&"×"&
TOROW(H2:H10, 1))&"×"&
TOROW(H2:H10, 1)),"×")),LAMBDA(ROW,transpose(sort(Transpose(row),1,true)))),LAMBDA(ROW,join("×",ROW)))),1,true),"×"))

6. Add M1 and N1 values to the unique values

Sample extract