but my suggestions were simply based on the sample data provided by the OP.
So you would think that, based on how that data looks, that the following case is
unlikely?
| Book1.xlsb |
|---|
|
|---|
| A | B | C | D |
|---|
| 1 | | Original Value | Expected Value -/+ 1 | Amount to add |
|---|
| 2 | | QUA-QT-1-I1 | QUA-QT-2-I2 | 1 |
|---|
| 3 | | QUA-QT-1-I1 | QUA-QT-2-I2 | 1 |
|---|
|
|---|
Based on how creative you were in the other thread (inserting dates of the form "12DEC2022" where text numbers originally were in the sample data, etc.) in order to break my shorter solutions (which were attempted improvements to my longer/working solution that you were comparing their performance to), I am puzzled that when I just change numbers in the data (keeping everything else the same) in this thread, you're suggesting that it's still possible that your formulas will work for his data.
Or they are quite welcome to stick with the longer suggestions already accepted. No problem from my viewpoint either way.
Until I pointed out that your formulas fail simple modifications to the data, you were insisting how your formulas were better than my long one was due to length (fair enough). But if they don't handle the same variety of data (at least on this
basic level), they aren't comparable at all.
But what I don't get is, my solution was already accepted. If you have no problem with him sticking with the longer solution ("from your viewpoint"), then why did you post in this thread at all? It's a legitimate/civil/professional question based on the claim you just made. Please take it as such!
If it was to teach others a more direct way to solve the problem, based on the usual (good) quality/robustness of your formulas, I am puzzled to see that you would think it to be good practice to make assumptions about the data as to make quite a shortcut (with the SUBSTITUTE) that would make the formula vulnerable to minor changes to the data.
The one good thing that came out of this conversation is that if he wants a shorter (
working) solution, he has one now, as I showed that my new one passes the same tests as the original, and it's of comparable length (and directness) to yours.
But, for completeness, can you find a shorter (working) solution that does the same thing as mine LONG solution (for in earlier versions of Excel)? I have no idea how to tackle that without SEARCH (and still use all non-volatile functions).
