reducing 4 digit number by removing duplicate numerals within

**nflmvp** · 07-20-2010, 11:46 AM

This might sound stupid but I need a formula to reduce the list of numbers of 0001 thru 9,999 down to a list that includes only numbers without duplicate numerals in it.

For example, the list cannot contain ,0001, 3132, 4225, 6717, 8428, etc., etc., etc., because each of those numbers (and many more of them) have numerals that appear in them more than once.

Is this do-able? Can anyone help a noob?

**Ron Coderre** · 07-20-2010, 11:52 AM

I'm not sure from your post...
Do you want those numbers with duplicate digits removed from the list?
or
Do you want the duplicate digits removed from each cell?

**nflmvp** · 07-20-2010, 11:57 AM

I need the numbers removed from the list.

I also have one more step...

Once the numbers with duplicate numerals have been removed, I need to come up with a formula that will remove every additional number that contains the same 4 numerals. For example, if I already have 0123, I need to remove (or somehow identify) every other combination of those four numbers....1230, 2301, 3012, etc

**shg** · 07-20-2010, 12:22 PM

That just leaves the 210 combinations of 10 choose 4, right?

       -A-- -B-- -C-- -D-- -E-- -F-- -G--
   1   0123                              
   2   0124                              
   3   0125                              
   4   0126                              
   5   0127                              
   6   0128                              
   7   0129                              
   8   0134                              
   9   0135                              
  10   0136                              
  11   0137                              
  12   0138                              
  13   0139                              
  14   0145                              
  15   0146                              
  16   0147                              
  17   0148                              
  18   0149                              
  19   0156                              
  20   0157                              
  21   0158                              
  22   0159                              
  23   0167                              
  24   0168                              
  25   0169                              
  26   0178                              
  27   0179                              
  28   0189                              
  29   0234 1234                         
  30   0235 1235                         
  31   0236 1236                         
  32   0237 1237                         
  33   0238 1238                         
  34   0239 1239                         
  35   0245 1245                         
  36   0246 1246                         
  37   0247 1247                         
  38   0248 1248                         
  39   0249 1249                         
  40   0256 1256                         
  41   0257 1257                         
  42   0258 1258                         
  43   0259 1259                         
  44   0267 1267                         
  45   0268 1268                         
  46   0269 1269                         
  47   0278 1278                         
  48   0279 1279                         
  49   0289 1289                         
  50   0345 1345 2345                    
  51   0346 1346 2346                    
  52   0347 1347 2347                    
  53   0348 1348 2348                    
  54   0349 1349 2349                    
  55   0356 1356 2356                    
  56   0357 1357 2357                    
  57   0358 1358 2358                    
  58   0359 1359 2359                    
  59   0367 1367 2367                    
  60   0368 1368 2368                    
  61   0369 1369 2369                    
  62   0378 1378 2378                    
  63   0379 1379 2379                    
  64   0389 1389 2389                    
  65   0456 1456 2456 3456               
  66   0457 1457 2457 3457               
  67   0458 1458 2458 3458               
  68   0459 1459 2459 3459               
  69   0467 1467 2467 3467               
  70   0468 1468 2468 3468               
  71   0469 1469 2469 3469               
  72   0478 1478 2478 3478               
  73   0479 1479 2479 3479               
  74   0489 1489 2489 3489               
  75   0567 1567 2567 3567 4567          
  76   0568 1568 2568 3568 4568          
  77   0569 1569 2569 3569 4569          
  78   0578 1578 2578 3578 4578          
  79   0579 1579 2579 3579 4579          
  80   0589 1589 2589 3589 4589          
  81   0678 1678 2678 3678 4678 5678     
  82   0679 1679 2679 3679 4679 5679     
  83   0689 1689 2689 3689 4689 5689     
  84   0789 1789 2789 3789 4789 5789 6789

**shg** · 07-20-2010, 11:56 AM

If the former, in an adjacent cell,

=IF(OR(ISNUMBER(FIND(MID(A1, {1,2,3}, 1), MID(A1, {2,3,4}, 3)))), "x", "")

or

=IF(MAX(FREQUENCY(MID(A1, {1,2,3,4}, 1) + 0, {0,1,2,3,4,5,6,7,8,9}))>1, "x", "")

**martindwilson** · 07-20-2010, 07:03 PM

**nflmvp** · 07-20-2010, 07:27 PM

These look like what I need.

In the event I'm wrong about the second step, I'll try to work up the numbers from the first step. Admittedly, I'm not that great with formulas.

Thanks for the help!

**nflmvp** · 07-21-2010, 01:26 PM

Thanks shg and martindwilson, for your help. In narrowing down that list to 210 numbers, I found the target much more quickly. Instead of having to go through 2359 numbers, I only had to go through 92 (well, my daughter did).

Thanks again!