SOLVED

# See attached

Brass Contributor

# See attached

I am not able to post this, maybe it will post eventually. It keeps asking me to correct highlited errors but cant find any

17 Replies

# Re: See attached

I had to use Safari, I had tried a thousand time in google chrome and it would not work.

# Re: See attached

Here's at least part of a solution for you. This simple solution presumes that YOU take responsibility for entering the five numbers in ascending order. I'm sure we could devise a way to automate that too, but in the absence of a compelling reason to automate it, I chose to stick with "simple."

The formula in column N relies on converting the numbers in columns A through E to text.

``=TEXT(A1,"0")&"-"&TEXT(B1,"0")&"-"&TEXT(C1,"0")&"-"&TEXT(D1,"0")&"-"&TEXT(E1,"0")``

It then concatenates those text values with the intervening hyphens, and repeats.

# Re: See attached

Thank you for your help @mathetes. I am wondering if I need to be more clear on the instructions. What I only need or the end goal is to have a formula or formulas to generate numbers base on the given numbers in column A to E. Column N gives a few examples of mixing them. Each column keeps their numbers, but all the rows in each column create sets of five numbers, in other words, rows from each column mix to create 5 numbers without leaving their column. The result should be smaller to higher. Notice how the first column numbers are smaller then the second and so forth.

# Re: See attached

I am wondering if I need to be more clear on the instructions.

Apparently. And still.

What I only need or the end goal is to have a formula or formulas to generate numbers base on the given numbers in column A to E. Column N gives a few examples of mixing them.

And the formula I gave does exactly that, and when they're changed in A-E, they change in N. As requested. [you did see the attached spreadsheet as well as the formula in the post, right?]

Notice how the first column numbers are smaller then the second and so forth.

Noted. (Kinda obvious, actually)

So, yes, more clarity. And more clarity on what the solution I gave is (apparently) NOT doing. And given the relative simplicity of the formula I gave, what are you not able to take from that to accomplish the goal. I acknowledged that I did not go to the trouble of automating the ascending sequence of numbers, given that they were all entered that way to begin with.

If that--taking five random numbers and sorting into ascending order-- needs to be automated, then perhaps you could go further still in explaining the context here. What's the "bigger picture" into which this otherwise fairly simple task is fitting? It can be done, but tell us what we're doing it for....

# Re: See attached

Here's a quick-and-dirty version that sorts the five numbers into ascending order as well as combining them.

# Re: See attached

@mathetes, I am sorry, I guess it's a little hard to explain it. I appreciate all your help.
I am not trying to combine cells.

I need to know all the combinations of the given numbers, here is a picture of how I did some of it manually. Let me know if that is clear.

# Re: See attached

I am not sure where to upload the photo, I am having a hard time here.

# Re: See attached

@mathetes
Maybe I wasn't clear, so I went and create another one with heading, photos, and more details.
I hope it's clear or clearer. Here is the link for it below. I really appreciate your help.

https://techcommunity.microsoft.com/t5/excel/generate-sets-of-five-numbers-base-on-the-given-numbers...
best response confirmed by Marco365 (Brass Contributor)
Solution

# Re: See attached

Try this formula if you have MS365:

=TOCOL(TOROW(TOROW(TOROW(TOROW(A1:A5)&"-"&B1:B5)&"-"&C1:C5)&"-"&D1:D5)&"-"&E1:E7)

Also, I think you can just edit this post instead of creating a new one.
Creating new posts creates confusion.

I had to look at both of your posts to figure out that you don't need to SORT numbers by "ascending order" explicitly, as numbers in column 2 is always greater than numbers in column1, column 3 is always greater than column2....
All you need is just combination.

# Re: See attached

Thank you, next time I will try to stick to one post and add more details. I thought it would bring less confusion.
I entered this formula on MS365 online, and it gives me a bunch of pound signs. Maybe I am doing something wrong.

# Re: See attached

I attached the file.

Formula in column N9.

The idea is like this:

https://stackoverflow.com/questions/72334182/formula-to-concatenate-all-combinations-of-values-two-c...

# Re: See attached

I am so sorry for making you work so hard. I kept trying and it finally worked. It was me who couldn't figure it out the first time. It works but on the online version of MS365.
Base on your keyword I did find a simple video. It shows how to get it done but without a formula, I couldn't find the options on the MAC version.
Thanks a bunch.

# Re: See attached

Hi Mathetes,
@rachel was able to help me. I hope you see the message soon so you don't have to put anymore time and effort in it. Thanks for your help.

# Re: See attached

If you are on Mac, best chance would be formula. E.g search “how to find all combinations of two lists in Excel using formulas”. There should be non-365 formulas available.

# Re: See attached

I will look into it. Thank you Rachel

# Re: See attached

Thanks for that solution. I'm not sure how you were able so quickly to deduce that what @Marco365 was seeking was the Cartesian Product.....but that said, I'm still left wondering what kind of context--what kind of practical application--this would ever have. Do you have an example of how this would be used in any setting other than in the mind of Rene Descartes?

# Re: See attached

I am not sure what the context is. but I saw similar questions asked elsewhere. Not combinations of numbers, but combinations of product model, colours etc. for example, ProductType = iPhone15, iPhone16; Model = Pro, Plus, MaxPro; Colours = Blue, Pink, Green, Black. Find all combinations of possible iPhones. and I saw some genius uses this Cartesian Product to do this. so some common sense and not-so-wild guess make me think that this must be what OP is after here too.
1 best response

Accepted Solutions
best response confirmed by Marco365 (Brass Contributor)
Solution

# Re: See attached

Try this formula if you have MS365:

=TOCOL(TOROW(TOROW(TOROW(TOROW(A1:A5)&"-"&B1:B5)&"-"&C1:C5)&"-"&D1:D5)&"-"&E1:E7)

Also, I think you can just edit this post instead of creating a new one.
Creating new posts creates confusion.

I had to look at both of your posts to figure out that you don't need to SORT numbers by "ascending order" explicitly, as numbers in column 2 is always greater than numbers in column1, column 3 is always greater than column2....
All you need is just combination.