Formula to search max. value based on criteria in other column

[roelandwatteeuw]

Hi all

I have this data with columns:
- A: the ID of the product
- B: the start of the proces
- C: the end of te proces
- D: the state of the proces during B to C

ID	Start	End	State
10028700​	1/01/2020​	31/03/2021​	Active
10028700​	1/04/2021​	31/12/9999​	Inactive
10029917​	1/07/2021​	30/09/2021​	Active
10029917​	1/10/2021​	31/12/9999​	Active
10030325​	1/10/2019​	2/03/2021​	Active
10030325​	1/04/2022​	31/12/9999​	Inactive
10031873​	1/09/2021​	3/10/2021​	Inactive
10031873​	4/10/2021​	14/11/2021​	Active
10031873​	15/11/2021​	31/01/2022​	Active
10031873	01/02/2022​	31/12/9999​	Active

What do I need:

A formula where Excel first looks at all the same ID's (A).
For those ID's I need the State (D) of the last End date (C) within all the rows with this ID.

This what it should look like:

ID	Start	End	State	Last state
10028700​	1/01/2020​	31/03/2021​	Active	Inactive
10028700​	1/04/2021​	31/12/9999​	Inactive	Inactive
10029917​	1/07/2021​	30/09/2021​	Inactive	Active
10029917​	1/10/2021​	31/12/9999​	Active	Active
10030325​	1/04/2022​	31/12/2025​	Active	Active
10030325​	1/10/2019​	31/03/2022​	Inactive	Active
10031873​	1/09/2021​	3/10/2021​	Inactive	Active
10031873​	4/10/2021​	14/11/2021​	Active	Active
10031873​	15/11/2021​	31/01/2022​	Active	Active
10031873	01/02/2022​	31/12/9999​	Active	Active

Hope you can help me!


gr.
Roely

 

[BSALV]

you need a formula for a max value or a formula for conditional formatting ?
Can you add those data with the XL2BB-tool ?

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1	ID	Start	End	State
2	10028700	01/01/2020	31/03/2021	Active	Inactive
3	10028700	01/04/2021	31/12/9999	Inactive	Inactive
4	10029917	01/07/2021	30/09/2021	Active	Active
5	10029917	01/10/2021	31/12/9999	Active	Active
6	10030325	01/10/2019	02/03/2021	Active	Inactive
7	10030325	01/04/2022	31/12/9999	Inactive	Inactive
8	10031873	01/09/2021	03/10/2021	Inactive	Active
9	10031873	04/10/2021	14/11/2021	Active	Active
10	10031873	15/11/2021	31/01/2022	Active	Active
11	10031873	01/02/2022	31/12/9999	Active	Active
Main

Cell Formulas
Range	Formula
E2:E11	E2	=INDEX(SORT(FILTER(B2:D11,A2:A11=A2),1,-1),1,3)

 

[roelandwatteeuw]

you need a formula for a max value or a formula for conditional formatting ?
Can you add those data with the XL2BB-tool ?

Hi, Thx for the help.
I need a formula for the max value. (But only the max for the same ID. And for this max I need the corresponding State with this value.).
This has nothing to do with conditional formatting. ?

 

[roelandwatteeuw]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1	ID	Start	End	State
2	10028700	01/01/2020	31/03/2021	Active	Inactive
3	10028700	01/04/2021	31/12/9999	Inactive	Inactive
4	10029917	01/07/2021	30/09/2021	Active	Active
5	10029917	01/10/2021	31/12/9999	Active	Active
6	10030325	01/10/2019	02/03/2021	Active	Inactive
7	10030325	01/04/2022	31/12/9999	Inactive	Inactive
8	10031873	01/09/2021	03/10/2021	Inactive	Active
9	10031873	04/10/2021	14/11/2021	Active	Active
10	10031873	15/11/2021	31/01/2022	Active	Active
11	10031873	01/02/2022	31/12/9999	Active	Active
Main

Cell Formulas
Range	Formula
E2:E11	E2	=INDEX(SORT(FILTER(B2:D11,A2:A11=A2),1,-1),1,3)

Hi, thx for the help!


I had a similar formula with the same result, but, when I sort the values of colomn A ascending + thé value of column C from old to new, the result isn't not good anymore.

Then I got ... (red is wrong)

ID	Start	End	State	Last state
10028700	1/04/2021	31/12/9999	Inactive	Inactive
10028700	1/01/2020	31/03/2021	Active	Active
10029917	1/10/2021	31/12/9999	Active	Active
10029917	1/07/2021	30/09/2021	Inactive	Inactive
10030325	1/04/2022	31/12/2025	Inactive	Inactive
10030325	1/10/2019	31/03/2022	Active	Active
10031873	01/02/2022	31/12/9999	Active	Active
10031873	15/11/2021	31/01/2022	Active	Active
10031873	04/10/2021	14/11/2021	Active	Active
10031873	01/09/2021	03/10/2021	Inactive	Inactive

I need a more consistent formula...
I thought it would be simple, but trying proofed me wrong.


After a long search I think I found the good solution:
=@XLOOKUP(A2 & "-" & (MAXIFS(C:C,A:A,@A:A)),$A$1:A10 & "-" & $C$1:C10,$D$1:D10)

MAXIFS(C:C,A:A,@A:A) searchs the max.value for the corresponding ID
@XLookup searchs the combination of the ID and the maximum value and returns the State in column D
(yeah, I could use an extra column to concatenate the two colomns)

Somebody who knows what the @ is for before XLOOPUP and befor A:A? (Excel made the edit automatic)

 

[Fluff]

I forgot to lock the ranges when I posted the formula, but when that is done, it doesn't matter how the data is sorted.

+Fluff 1.xlsm
	A	B	C	D	E
1	ID	Start	End	State
2	10028700	01/01/2020	31/03/2021	Active	Inactive
3	10028700	01/04/2021	31/12/9999	Inactive	Inactive
4	10029917	01/07/2021	30/09/2021	Active	Active
5	10029917	01/10/2021	31/12/9999	Active	Active
6	10030325	01/10/2019	02/03/2021	Active	Inactive
7	10030325	01/04/2022	31/12/9999	Inactive	Inactive
8	10031873	01/09/2021	03/10/2021	Inactive	Active
9	10031873	04/10/2021	14/11/2021	Active	Active
10	10031873	15/11/2021	31/01/2022	Active	Active
11	10031873	01/02/2022	31/12/9999	Active	Active
Main

Cell Formulas
Range	Formula
E2:E11	E2	=INDEX(SORT(FILTER($B$2:$D$11,$A$2:$A$11=A2),1,-1),1,3)

 

[roelandwatteeuw]

Hi Fluff

Works great as well. Thanks!
I don't know which one is better.
It works and that's the most important
But I think I will use yours


Additional question (or should I make a new post?):
Do you know how to make the range more flexible.
I mean, now you've got '$B$2:$D$11' and '$A$2:$A$11'
but row 11 isn't my final row and it will be different all the time. (start row will remain the same)

I could enter '$B$2:$D11' $A$2:$A11', so it counts up for every line.
Or I could enter '$B$2:$D$1048576' and '$A$2:$A$1048576' (last row possible in Excel)

But isn't there a way to search the last row and then add it to the formula?
Something like '$B$2:D & last row'
For the 'last row' I would use:

=match(2,1/(D:D<>""),1)

This combination gives an error:

=$B$2:D & match(2,1/(D:D<>""),1)

And this will not work in the formula:

="$B$2:D" & match(2,1/(D:D<>""),1)

 

[Fluff]

I would just use something like

=INDEX(SORT(FILTER($B$2:$D$1000,$A$2:$A$1000=A2),1,-1),1,3)

and change the 1000 to a row that is below the max number of rows you would expect, without going over the top.

 

[roelandwatteeuw]

I would just use something like

=INDEX(SORT(FILTER($B$2:$D$1000,$A$2:$A$1000=A2),1,-1),1,3)

and change the 1000 to a row that is below the max number of rows you would expect, without going over the top.

Yeah, that's what I did. I was just wondering if you could make such a combination.
I always want to be prepared on everything, like exceeding the max. set row

But it's more than good for now.
Thanks for the help.

 

[Fluff]

You're welcome & thanks for the feedback.