Return any customized text for the highest value in a column

[alonc1983]

How to return a value with any text for a highest value in a column and if it is equal to a certain text in another column.

Example.
If column A is equals to ABC and if column B is of highest value, then I can customize the value to be displayed

 

[Legacy 143009]

What do you mean by highest value? Hights number of characters?

 

[Legacy 143009]

Are you looking for something like this?

=MAX(IF($A:$A="ABC",$B:$B))

 

[alonc1983]

So it is going to be like this...

Reason for update	Document Status	Site Code	System Code	Sub System Code	Doc Type	Sequence number	Revision	Document Number
CANCELLED	CLD	200	01	02	DTL	00001	00	200-01-02-DTL-00001_R00
SUPERSEDED	AFC	201	01	02	DTL	00002	00	201-01-02-DTL-00002_R00
SUPERSEDED	AFC	201	01	02	DTL	00002	01	201-01-02-DTL-00002_R01
NEW	AFC	201	01	02	DTL	00002	02	201-01-02-DTL-00002_R02
CANCELLED	CLD	201	01	02	DTL	00003	00	200-01-02-DTL-00001_R00
Please complete Formula	AFC	202	01	02	DTL	00004	00	Please complete Formula
Please complete Formula	AFC	202	01	02	DTL	00004	01	Please complete Formula
Please complete Formula	AFC	202	01	02	DTL	00004	02	Please complete Formula
Please complete Formula	AFC	202	01	02	DTL	00004	03	Please complete Formula
Please complete Formula	AFC	202	01	02	DTL	00004	04	Please complete Formula
Please complete Formula	AFC	202	01	02	DTL	00004	05	Please complete Formula
Please complete Formula	AFC	202	01	02	DTL	00004	06	Please complete Formula
Please complete Formula	AFC	202	01	02	DTL	00004	07	Please complete Formula
Please complete Formula	AFC	202	01	02	DTL	00004	08	Please complete Formula
Formula for A	If B = CLD and I= is any number, A will show "CANCELLED". If B = AFC and I is the last or only revision of the same document number, then A will show "NEW". If B = AFC and I is not the last or only revision of the same document number, then A will show "SUPERSEDED".
Formula for I	reference cell I2 for example.

 

[Legacy 143009]

Column I is easy. Paste this formula to I2:

=TEXTJOIN("-",1,C2,TEXT(D2,"00"),TEXT(E2,"00"),F2,TEXT(G2,"00000")) &"_R"&TEXT(H2,"00")

Column A is quite tricky. Maybe there is a simpler solution. Paste this to A2:

=LET(i,INT($D$2:$D$15&$E$2:$E$15&$G$2:$G$15&$H$2:$H$15),IF(B2="CLD","CANCELED",IF(ROW()-1=MATCH(MAX(($B$2:$B$15=B2)*($C$2:$C$15=C2)*i),i,0),"NEW","SUPERSEEDED")))

 

[alonc1983]

Column I is easy. Paste this formula to I2:

=TEXTJOIN("-",1,C2,TEXT(D2,"00"),TEXT(E2,"00"),F2,TEXT(G2,"00000")) &"_R"&TEXT(H2,"00")

Column A is quite tricky. Maybe there is a simpler solution. Paste this to A2:

=LET(i,INT($D$2:$D$15&$E$2:$E$15&$G$2:$G$15&$H$2:$H$15),IF(B2="CLD","CANCELED",IF(ROW()-1=MATCH(MAX(($B$2:$B$15=B2)*($C$2:$C$15=C2)*i),i,0),"NEW","SUPERSEEDED")))

Thanks a lot! This works really.

 

[Legacy 143009]

Glad it did work! A more cosistent version could be:

=IF(B2="CLD","CANCELED",IF(INT($D$2&$E$2&$G$2&$H$2)=MAX(($B$2:$B$15=B2)*($C$2:$C$15=C2)*INT($D$2:$D$15&$E$2:$E$15&$G$2:$G$15&$H$2:$H$15)),"NEW","SUPERSEEDED"))

But both versions will work OK
Could you please mark as answer if it worked? It was a hard one for me

 

[alonc1983]

Glad it did work! A more cosistent version could be:

=IF(B2="CLD","CANCELED",IF(INT($D$2&$E$2&$G$2&$H$2)=MAX(($B$2:$B$15=B2)*($C$2:$C$15=C2)*INT($D$2:$D$15&$E$2:$E$15&$G$2:$G$15&$H$2:$H$15)),"NEW","SUPERSEEDED"))

But both versions will work OK
Could you please mark as answer if it worked? It was a hard one for me

Yes, this one worked too and I used this instead as this is more simple to understand. Thank you brother