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]

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!

Post your topic backed by a table.

 

[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]

Is that what you want?

 

[vladimiratanasiu]

Book1
	A	B	C
1	Values	Dates	Status
2	dw1	12-Jun
3	dw3	28-Jun
4	dw2	3-Jul
5	dw1	11-Jul	True
6	dw3	14-Sep
7	dw2	22-Sep	True
8	dw3	30-Sep	True
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=IF(B2=MAX(IF(A2=A2:$A$8,B2:$B$8,"")),"True","")

 

[Corp_exceller]

Thanks for this, this looks close. I've tried it but seems to be bringing back nothing. Is there anything I need to do to the data before applying the formula? Also, there are over 3000 rows, around 2200 of which are unique. Does this have any effect on the functioning of the formula?

 

[vladimiratanasiu]

Thanks for this, this looks close. I've tried it but seems to be bringing back nothing. Is there anything I need to do to the data before applying the formula? Also, there are over 3000 rows, around 2200 of which are unique. Does this have any effect on the functioning of the formula?

Be very clear in your message(s), with concrete examples, for them to be properly understood. What do you mean by looking close, but bringing back nothing? Have you adjusted accurately the (absolute) references from formula, to your table ones? Use instead of images the XL2BB add-in ( XL2BB - Excel Range to BBCode ), to copy an editable part of your table with formulas / issues too, and post it in your answer(s).

 

[Corp_exceller]

Be very clear in your message(s), with concrete examples, for them to be properly understood. What do you mean by looking close, but bringing back nothing? Have you adjusted accurately the (absolute) references from formula, to your table ones? Use instead of images the XL2BB add-in ( XL2BB - Excel Range to BBCode ), to copy an editable part of your table with formulas / issues too, and post it in your answer(s).

Apologies! What you did there is exactly what I want but the formula is bringing back blanks, bar one row, which I suspect is the latest date out of all the dates in the spreadsheet. This is having replaced your columns with my actual columns within the formula.

I am unable to upload add-ins as it is blocked by my employer. It seems to not be recognising the value groupings and instead, is flagging the latest date out of all entries.

 

[Peter_SSs]

I am unable to upload add-ins as it is blocked by my employer.

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

 

[vladimiratanasiu]

Apologies! What you did there is exactly what I want but the formula is bringing back blanks, bar one row, which I suspect is the latest date out of all the dates in the spreadsheet. This is having replaced your columns with my actual columns within the formula.

I am unable to upload add-ins as it is blocked by my employer. It seems to not be recognising the value groupings and instead, is flagging the latest date out of all entries.

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.