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!

 

[Corp_exceller]

Ok. Try to analyze the formulas and references from this table, adjusting them in your one on the basis of its different structure. Apart from the suggestion of Peter_SSs, you could upload a (larger) section of your table as file in cloud (e.g. Dropbox), and send its generated link in your message. P.S. When apply formulas, take into account all of them to be edited as array type ( Excel Array formula | Exceljet)

Book1
	B	C	D
2	Values	Dates	Status
3	AA	12 June 2022
4	CC	28 June 2022
5	EE	01 September 2022	Latest
6	BB	03 July 2022	Latest
7	EE	11 July 2022
8	VV	14 September 2022	Latest
9	EE	04 February 2022
10	AA	22 September 2022	Latest
11	CC	30 September 2022	Latest
12	BB	07 January 2022
13	AA	03 February 2022
14	VV	03 April 2022
15	DD	05 August 2022	Latest
Sheet1

Cell Formulas
Range	Formula
D15,D3:D13	D3	=IF(C3=MAX(IF(B3=$B$3:$B$15,$C$3:$C$15,"")),"Latest","")
Press CTRL+SHIFT+ENTER to enter array formulas.

I copied exactly what you have and this is what I get. I'm really not sure what the issue is as it is exactly as you've posted

 

[Corp_exceller]

Ok. Try to analyze the formulas and references from this table, adjusting them in your one on the basis of its different structure. Apart from the suggestion of Peter_SSs, you could upload a (larger) section of your table as file in cloud (e.g. Dropbox), and send its generated link in your message. P.S. When apply formulas, take into account all of them to be edited as array type ( Excel Array formula | Exceljet)

Book1
	B	C	D
2	Values	Dates	Status
3	AA	12 June 2022
4	CC	28 June 2022
5	EE	01 September 2022	Latest
6	BB	03 July 2022	Latest
7	EE	11 July 2022
8	VV	14 September 2022	Latest
9	EE	04 February 2022
10	AA	22 September 2022	Latest
11	CC	30 September 2022	Latest
12	BB	07 January 2022
13	AA	03 February 2022
14	VV	03 April 2022
15	DD	05 August 2022	Latest
Sheet1

Cell Formulas
Range	Formula
D15,D3:D13	D3	=IF(C3=MAX(IF(B3=$B$3:$B$15,$C$3:$C$15,"")),"Latest","")
Press CTRL+SHIFT+ENTER to enter array formulas.

Actually, I’m missing curly brackets. I haven’t tried them with the ctrl-shift thing you mentioned so let me try that tomorrow and I’ll let you know how I’ve got on.

 

[Corp_exceller]

Fantastic, that worked! It was my ignorance of the nuance ctrl-shift-Enter, as opposed to just Enter. Thanks a lot!

 

[vladimiratanasiu]

Fantastic, that worked! It was my ignorance of the nuance ctrl-shift-Enter, as opposed to just Enter. Thanks a lot!

You're welcome. Thanks for the follow-up.

 

[Corp_exceller]

Hi,

I have a new question of a very similar nature! Is it possible to do the same as I asked for, but add another column into the mix? So for example, I now want to find latest date based on further broken down data, whilst still ordering by the application number? For example..

Each code can have a duplicated category e.g. AA has two INS and one OUT. I want to pic out the latest IN per code (or OUTS, as the case may be)

 

[vladimiratanasiu]

Hi,

I have a new question of a very similar nature! Is it possible to do the same as I asked for, but add another column into the mix? So for example, I now want to find latest date based on further broken down data, whilst still ordering by the application number? For example..

View attachment 71909

Each code can have a duplicated category e.g. AA has two INS and one OUT. I want to pic out the latest IN per code (or OUTS, as the case may be)

You asked before to be found the most recent day of each month. As result, it's a single day only, not more ones. No matter how many other series of data are added, it can be just one Code / Category etc. result, linked with a single latest day of month. So, you have to decide what series of elements (Values / Category etc.) is accorded with the time, I can't see both working simultaneously.

 

[vladimiratanasiu]

You asked before to be found the most recent day of each month. As result, it's a single day only, not more ones. No matter how many other series of data are added, it can be just one Code / Category etc. result, linked with a single latest day of month. So, you have to decide what series of elements (Values / Category etc.) is accorded with the time, I can't see both working simultaneously.

Or, it is possible by resulting other new Status series, each one specific to every new category that is added.

 

[Corp_exceller]

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

 

[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

Upload, or insert by copy-paste in the body of message, an editable table (not a photo! ) with examples of initial data, final results desired and relationships between them. Your messages and previous attached image are unclear , regarding the content of the new table.

 

[Peter_SSs]

I have a new question ..

Could we have the sample data in a form that we can copy to test with?

Another option (though not quite as good) is simply to copy/paste from your Excel into the forum (include your expected results manually entered)