Connect periods with spilled result, where the result is same as the calculated result in previous row

[roelandwatteeuw]

Hi all

Thanks for taking time to read my problem.

Let me first quickly introduce you to the data.

Data:

The black columns are given data:

A - Number: Is an unique number per product​

B - Begin: Start date from the product​

C - End: End date from the product​

This data is sorted:

First on column A - Number: Low to High​

Next on column B - Begin: Low to High​

​

The green and orange columns are calculated ones:

D - Abegin: Connects the periods if they directly follow each other AND have the same number​

E - Key: Concatinates the 'Number' and 'Abegin'​

F - Aend: Gives the maximum value from column C - End, when the key matches the key of the current row​

Used Formulas:

​

D	E	F
Abegin	Key	Aend
=IF( ISNUMBER($B1); IF( AND($A2 = $A1; $B2 = $C1+1); $D1; $B2 ); $B2 )	=$A2 & "-" & $D2	=MAXIFS( $C:$C; $E:$E; $E2 )
If the the cell in column B, from the row above isn't a number, then give the begin date from the current row (this is to avoid an error on the first row) If the cel is a number, then look in row above to the end date. If that end date is exactly one day before the Begin date from the current row, then take the result that was calculated in the row above. If not, then give the begin date from the currect row	Putting 'Number' and 'Begin' together	Gives the max. value from 'End' if the key matches

Problem:

These formulas work great... so where's the problem?
I want to use them as a spilled result.

The 'Key' and 'Aend' columns aren't a problem, but the 'Abegin' is.
I can't find a way to get the calculated result from the row above.
Probably need a LAMBDA or something...


My current formula for Abegin is:

=IF( ISNUMBER( OFFSET(lst_Begin; -1; 0) ); IF( (lst_Nr = lst_NrPrev) * ( lst_Begin = OFFSET(lst_Begin; -1; 1)+1 ); "PREV DATE"; lst_Begin ); lst_Begin )

lst_Begin = all data from column B - Begin - without header (B2:B10) lst_Nr = all data from column A - Number - without header (A2:A10) lst_NrPrev = Number in the previous row (A1:A9)

Result with this formula is in column I:

So I need a formula that fills in the 'PREV DATE' value.

If needed, I can upload the file.

thx!

Grtz
Roeland

 

[roelandwatteeuw]

How about

=DROP(REDUCE("",UNIQUE(FILTER(A2:A100,A2:A100<>"")),LAMBDA(x,y,LET(f,FILTER(B2:C100,A2:A100=y),r,VSTACK(99,DROP(INDEX(f,,1),1)-DROP(INDEX(f,,2),-1)),VSTACK(x,IF(ROWS(f)=1,INDEX(f,,1),DROP(REDUCE("", SEQUENCE(ROWS(f)),LAMBDA(m,n,VSTACK(m,INDEX(f,XMATCH(TRUE,CHOOSEROWS(r,SEQUENCE(n))<>1,,-1),1)))),1)))))),1)

Thank you Fluff!

Well, this seems to work if I enter the formula in my English version of Excel.
When opening the file in my Dutch version, the calculation is done, but when I want to "change" something, I get an error. (even just enter the existing formula gives an error)
So probably this formula isn't supported in (my) Dutch version.

This is the biggest issue.

Apart from this, I find it quite hard to rework the formula as well.
I mean, when I want to add an extra condition, it's really difficult (for me).

e.g. I add an extra column 'Group'. Only the periods from the same number AND the same group that follow directly can have the begin date from above.​

My knowledge isn't big enough to solve this problem.

For me it's really magic that you're doing, Fluff! Amazing!​

So I'm trying to translate it to a more comprehensive formula (for me) and that why I came up with the 2 extra columns (see post #9)
The 'Change?'-column makes it easy to for me to change the conditions.
With the 'Nr records up?'-column I'm trying to get a formula where the 'Change?'-column is accumelated by groups.

Can you make a formula for column B?

 

[Fluff]

As I said before, I would stick with a drag down formula.
Creating a spill formula is sometimes much more trouble than it's worth, & is often very complex.

 

[roelandwatteeuw]

As I said before, I would stick with a drag down formula.
Creating a spill formula is sometimes much more trouble than it's worth, & is often very complex.

I fully understand your opinion.
However, automate the full process would make it so much better.

And I think the same... Don't make it too complex. That's why I looked for a more comprehensive way.
Adding the 2 extra columns helped in this.
The first column 'Change?' is easy to understand and to rework. And that's the most important one.
The second colomn 'Nr records up?' doesn't have to be easy or simple formula, because it won't ben necessary to rework this one.
So it can be complex, but off course, it has to work properly.

 

[Fluff]

Maybe someone else will help, but I provided a formula that does what you asked & you dismissed it out of hand.

 

[roelandwatteeuw]

Maybe someone else will help, but I provided a formula that does what you asked & you dismissed it out of hand.

I understand Fluffy, but it doesn't work in my dutch version.
And I think I gave reasonable arguments why I can't use your formula, didn't I?
I want to emphasize that I didn't want to ignore your advice and that I'm grateful for your help.

I know, my question changed a bit during the process, since I was testing your codes and was trying to understand them... but I couldn't do that enough to reform them to my data.

I simplified the data in my initial question in the hope to make it less complex.
But as said before, since I don't understand the formula enough, reforming it is too hard.
And I can't test/modify your last given solution due to my (Dutch) Excel version.

So I kept searching for other solutions and thought of the new way with the two extra columns.
And so my question changed a bit.

The mission is still the same, but I changed the target

(Maybe I should start a new topic with the 'new' question -since it changed- ? Others probably won't read the whole process and leave, don't you think? )

 

[roelandwatteeuw]

With some creativity I found this formula that seem to work for the 'Nr records up?' Column

=IF( $H$2#=0; 0;
   LET(
      Countnr;
         COUNTIFS(
            OFFSET( INDEX(H2#;;1); 0; 0; SEQUENCE(ROWS(INDEX(H2#;;1))) );
               1;
            OFFSET( A2:A13; 0; 0; SEQUENCE(ROWS(A2:A13)) );
               A2:A13 );
      Countnr - IF(
         OFFSET( INDEX(H2#;;1); -1; 0) = 0;
         Countnr - 1;
         0 )
) )

Hooray! (I hope )