It's been a while since I've done anything this complicated in excel.
I need to create a field that will indicate a status based on some rules. Here's the fields I'm working with
Name
ID1
ID2
ID3
...
ID9
So name and ID9 will always have data the rest are variable and may be blank except at least 1 must have data. Here's the criteria (I think):
If the lowest ID field has data (then that value becomes the ID for the name IDNAME) and then RESULT I NEED would be IDNAME +1 (named H-ID).
See attached sheet for examples
constraints:
There will never be any blank ID# fields larger than one that ends up being IDName (i.e. if ID1 is not blank then all ID fields will have an entry. If ID7 is the first non-blank then only ID7, ID8 and ID9 will have data and ID9 could be the first non-blank and will NEVER be blank).
There will never be duplicate IDName entries but H-ID/RESULT may be duplicated.
Eventually I want to be able to sort these by IDName then any entries where H-ID equals IDName. See example
I think can figure out the formulas/VBA stuff but my question is, will this be best written as a Macro/VBA or just a complicated formula? The sheet will be big but not HUGE (at most it will be under 5000 lines but it does have like 30 columns. I'm pretty sure VBA is the way to go but just want to see if maybe excel has made the If/then looping easier in a formula since the last time I did something like this.
Bookmarks