Part Number jumbling to give all possible permutations

ellison

Board Regular
Joined
Aug 1, 2012
Messages
200
Office Version
  1. 365
Platform
  1. Windows
Hi,

Is it possible for Excel to "jumble up" a part number & give all of its possible permutations?
In other words...
We have some slightly fat fingered colleagues who have been typing part numbers up with all of the right digits, letters (a-z, A-Z) and characters (dashes, slashes, spaces, commas etc) but the problem is that they have not necessarily typed them in the right order!

So if a part number is nice and short AB12, we could sometimes check whether any of our colleagues have entered one on of the different permutations of that part number =
i) AB12
ii) BA12
iii) B1A2
iv) B12A
v) A1B2
vi) A12B
vii) AB21
etc etc

I'm beginning to lose count of how many different permutations there are

And I'm getting really quite lost when it comes to extrapolating the possibilities on longer part numbers (some of our part numbers contain 25+ characters)

In an ideal world, Excel could do this using a macro with a results sheet which had 2 fields eg
Original Part NumberOutcomes
AB12AB12
AB12BA12
AB12B1A2
etc, etc


Huge thanks for any help you can provide (especially from my fat fingered colleagues!)

:)
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,776
Hi ellison. You can use the random function to generate possible permutations which likely would work for part numbers having 4 or 5 characters but for part numbers with 25 characters it would seem impossible. The possible permutations for a 5 character part number would be 1x2x3x4x5 which would be 120 possible permutations. If U had 25 characters, I can't do the math as the possible permutations are too numerous. I'm guessing it would be better to limit the input of part numbers to existing part numbers which probably doesn't help U now. Good luck. Dave
 

ellison

Board Regular
Joined
Aug 1, 2012
Messages
200
Office Version
  1. 365
Platform
  1. Windows
Hi ellison. You can use the random function to generate possible permutations which likely would work for part numbers having 4 or 5 characters but for part numbers with 25 characters it would seem impossible. The possible permutations for a 5 character part number would be 1x2x3x4x5 which would be 120 possible permutations. If U had 25 characters, I can't do the math as the possible permutations are too numerous. I'm guessing it would be better to limit the input of part numbers to existing part numbers which probably doesn't help U now. Good luck. Dave
Crumbs I thought that may be the case, was holding out hope that Excel may be able to help find a workaround.

Thanks for your input though, appreciated!
 

sdear

Board Regular
Joined
May 23, 2010
Messages
96
Crumbs I thought that may be the case, was holding out hope that Excel may be able to help find a workaround.

Thanks for your input though, appreciated!
Here's something I found in my searchings:
How to generate or list all possible permutations in Excel?

It does up to 7 characters (5040 permutations, or cells in a column).
You'd have a problem with 10 characters because it'd be more lines than what Excel would allow (~1million).
 

ellison

Board Regular
Joined
Aug 1, 2012
Messages
200
Office Version
  1. 365
Platform
  1. Windows
Aw thanks so much, that is excellent and will come in very handy!
All the best
 

Watch MrExcel Video

Forum statistics

Threads
1,129,576
Messages
5,637,183
Members
416,960
Latest member
Carbon1198

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top