all possible combination formula for numbers

[awanone]

Hi All,
I would like to find out a formula for the following situation, i have the following 5 integers lets say A, B, C, D, E (they can be numbers in between 0 to 4 & one number can repeat in the sequence like 01202 here 2 and 0 is repeating ) , i would like to see all the possible combinations for example ( as example stated 01202 would want to see all combinations possible such as 00122, 22001,12020 so on ... ). so is there any formula in excel where I can give the numbers and it can provide me with all possible combinations, thanks for the help

 

[sandy666]

or like this

#1	#2	#3	#4	#5		Number
0	0	1	2	2		00122
						00212
						00221
						01022
						01202
						01220
						02012
						02021
						02102
						02120
						02201
						02210
						10022
						10202
						10220
						12002
						12020
						12200
						20012
						20021
						20102
						20120
						20201
						20210
						21002
						21020
						21200
						22001
						22010
						22100

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Transpose = Table.Transpose(Source),
    ExpandFirst = Table.ExpandListColumn(Table.AddColumn(Transpose, "Custom", each {0..4}), "Custom"),
    ExpandSecond = Table.ExpandListColumn(Table.AddColumn(ExpandFirst, "Custom.1", each {0..4}), "Custom.1"),
    ExpandThird = Table.ExpandListColumn(Table.AddColumn(ExpandSecond, "Custom.2", each {0..4}), "Custom.2"),
    ExpandFourth = Table.ExpandListColumn(Table.AddColumn(ExpandThird, "Custom.3", each {0..4}), "Custom.3"),
    Type = Table.TransformColumnTypes(ExpandFourth,{{"Column1", type text}, {"Custom", type text}, {"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}}),
    Join = Table.AddColumn(Type, "Number", each [Column1]&[Custom]&[Custom.1]&[Custom.2]&[Custom.3]),
    SortAsc = Table.Sort(Table.Distinct(Table.SelectColumns(Join,{"Number"})),{{"Number", Order.Ascending}}),
    Filter0 = Table.SelectRows(Table.AddColumn(SortAsc, "Custom", each List.Count(Text.Split([Number],"0"))-1), each ([Custom] = 0 or [Custom] = 1 or [Custom] = 2)),
    Filter1 = Table.SelectRows(Table.AddColumn(Filter0, "Custom.1", each List.Count(Text.Split([Number],"1"))-1), each ([Custom.1] = 0 or [Custom.1] = 1)),
    Filter2 = Table.SelectRows(Table.AddColumn(Filter1, "Custom.2", each List.Count(Text.Split([Number],"2"))-1), each ([Custom.2] = 0 or [Custom.2] = 1 or [Custom.2] = 2)),
    Filter3 = Table.SelectRows(Table.AddColumn(Filter2, "Custom.3", each List.Count(Text.Split([Number],"3"))-1), each ([Custom.3] = 0 or [Custom.3] = 0)),
    Filter4 = Table.SelectRows(Table.AddColumn(Filter3, "Custom.4", each List.Count(Text.Split([Number],"4"))-1), each ([Custom.4] = 0 or [Custom.4] = 0)),
    TSC = Table.SelectColumns(Filter4,{"Number"})
in
    TSC

 

[sandy666]

here is case with defined repeats

	Number0	Number1	Number2	Number3	Number4		Warning		Numbers		Number
Repeat	2	1	2	0	0		5		0		00122
									1		00212
									2		00221
									3		01022
									4		01202
											01220
											02012
											02021
											02102
											02120
											02201
											02210
											10022
											10202
											10220
											12002
											12020
											12200
											20012
											20021
											20102
											20120
											20201
											20210
											21002
											21020
											21200
											22001
											22010
											22100
											30

 

[Fluff]

Cross posted on Reddit

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[sandy666]

I'll post M if I take a time to do that in a single Query

 

[CA_Punit]

Using Formula the question is solved in Reddit. Thanks @Fluff for the update.

 

[CA_Punit]

The solution provided in reddit is not dynamic in nature and seems to ignore the fact that numbers can change.
Can someone help with a formula based answer