Excel 2007 : Semi-Random list, no duplicates.

**Masker** · 05-15-2012, 04:09 PM

Hi Forum. Hope you can help me.

I'm working on a spreadsheet for a science fiction game. In it, the spreadsheet is randomly designing a solar system for me. It can have a varied number of planets, each residing in 1 of 19 possible orbits. The orbits it resides in is somewhat random, though, for example, icy planets have a greater chance of being further out from the central star than hot ones, etc.

I have everything working fine as for how the planets are generated. The spreadsheet will also assign them an orbit number according to my rules. My problems are this:

1) The spreadsheet will often assign an orbit number that duplicates an already existing one, and/or
2) Sometimes the spreadsheet will assign a planet an orbit that can't exist because of the size of the central star (really big stars can envelop up to the first 9 orbits).

The rules for the game state that if you randomly roll an orbit that is already taken, you put the world in the nearest empty orbit, going either up or down in orbits until you find an empty one. I have the spreadsheet already calculating star size, and if the star takes up any orbits, it marks them as taken. So my question:

How can I have Excel determine a non-repeating orbit number for each planet within these rules? I.e. if an orbit is occupied for any reason, go up or down an orbit until an empty orbit is found, and assign the planet to that orbit. Of course, as the list is populated, more orbits are occupied and so that would affect which orbits later planets could go into.

An example might help:

Here's a simplified list from the spreadsheet:

Orbit
0 Occupied by Star
1 Occupied by Star
2
3
4 Planet Amethyst
5
6

So, let's say that Planet Beryl is generated, and given an orbit of 4. Well, Orbit 4 is occupied, so the rules say Planet Beryl can occupy either Orbit 3 or 5.
Let's say that Planet Beryl was given an orbit of 1. This orbit is occupied by the star. So it should be put into Orbit 2. It can't go into Orbit 0.

About me: I am self-taught with Excel. I don't know anything about VBA or macros. However, I'm pretty good at picking up on formulae and their use. (I learned a lot from building this project.) I'm handy with IFs, ANDs, ORs, VLOOKUPS, etc. and used LEFTs and RIGHTs a few times. Maybe there's a formula that makes this easy-- would be great if you could point me towards it.

Thanks in advance.

**Spencer101** · 05-15-2012, 04:59 PM

This sounds like an interesting project... any chance we could see an example workbook to make things easier to understand?

**Masker** · 05-15-2012, 06:55 PM

The game is in Beta test so it's not for general consumption. (It's a tabletop RPG). However, I should be able to mock up the relevant areas and remove any specific game content later tonight as an example.

Now to figure out how to post workbooks here...

**Masker** · 05-15-2012, 10:33 PM

I've attached a workbook with some notes. It's a -greatly- simplified workbook, showing just the area I'm having problem with, and taking out a lot of the VLOOKUP tables I used. I just typed in some values which simulate the values I am using.

You'll see at the top a list of 19 orbits. The spreadsheet already correctly places any orbits the central star occludes as well as the orbit of the "Main world" of the system. This is fine and is working as intended. What puzzling me is placing the "Other worlds" correctly according to the rules I specified above: Each other world has an orbit generated for it. If that orbit is free, the world goes into it. If it is not free, then it is placed in the nearest free orbit (up or down the list).

Let me know if you have any questions. Thanks for looking at this.

Planetary Sample.xlsx