2

I have an excel file where I wish to make comparisons to two columns. I am trying to create a third column that is based off a conditional lookup.

Column1 has input data, Column2 has output data, and Column3 is where I wish to store results from a lookup table. Both have times in them as well. The four conditions I have are the following:

  1. No input, No Output, Result is "Result One"
  2. No Input, Output is less than 5 days and 20 hours, Result is "Result Two"
  3. Input is less than 24 weeks, Output is less than 24 weeks, (But both are greater than one week) Result is "Result Three"
  4. Input is less than one week, Output is less than one week, Result is "Result Four"

Thus this is how it would be:

Column1 Column2 Column3
No Input No Output Result One
No Input Output 4d02h Result Two
Input 23w4d Output 22w3d Result Three
Input 3d01h Input 2d22h Result Four

I have tried creating a lookup table and using the concat feature on Column1 and Column2. This works for "Result One" but for the others they are inequalities and thus I'm not entirely sure on how to do them.

David Leal
  • 6,373
  • 4
  • 29
  • 56
Boris
  • 67
  • 8
  • Interesting question! Is the data exactly as shown in the table, e.g. the word 'Input' followed by a string showing the duration? What would happen if (for example) input was less than 24 weeks and output more than 24 weeks? Are other time units possible or can it only be weeks (w), days (d) and hours (h) ? – Tom Sharpe Nov 08 '22 at 09:26
  • Can I ask does it have to be lookup for the third column or could it not just be embedded `IF` statements to follow your rules? – RGilchrist Nov 08 '22 at 14:36

2 Answers2

2

enter image description here Excel Sheet for Inequalities Lookup Comparison

For Column1

Column1/Week = IFERROR(RIGHT(LEFT(A2, SEARCH("w", A2)-1), LEN(LEFT(A2, SEARCH("w", A2)-1)) - MAX(IF(ISNUMBER(MID(LEFT(A2, SEARCH("w", A2)-1), ROW(INDIRECT("1:"&LEN(LEFT(A2, SEARCH("w", A2)-1)))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(LEFT(A2, SEARCH("w", A2)-1)))), 0)))*1, 0)
Column1/Day = IFERROR(RIGHT(LEFT(A2, SEARCH("d", A2)-1), LEN(LEFT(A2, SEARCH("d", A2)-1)) - MAX(IF(ISNUMBER(MID(LEFT(A2, SEARCH("d", A2)-1), ROW(INDIRECT("1:"&LEN(LEFT(A2, SEARCH("d", A2)-1)))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(LEFT(A2, SEARCH("d", A2)-1)))), 0)))*1, 0)
Column1/Hour = IFERROR(RIGHT(LEFT(A2, SEARCH("h", A2)-1), LEN(LEFT(A2, SEARCH("h", A2)-1)) - MAX(IF(ISNUMBER(MID(LEFT(A2, SEARCH("h", A2)-1), ROW(INDIRECT("1:"&LEN(LEFT(A2, SEARCH("h", A2)-1)))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(LEFT(A2, SEARCH("h", A2)-1)))), 0)))*1, 0)
Column1/Total Hours = D2 * 7 * 24 + E2 * 24 + F2

For Column2

Column2/Week = IFERROR(RIGHT(LEFT(B2, SEARCH("w", B2)-1), LEN(LEFT(B2, SEARCH("w", B2)-1)) - MAX(IF(ISNUMBER(MID(LEFT(B2, SEARCH("w", B2)-1), ROW(INDIRECT("1:"&LEN(LEFT(B2, SEARCH("w", B2)-1)))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(LEFT(B2, SEARCH("w", B2)-1)))), 0)))*1, 0)
Column2/Day = IFERROR(RIGHT(LEFT(B2, SEARCH("d", B2)-1), LEN(LEFT(B2, SEARCH("d", B2)-1)) - MAX(IF(ISNUMBER(MID(LEFT(B2, SEARCH("d", B2)-1), ROW(INDIRECT("1:"&LEN(LEFT(B2, SEARCH("d", B2)-1)))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(LEFT(B2, SEARCH("d", B2)-1)))), 0)))*1, 0)
Column2/Hour = IFERROR(RIGHT(LEFT(B2, SEARCH("h", B2)-1), LEN(LEFT(B2, SEARCH("h", B2)-1)) - MAX(IF(ISNUMBER(MID(LEFT(B2, SEARCH("h", B2)-1), ROW(INDIRECT("1:"&LEN(LEFT(B2, SEARCH("h", B2)-1)))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(LEFT(B2, SEARCH("h", B2)-1)))), 0)))*1, 0)
Column2/Total Hours = H2 * 7 * 24 + I2 * 24 + J2

For Column3

Column3 = IFS(AND(G2=0,K2=0), "Result One", AND(G2=0,K2<140), "Result Two", AND(G2>0,G2<168,K2>0,K2<168), "Result Four", AND(G2>=168,G2<4032,K2>=168,K2<4032), "Result Three", TRUE, "No Result")
Kevin
  • 432
  • 1
  • 2
  • 10
1

You could try this to convert the time duration strings into a number of hours and then you could do various comparisons on them but the question needs more clarification.

=LET(units,{"h","d","w"},hours,{1,24,168},array,
  TEXTSPLIT(REDUCE(A1,units,LAMBDA(a,c,SUBSTITUTE(a,c,"-"&c&"|"))),"-","|",TRUE),
    SUM(TAKE(array,,1)*XLOOKUP(TAKE(array,,-1),units,hours)))

enter image description here

We can encapsulate previous logic into a LAMBDA function: CONV, to complete the rest of the transformation and finally get the desired result. Put the following formula in cell E2:

=LET(input, A2:B5, fiveDays20Hrs, 140, wk1d, 168, wk24d, 4032, 
 clean, SUBSTITUTE(SUBSTITUTE(input, "No Input", "No-Input"),
 "No Output", "No-Output"), ColA, INDEX(clean,,1), ColB, INDEX(clean,,2),
 SPLIT, LAMBDA(x, TEXTSPLIT(TEXTJOIN(";",,x), " ", ";",,,"")),
 CONV, LAMBDA(arr, LET(units,{"h","d","w"}, hours,{1,24,168},
  DROP(REDUCE("", arr, LAMBDA(acc, item, LET(parse, REDUCE(item,units, 
   LAMBDA(a,c,SUBSTITUTE(a,c,"-"&c&"|"))), split, TEXTSPLIT(parse,"-","|", TRUE),
   total, SUM(TAKE(split,,1)*XLOOKUP(TAKE(split,,-1),units,hours)),
   VSTACK(acc, IFERROR(total,0))))),1)
  )),
 set, HSTACK(SPLIT(ColA), SPLIT(ColB)), setAHrs, INDEX(set,,2), 
 setBHrs, INDEX(set,,4), colAHrs, CONV(setAHrs),
 colBHrs, CONV(setBHrs),
 MAP(INDEX(set,,1), colAHrs, INDEX(set,,3), colBHrs, LAMBDA(colA, hrsA, colB, hrsB,
 IF(AND(colA="No-Input", colB="No-Output"), "Result One", 
  IF(AND(colA="No-Input", hrsB < fiveDays20Hrs), "Result Two", 
    IF(AND(hrsA > wk1d, hrsA < wk24d, hrsB > wk1d, hrsB < wk24d), "Result Three", 
      IF(AND(hrsA < wk1d, hrsB < wk1d), "Result Four", "CASE NOT DEFINED"))))
 ))
)

and here is the corresponding output:

final result

David Leal
  • 6,373
  • 4
  • 29
  • 56
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • great idea on how to convert this information into hours. I see you use the fourth input argument (`ignore_empty`) of `TEXTSPLIT` to `TRUE`, but according to the [documentation](https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7) that is the default value. What surprised me is that if you don't put it, you get `#VALUE!`. I don't know if Microsoft documentation is wrong or if I am missing something. Thanks – David Leal Nov 10 '22 at 15:48
  • I was trying to use your approach for an array creating a `LAMBDA` function and using `BYROW`, It works for a single value but it doesn't work when using inside `BYROW`. I don't know why. `=LET(CONV, LAMBDA(y, LET(units,{"h","d","w"},hours,{1,24,168},array, TEXTSPLIT(REDUCE(y,units,LAMBDA(a,c,SUBSTITUTE(a,c,"-"&c&"|"))),"-","|", TRUE), SUM(TAKE(array,,1)*XLOOKUP(TAKE(array,,-1),units,hours)))), BYROW({"23w4d"; "3d01h"}, LAMBDA(item, CONV(item))) )` Any suggestion on why is that? Thanks – David Leal Nov 10 '22 at 15:51
  • I tested it works by replacing `{"23w4d"; "3d01h"}` with a range with the same values. It should not be like that because `BYROW` accepts as the first input argument an array. It is weird. – David Leal Nov 10 '22 at 18:19
  • 1
    @David re your first comment I found the same thing and am equally puzzled by it. Re the second comment, I'll have a look at it tomorrow when hopefully my brain might be working a bit better than it is now :-) – Tom Sharpe Nov 10 '22 at 22:48
  • 1
    @Manny Cl thank you for accepting my answer! I actually meant to put quite a bit more effort into it to try and answer your question more fully; for my own satisfaction, I will add some more to it tomorrow. – Tom Sharpe Nov 10 '22 at 22:50
  • ok, thanks for responding @TomSharpe, I did some tests and I think the problem is with `TEXTSPLIT` output, it returns the first row of the first column only, but I don't know why. For example, this simple test doesn't work: `=LET(input, {"a,b;c,d;";"e,f;g,h;"}, BYROW(input, LAMBDA(item, TEXTJOIN(";",,TEXTSPLIT(item,",",";", TRUE)))))` – David Leal Nov 11 '22 at 00:06
  • Tom, I was able to make it works, and also to complete the request of the question, based on your approach. I didn't consider it appropriate to post a new answer based on your idea, because it is key to the solution, so I updated your answer with the pending steps. Please feel free to roll it back or make the changes you consider. Thanks – David Leal Nov 11 '22 at 04:21
  • 1
    Well done! I was starting to take the same approach by defining my conversion method as a lambda. The only things I would have done differently would be to keep the lambda separate (I actually used the advanced formula editor to format it nicely). I was going to use helper columns to separate the result (output) logic from the conversion (input) logic and expressions like ConvertToHour("5d20h") to define constants. But all this is a matter of personal preference. – Tom Sharpe Nov 11 '22 at 08:08
  • Thanks, Tom, it seems to be a bug, what I mentioned about `BYROW` in my previous comment. Using `MAP` instead produces the expected result. Check the question I posted to show the problem: [TEXTSPLIT combined with BYROW returns an unexpected result when using an array of strings as input](https://stackoverflow.com/questions/74397019/textsplit-combined-with-byrow-returns-an-unexpected-result-when-using-an-array-o?noredirect=1#comment131337833_74397019). Anyway, the approach I took in your answer via `REDUCE/VSTACK` is a good approach too for this case. – David Leal Nov 11 '22 at 14:02
  • Tom, are you referring to this Add-ins [Advance Formula Environment](https://www.microsoft.com/en-us/garage/profiles/advanced-formula-environment-a-microsoft-garage-project/) to edit your formulas? – David Leal Nov 11 '22 at 14:16
  • Yes, Advanced Formula Environment is what I meant - I think it's pretty useful. – Tom Sharpe Nov 14 '22 at 22:03