Identify row containing max date for multiple rederences

[Corp_exceller]

Hi,

I have a sheet of data containing multiple rows of non distinct application numbers, and columns containing a date and time, which will be different per entry. This is a non distinct set of application numbers which I want to make distinct based on latest date.

*Note there are many other columns in the data set which don’t apply to the problem I’m trying to fix.

I want to identify the row which contains the latest date (for each application number) by putting either true false or any other two binary markings, so that I can filter on the ‘true’s, giving me a leaned out data set.

Anyone know how to do this? Thanks!

 

[vladimiratanasiu]

I suppose I’m wanting the latest dates to be based on the category, but taking into account the code, so that I’m not just left with one latest date for IN and one for OUT. I’d want it per code.

Is it what are you looking for?

Book1
	F	G	H	I
2	Values	Dates	Category	Status
3	AA	6/12/2022	IN
4	CC	6/28/2022	OUT
5	EE	9/1/2022	IN	Latest
6	BB	7/3/2022	IN	Latest
7	EE	7/11/2022	IN
8	VV	9/14/2022	OUT	Latest
9	EE	2/4/2022	OUT	Latest
10	AA	9/22/2022	IN	Latest
11	CC	9/30/2022	OUT	Latest
12	BB	1/7/2022	IN
13	AA	2/3/2022	OUT	Latest
14	VV	4/3/2022	OUT
15	DD	8/5/2022	OUT	Latest
Sheet1

Cell Formulas
Range	Formula
I3:I15	I3	=IF(G3=MAX(IF(F3=$F$3:$F$15,IF(H3=$H$3:$H$15,$G$3:$G$15))),"Latest","")

 

[Corp_exceller]

Yes

Is it what are you looking for?

Book1
	F	G	H	I
2	Values	Dates	Category	Status
3	AA	6/12/2022	IN
4	CC	6/28/2022	OUT
5	EE	9/1/2022	IN	Latest
6	BB	7/3/2022	IN	Latest
7	EE	7/11/2022	IN
8	VV	9/14/2022	OUT	Latest
9	EE	2/4/2022	OUT	Latest
10	AA	9/22/2022	IN	Latest
11	CC	9/30/2022	OUT	Latest
12	BB	1/7/2022	IN
13	AA	2/3/2022	OUT	Latest
14	VV	4/3/2022	OUT
15	DD	8/5/2022	OUT	Latest
Sheet1

Cell Formulas
Range	Formula
I3:I15	I3	=IF(G3=MAX(IF(F3=$F$3:$F$15,IF(H3=$H$3:$H$15,$G$3:$G$15))),"Latest","")

Yes, that is the one! Thanks very much!!!

 

[vladimiratanasiu]

Yes

Yes, that is the one! Thanks very much!!!

You're welcome.

 

[Corp_exceller]

I don't suppose there would be an array type formula that would work for this problemI have?

Identify presence of difference texts across DIFFERENT cells, per group

I have the below data and want to see how I can achieve the last column. Basically wanting to check for text down a column and per code so for example, I'm not looking for cells that contain "A" and "B" in the one cell, I'm looking for "A" and "B" across cells i.e. groups of codes, so that I...

www.mrexcel.com

 

[vladimiratanasiu]

I don't suppose there would be an array type formula that would work for this problemI have?

Identify presence of difference texts across DIFFERENT cells, per group

I have the below data and want to see how I can achieve the last column. Basically wanting to check for text down a column and per code so for example, I'm not looking for cells that contain "A" and "B" in the one cell, I'm looking for "A" and "B" across cells i.e. groups of codes, so that I...

www.mrexcel.com

What's, specifically, your last addressed problem? Is it new / separated , or it relates with the previous one?

 

[Peter_SSs]

What's, specifically, your last addressed problem? Is it new / separated , or it relates with the previous one?

Please ask/answer anything related to post #24 in the thread linked in that post.

 

[vladimiratanasiu]

Please ask/answer anything related to post #24 in the thread linked in that post.

Sorry, but that thread was mentioned in the body of this one. That's why, I considered it as a part of the present topic related with its previous messages, and answered accordingly asking clarifications.

 

[Peter_SSs]

Sorry, but that thread was mentioned in the body of this one.

True, but the consequence of #12 of our Forum Rules is that we do not want the same question running in two places.
In any case, I presumed that if you had followed the link to the other thread you would have seen that the question had already been resolved there by the time you made post #25 here.

 

[Corp_exceller]

@vladimiratanasiu i posted on the thread in the above link and Peter managed to give me an excel solution for this. But unfortunately the AGGREGATE function doesn’t exist in Google sheets so wondered if anyone know of an alternative to AGGREGATE in Google sheets

Google sheets: Identify presence of difference texts across DIFFERENT cells, per group

I have the below data and want to see how I can achieve the last column, in google sheets. I was given an excel solution in a different thread but unfortuunatelt the AGGREGATE function is non existent in google sheets. The excel solution given is: =IF(A2=A1,C1,SUBSTITUTE(B2&" TO...

www.mrexcel.com

 

[vladimiratanasiu]

True, but the consequence of #12 of our Forum Rules is that we do not want the same question running in two places.
In any case, I presumed that if you had followed the link to the other thread you would have seen that the question had already been resolved there by the time you made post #25 here.

True, but it was not me posting the same problem in more threads. I read the content of the related thread, and saw that its answers didn't solve (completly) the Google sheets issues. That's why, I asked more details. They are provided in last answer of Corp_exceller, that can be considered the missing link of our discussion.