Creating unique three-number combinations from a single list of numbers ending in 5 in Google Sheets

Other Questions Google Sheets
1

I’m trying to make a big list of numbers that end in 5, maybe up to 1000 or 10000. Then I want to create as many three-number combinations as possible from this list. Each combo should have different numbers.

For example:

  • 15, 335, 795
  • 55, 275, 615

It’s okay if a number shows up in different combos, but not more than once in the same combo.

I’m using Google Sheets for this. Right now, I can make combos from three separate lists, but that gives me unwanted results like 25, 25, 25.

My main goal is to get the most possible unique combos. I also want to add up the numbers in each combo and find cases where the same total comes up four times.

Any ideas on how to do this efficiently in Google Sheets? I’m wondering if expanding my number range would help get more combos.

2

I’ve tackled a similar challenge in Google Sheets before, and I found a workaround that might help you out. Instead of using separate lists, try creating a single column with all your numbers ending in 5. Then, use the RANDBETWEEN function to pick three random rows from this column.

Here’s a rough outline of the process:

  1. Fill column A with your numbers (15, 25, 35, etc.).

  2. In columns B, C, and D, use formulas like:
    =INDEX($A$1:$A$1000, RANDBETWEEN(1, COUNTA($A$1:$A$1000)))

  3. In column E, add a formula to check if B, C, and D are unique:
    =AND(B1<>C1, B1<>D1, C1<>D1)

  4. Filter out the rows where column E is FALSE.

This method should give you unique combinations. For the sum and frequency, you can add more columns as needed.

Remember to adjust the range in the formulas based on how many numbers you actually have. This approach has worked well for me in generating large sets of unique combinations efficiently.

3

Having worked on similar projects, I can offer an alternative approach that might be more efficient for your needs. Instead of relying on random selection, consider using array formulas to generate all possible combinations systematically.

Start by creating your list of numbers ending in 5 in column A. Then, in a separate sheet or area, use this formula:

=ARRAYFORMULA(CHOOSE({1,2,3},A1:A1000,A1:A1000,A1:A1000))

This will generate all possible combinations. To filter out duplicates within each combination, add a helper column with:

=ARRAYFORMULA(A1<>B1 AND A1<>C1 AND B1<>C1)

Filter this column to TRUE to keep only unique combinations. For summing and finding combinations with the same total, you can add additional columns as needed.

This method ensures you get all possible combinations without relying on randomness, which should help in achieving your goal of maximizing unique combinations efficiently.

4

yo, have u tried using a pivot table? it might work for ur problem. set up ur list of numbers in one column, then use the pivot table to make combos. u can filter out dupes and do sums easily. just an idea, might save u some time instead of messin with complex formulas