Search for value in a column, satisfying specific criteria and use its line content

English
, , , , ,
#1

Hello all,
I have a Calc (4.2.4.2) sheet with these columns

| Date | B | T | P | D | L |

and, for each line, I have to search forward for the first line whose “D” value is equal or greater than B+T+P+D of the current line and then, in current line, L column, place the delta between the found line “Date” field and the current line “Date” field.

Example:

Date		B	T	P	D	L
2014-05-01	8	4	2	10	HERE goes "26" because the first line with D >= "24" is on 2014-05-28.
2014-05-02	7	4	1	10	21 (2014-05-23)
2014-05-03	7	4	1	10	20 (same 2014-05-23)
2014-05-04	7	4	1	10	19 (still 2014-05-23)
2014-05-05	7	4	0	10	18
2014-05-06	7	4	0	10	17
2014-05-07	7	4	0	10	16
2014-05-08	7	4	4	11	19 (2015-05-28)
2014-05-09	7	4	2	15	19
2014-05-10	7	4	2	15	AND
2014-05-11	6	4	2	15	SO
2014-05-12	8	5	3	15	FORTH
2014-05-13	8	5	4	15	FOR
2014-05-14	9	6	3	16	EACH
2014-05-15 	9	6	3	16	SINGLE
2014-05-16	9	6	2	16	LINE
2014-05-17	8	5	3	17	TILL
2014-05-18	8	5	3	17	THE
2014-05-19	8	5	3	17	END
2014-05-20	9	4	2	18
2014-05-21	9	5	2	18
2014-05-22	11	3	3	20
2014-05-23	11	3	3	22
2014-05-24	10	3	3	23
2014-05-25	9	3	4	23
2014-05-26	10	4	3	23
2014-05-27	10	4	3	26
2014-05-28	9	4	3	28

Can you please help me? I’ve tried VLOOKUP but I’m not sure how to properly use it or if it’s the correct thing. Cant’ get a number out of it…

#2

I have to search forward for the first line whose “D” value is equal or greater than B+T+P+D …

Use an ARRAY-formula in a helper-column! (Entered with Shift+Ctrl+Enter)

In a column right of the one labelled “L”(col I here): {=MATCH(TRUE();SUM(B2:E2)<=E3:E$29;0)} in row 2 and then filled down.

In the “L”-column: =DAYS(INDEX(A3:A$29;I2;1);A2) in row 2 and then filled down (NO array-formula!)

Note: Filling down an array-formula dragging the little square requires pressing Ctrl in addition. (No extras if using the menu sequence.)

Atachment added by EDIT1: 35715MatchFirstGreater001.ods

[begin EDIT2] @Marco1 - (See below, commenting ROSt52’s contribution) Backward search will not be done by the SAME formula. But a properly adjusted formula will easily be designed. [end EDIT2]

[begin EDIT3] Calc will recalculate the formulae in a sheet top down and left to right. Functions looking for matches also have a fixed direction of going through their areas. Searching for matches upward row by row seems not to be supported. If monotonic order is assured there might be a way to get formulae to that effect, I didn’t really try. A clearly structured way to perform upward (or leftward) search will, in my opinion, require using helpers for first mirroring the search area - and then reversely the results. Because this looked interesting to me, I did it. See attached!.
(FileWithdrawn!) BE CAREFUL! The sheets might not work reliably! Judge yourself![end EDIT3]

[begin EDIT4] My last comment, does it seem harsh? That wasn’ my intention. My last upload: It was an intermediary file accidentally. Now the correct one, I hope: ask35715MatchFirstGreater002.ods [end EDIT4]

#3

Thank you Lupp, it worked perfectly (I wasn’t even aware of array-formulas…)!!! Except for the fact that I had to find out the matching between, say, “true” and the Italian translation of the formula name, everything worked.

@Lupp… any hint on how to adjust the formula for backward?! :slight_smile: I’m working on my personal CFD (see Cumulative Flow Diagram) and having some auto-reports for my personal kanban would be great! Thanks.

#4

@Marco1 - My last EDIT above was too rash. “… instead of forward, I have to check backward for the same thing / the first (meant: highest row number above?) line whose “D” value is <= than the B+T+P+D of the current line …” won’t work. D above seems to be ALWAYS less or equal D current. In addition all the numbers are supposed to be >0. More (better) explanation needed.
Do you mean “… whose B+T+P+D value is <= than the D of the current …”? This might make sense.

#5

@Lupp you’re right! Let me describe it: I want to calculate my “Production” Lead Time (see http://brodzinski.com/wp-content/uploads/cfd-03.jpg ) and so I need, for each line, to find a previous line where the total of the columns values are the same (or less) than the current D value (respect to picture, I’m considering “B” value in the calculation) and compute delta times.
I actually am not sure whether forward or backward approach is the best, so I’d love to try both and compare them.

#6

@Marco1 - Not sure with what I am right. With everything?

I kind of want to finish this now. Sorry, I’m not really interested in this kind of management stuff. Ways “Best Practice” is revolutionised once in a while isn’t my thing. How do you want to validate any decision following the experiences your’e seeing forward to? See the new EDIT3 to my answer, please!

#7

…whose “D” value is equal or greater than B+T+P+D…

I am afraid there is a error in the expression “B+T+P+D”

D=B+T+P+D if B+T+P=0
D>B+T+P+D if B+T+P<0

The example columns do not include values <= 0. Thus, the initial condition cannot be fulfilled. It might be needed to consider the initial condition.

#8

Did you check my answer? I think it takes “Marco”'s problem as it was meant. Please notice: He is looking FORWARD (rows downward) for greater values of D while he compares them to the value of B+T+P+D from the CURRENT row.

#9

@Lupp - Considering that a search looks down the rows (=FORWARD) to search for B+T+P+D and compare with the current D then my comment is not needed. A macro can handle the problem. No need to check you details, I am sure that the formulas you wrote match the needs for a macro. A +1 for your anwers.

#10

Thanks for the comments. Suppose that, instead of forward, I have to check backward for the same thing (the first line whose “D” value is <= than the B+T+P+D of the current line) and then write current date-found date.
Do you think the same formula would work?! I’m giving it a try.

#11

@ROSt53 - There might be justifiable objections against programming and using macros in many contexts.
The actual foundation of “spreadsheeting” (as I see it) is standard functions and user-defined expressions. Therefore I always look first for a solution by formulae (often using helpers). If you consider looking into the example attached to my answer above you may see how my paradigm does apply to this case. Granted that a programmed macro might be more effective …(How would execution be triggered?)

#12

@ROSt53 sorry I was not clear: all values are >= 0 in all lines.

#13

@Marco1 - We all try to be precise as possible but sometimes another person misinterprets what is written. I this case it was my misinterpretation.

#14

@Lupp - I fully agree with your perception of “spreadsheeting”. I only was wondering about the conditions from a mathematical point-of view for a the situation within a single row. (My misinterpretation of the question.) As mentioned before, I did not analyze your formulas because I learned through several of you replies in this forum that you have a great expertise on such formulas. I hope that, when I am in need, I can get your expertise in complex formulas as well.

#15

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.