Selecting from a Changing Column of Numbers

Hello Excel Forums,

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

Description:
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).

Issue:
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 http://www.mrexcel.com/forum/excel-q...ml#post3831886

Hello Teethless mama,

Thank you for your reply, and my apologies for a late response.

I analyzed the formula and had fun trying to understand it. I learned about the COUNTIF function; when nested in an IF, any positive whole number returns TRUE and 0 returns FALSE. So if a number in Column B is found in A, it returns how many to IF, and as long as it was not 0, then IF returns B2 (as wanted)! If COUNTIF finds 0, then IF returns something more complicated :3.

The design of MIN(IF(A$2:A$41>C1,A$2:A$41)) is pretty cool! I think I understand the idea. First, it compares the cell one above the current (in Column C) to each number in Column A. IF(A$2:A$41>C1,A$2:A$41) returns the list of numbers greater than (but not equal to) the cell one above the current. Then MIN returns the smallest number in the list, and then the formula is reiterated down Column C. Since the cell one above is important, I guess that Excel processes a column of formulas from lowest to highest row number. Also, because of MIN, and because the formula is always looking at the cell one above, I must sort Column B from least to greatest (although I don't think A needs sorting). Please correct me if I am wrong so far.

As I was analyzing, I realized the biggest hurdle would have been putting the idea into Excel language. Learning about array formulas today helped, and hopefully I've become a bit better at writing Excelese.



I appreciate your help, and hope you have great week!

- Polite Master