Compare Columns and choose based on third

[dlesny]

Hello.

I have teams inputing data files in SharePoint. They should only input one file per team but sometimes they have more and I would like to catch that and choose the newest one.

As in example below WildCats Team has 2 different "FileName" docs. I would want to disregard  "Assessment - WildCats.xlsx" becouse its "Date" is older.

Date	TeamName	FileName	Tag1	Tag2	Tag3
15/11/2018	WildCats	Assessment - WildCats.xlsx	[Null]	[Null]	[Null]
15/11/2018	WildCats	Assessment - WildCats.xlsx	PDM	[Null]	[Null]
15/11/2018	WildCats	Assessment - WildCats.xlsx	[Null]	[Null]	Dev
15/11/2018	WildCats	Assessment - WildCats.xlsx	[Null]	Tech	[Null]
20/05/2019	WildCats	NEW Assessment - WildCats.xlsx	[Null]	[Null]	[Null]
20/05/2019	WildCats	NEW Assessment - WildCats.xlsx	[Null]	[Null]	[Null]
20/05/2019	WildCats	NEW Assessment - WildCats.xlsx	[Null]	[Null]	[Null]
15/11/2018	Onion Terror	Assessment - Onion Terror.xlsx	[Null]	[Null]	[Null]
15/11/2018	Running With Scisors	Assessment - Running With Scisors.xlsx	[Null]	[Null]	[Null]

[BenMoss]

Hi @dlesny if you convert your date field to be a true date field (using the datetime tool); you can then sort by this date field, and then use the sample tool to select the 1st '1' record, grouping by team name, to return the latest file per team.

I think this is what you are looking for?

Ben

[dlesny]

Hi BenMoss 

Thank you for reply. I got the first step - I need to change the Date format, Thank you. But There will be many lines with the same TeamName for both Files. I need all the lines for the Newes date.

[john_miller9 · Accepted solution]

@dlesnyIf you need all of the records that match the last date for each team, just add a summarize tool to group by Team Name and Max of your new Date field and join back to the original data on Team Name and Max_Date = Date.

[dlesny]

Awesome! Thank you @john_miller9