Question on ordering and manual overrides

[kwhelanne]

Hi!

I am interested in creating a formula (or macro if necessary) to change a list of numbers that indicate order based on a manual override. I have attached an example spreadsheet. For each category, there should be sequential numbers for all of the colors within that category, starting at 1 (for example, for primary colors, blue is 1, yellow is 2, and red is 3). But there is a manual override for some of them (e.g., I actually want blue to appear 3rd). How can I write formulas or VBA to get to a "finalized order" that takes into account the manual overrides and the constraint that there must be consecutive numbers starting at 1 (e.g., changing yellow to 1, red to 2, and blue to 3)?

Example ordering issue.xlsx

I have been scouring the internet, but haven't been able to find anything relevant; nevertheless please feel free to direct me somewhere if you know of a helpful resource.

Thanks!!

[vichopalacios]

Hi
Can you try this spreadsheet.
It is done by means of a simple macro
I hope it's what you are looking for

[kwhelanne]

Thank you so much!