How to write latest record in Google Sheet formula and autofill?-CodePudding

I have a Google sheet - table 1 below and all the data is given. Table 1 data is people(VenderNo) claim that they want to join event with what data(EventStartDate) and what booth number(BoothNo). So the data is duplicated because people update it with new line and never allowd to delete or update the existing record(table 1).

CodePudding user response：

You don't need a Formula for that. Just select your columns and go to "Data" ->"Sort data"->"advanced sort settings" and then sort by column "EventStartDate" and secondly add sort by another column and add "BoothNo". In the end you can run a data clean up to remove duplicates. You find it also under data.

CodePudding user response：

to get the latest update from your table 1 you can use:

=SORTN(SORT(A2:C, B2:B, 0), 9^9, 2, 1, 1)

for your table 2 try:

={"VN"; INDEX(IFNA(VLOOKUP(E2:E&F2:F, 
 SORTN(SORT({B2:B&C2:C, A2:C}, B2:B, 0), 9^9, 2, 2, 1), 2, 0)))}

CodePudding user response：

Try

={"vendor v2";arrayformula(iferror(
if(E2:E9&"~"&F2:F9=vlookup(E2:E9&"~"&RIGHT(F2:F9,2),sort(arrayformula({B2:B6&"~"&RIGHT(C2:C6,2),B2:B6&"~"&C2:C6,B2:B6,C2:C6,A2:A6}),2,0),2,0),
vlookup(E2:E9&"~"&RIGHT(F2:F9,2),sort(arrayformula({B2:B6&"~"&RIGHT(C2:C6,2),B2:B6&"~"&C2:C6,B2:B6,C2:C6,A2:A6}),2,0),5,0),)))}