Selecting from a Changing Column of Numbers

[Polite Master]

Hello Mr. Excel Forums,


I am creating a spreadsheet to help manage Block ID conflicts when adding mods to a Minecraft Server.


Column A contains a list of available numbers (e.g. 1-20 and 31-50). Column B contains a list of numbers a new mod tries to use up by default (i.e. 1-25). A number in Column B that is not in Column A must be changed to the nearest number that is in Column A. In this example, numbers 21-25 in B are not available in A and must be changed to 31-35, respectively. Column C should be the result of changing Column B (i.e. 1-20 and 31-35).


The main problem is that as a given cell in Column B changes, that number needs to become unavailable in Column A for the next change. For example, when 21 in B returns 31, 31 can no longer be used for the next number in B (22). I have been able to use VLOOKUP or INDEX/MATCH functions to lookup the numbers to be changed. However, I am at a loss on how to solve the aforementioned problem. Any suggestions or thoughts you may have are greatly appreciated :D






Thank you for reading and in advance for your ideas.


- Polite Master

Also posted at Selecting from a Changing Column of Numbers with attached spreadsheet.

 

[Gerald Higgins]

Hi, I think I understand what you want to do, this sounds like a good little challenge for formulas, which I'm sure is do-able.

But before I get too deeply into it, could you give us a fully worked example please, showing what you want to do for each number ?

Thanks

 

[Polite Master]

Hi Gerald Higgins,

Thanks for replying!

I believe I just found a solution, although I have yet to test it with my real data. That solution can be read at my repost here: Selecting from a Changing Column of Numbers

The example spreadsheet you requested is posted at the above link. I was unsure how to attach my example on Mr. Excel Forums. I'd appreciate if you could point out how to attach a spreadsheet (or point to help) for the future :D.



With gratitude,

Polite Master

 

[Michael M]

Hi Polite Master
This forum doesn't allow attached spreadsheets.
You can either use the HTML Maker in my tag to post SMALL samples of your data
OR
use a hosting site like DropBox or MediaFire to upload you workbook and then post a link to that site back here !!

 

[Polite Master]

Hi Michael M,

I appreciate you clarifying for me. :D


- Polite Master