Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 29 2024 10:46 AM

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

Labels:

17 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 29 2024 10:48 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 29 2024 11:34 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 29 2024 01:16 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 29 2024 02:33 PM - edited Apr 29 2024 02:35 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 29 2024 03:23 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 29 2024 04:15 PM

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

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 29 2024 04:16 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 29 2024 04:50 PM

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

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)*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 29 2024 09:13 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 29 2024 09:43 PM

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.

I entered this formula on MS365 online, and it gives me a bunch of pound signs. Maybe I am doing something wrong.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 29 2024 10:09 PM

I attached the file.

Formula in column N9.

The idea is like this:

See the first answer.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 29 2024 10:21 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 29 2024 10:27 PM

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.

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 29 2024 10:29 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 30 2024 08:09 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 30 2024 09:12 AM

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)*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 29 2024 09:13 PM

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