-1

I want duplicates in my data identified. Once I have this data I want to sort the duplicates by date and keep the most recent one.

I cannot not provide a copy of my sheet because of the sensitive information inside.

player0
  • 124,011
  • 12
  • 67
  • 124
retter
  • 23
  • 4
  • Welcome. The question is too broad as it doesn't include enough details. Regarding your sheet, create a demo sheet with fictitious data and also add a brief description of your search/research efforts as is suggested on [ask]. – Rubén Apr 28 '19 at 01:17

1 Answers1

0

do it like this:

=ARRAYFORMULA(IFERROR(VLOOKUP(UNIQUE(B:B), 
 QUERY({A:B}, "select Col2,Col1 
               order by toDate(Col1) desc
               format Col1 'dd/mm/yyyy' ", 0), {2, 1}, 0)))

0

=ARRAYFORMULA(QUERY(IFERROR(VLOOKUP(UNIQUE(QUERY({Data!A:H}, 
 "select Col3
  where toDate(Col5) >= date '"&TEXT(A1, "yyyy-mm-dd")&"' 
    and toDate(Col5) <= date '"&TEXT(B1, "yyyy-mm-dd")&"'
  order by toDate(Col1) desc ", 0)), 
 QUERY({Data!A:H}, 
 "select Col3,Col1,Col2,Col4,Col5,Col6,Col7 
  where toDate(Col5) >= date '"&TEXT(A1, "yyyy-mm-dd")&"' 
    and toDate(Col5) <= date '"&TEXT(B1, "yyyy-mm-dd")&"'
  order by toDate(Col1) desc
  format Col5 'mm/dd/yyyy' ", 0), {2,3,1,4,5,6,7}, 0)),
 "format Col1 'yyyy-nn-dd hh:mm:ss', Col5 'mm/dd/yyyy'"))

0

=ARRAYFORMULA(QUERY(IFERROR(VLOOKUP(UNIQUE(QUERY({Data!A:H}, 
 "select Col2
  where toDate(Col5) >= date '"&TEXT(A1, "yyyy-mm-dd")&"' 
    and toDate(Col5) <= date '"&TEXT(B1, "yyyy-mm-dd")&"'
  order by toDate(Col1) desc ", 0)), 
 QUERY({Data!A:H}, 
 "select Col2,Col1,Col3,Col4,Col5,Col6,Col7 
  where toDate(Col5) >= date '"&TEXT(A1, "yyyy-mm-dd")&"' 
    and toDate(Col5) <= date '"&TEXT(B1, "yyyy-mm-dd")&"'
  order by toDate(Col1) desc
  format Col5 'mm/dd/yyyy' ", 0), {2,1,3,4,5,6,7}, 0)),
 "format Col1 'yyyy-mm-dd hh:mm:ss', Col5 'mm/dd/yyyy'"))
player0
  • 124,011
  • 12
  • 67
  • 124
  • The data is being pulled from the Data Sheet and being imported to Sheet2 and filtered out by the dates listed in row 1. The data that is being imported, I the duplicates removed but keeping the latest date. See the spreadsheet: https://docs.google.com/spreadsheets/d/1X_NBWq1m9iBxvYXhy2wcttHanvPFPlAOJWgbbKbJimQ/edit?usp=sharing – retter Apr 28 '19 at 14:25
  • QUERY(All!A:Z, "select A, B, C, D, E, F, G, H where toDate(E) >= date '"&TEXT(A1, "yyyy-mm-dd")&"' and toDate(E) <= date '"&TEXT(B1, "yyyy-mm-dd")&"' order by (I) desc", (FILTER(A:Z,TRUE) )) – retter Apr 28 '19 at 14:35
  • what is the purpose of "(FILTER(A:Z,TRUE) " ? – player0 Apr 28 '19 at 14:48
  • The purpose of filter was to only show items that were not duplicates. This formula is only showing items that are duplicates. – retter Apr 28 '19 at 18:03
  • @retter did you try the 2nd formula from the answer? if this still not what you seek, go to your sheet you shared and make manually an example of your expected outcome – player0 Apr 28 '19 at 18:06
  • The first formula returns no data. The 2nd formula only returns 7 results. It should return more. The only results that should not show up are the duplicated results. Everything that is not duplicated should be returned. – retter Apr 28 '19 at 20:55
  • which column should be unique and with no duplicates? column with names Mike, Julie, etc. ?? – player0 Apr 28 '19 at 21:11
  • Column B. The projects – retter Apr 28 '19 at 21:35
  • Its showing more results but not all over them. Results should only be removed if there are duplicates. With duplicates the most recent date is kept. I added in green of what the results should be on the sheet. – retter Apr 28 '19 at 22:46
  • it does exactly that but you probably forgot that you also filter by date range (A1, B1) that's why you getting fewer results than you expect. – player0 Apr 29 '19 at 00:19
  • I see. So how do I make it return the results I want? Thanks for all of your help – retter Apr 29 '19 at 00:28
  • widen the range... let A1 be 1/1/2000 and B1 be 1/1/2020 (for example) – player0 Apr 29 '19 at 00:50