Keep each product's latest record
FormulasEasyDeterministic scoringLatest run
Passed by all 12 products on their best model.
Prompt
On the Report sheet, keep one row per product using its latest record and list the products alphabetically. Put the headers in row 1 and use a formula starting in A2.
Data
Starting sheet| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-01 | 8100 |
| 3 | Beta | 2025-03-01 | 6200 |
| 4 | Alpha | 2025-03-05 | 8300 |
| 5 | Gamma | 2025-03-02 | 7100 |
| 6 | Beta | 2025-03-07 | 6450 |
| 7 | Gamma | 2025-03-08 | 7500 |
| 8 | Delta | 2025-03-03 | 9200 |
| 9 | Delta | 2025-03-09 | 9400 |
| 10 | Alpha | 2025-02-20 | 7900 |
| 11 | Beta | 2025-02-21 | 6000 |
Report
Starting sheet| A | |
|---|---|
| 1 |
A passing output
Autosheet in Excel · Opus 5.5A2=LET(_xlpm.d, _xlws.FILTER(Data!A2:C1000, Data!A2:A1000<>""), _xlpm.s, SORTBY(_xlpm.d, INDEX(_xlpm.d,,1), 1, INDEX(_xlpm.d,,2), -1), _xlpm.p, INDEX(_xlpm.s,,1), _xlws.FILTER(_xlpm.s, _xlpm.p<>VSTACK("", DROP(_xlpm.p,-1))))
| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8300 |
| 3 | Beta | 2025-03-07 | 6450 |
| 4 | Delta | 2025-03-09 | 9400 |
| 5 | Gamma | 2025-03-08 | 7500 |
Pass criteria
All must be met, checked automatically.Report!A2:C5hold the expected valuesReport!A2uses a formula
Passed 22
Autosheet in Excel Opus 5.5 · Medium
A2=LET(_xlpm.d, _xlws.FILTER(Data!A2:C1000, Data!A2:A1000<>""), _xlpm.s, SORTBY(_xlpm.d, INDEX(_xlpm.d,,1), 1, INDEX(_xlpm.d,,2), -1), _xlpm.p, INDEX(_xlpm.s,,1), _xlws.FILTER(_xlpm.s, _xlpm.p<>VSTACK("", DROP(_xlpm.p,-1))))
| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8300 |
| 3 | Beta | 2025-03-07 | 6450 |
| 4 | Delta | 2025-03-09 | 9400 |
| 5 | Gamma | 2025-03-08 | 7500 |
Autosheet in Excel GPT-6 Astra · Low
A2=LET(_xlpm.records,SORTBY(Data!A2:C11,Data!B2:B11,-1,ROW(Data!A2:A11),-1),_xlpm.products,INDEX(_xlpm.records,0,1),_xlws.SORT(_xlws.FILTER(_xlpm.records,XMATCH(_xlpm.products,_xlpm.products)=SEQUENCE(ROWS(_xlpm.records))),1,1))
| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8,300.00 |
| 3 | Beta | 2025-03-07 | 6,450.00 |
| 4 | Delta | 2025-03-09 | 9,400.00 |
| 5 | Gamma | 2025-03-08 | 7,500.00 |
Autosheet in Sheets Opus 5.5 · Medium
A2=IFERROR(__xludf.DUMMYFUNCTION("SORT(SORTN(SORT(FILTER(Data!A2:C1000, Data!A2:A1000<>""""), 2, FALSE), 9^9, 2, 1, TRUE), 1, TRUE)"),"Alpha")
| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8300 |
| 3 | Beta | 2025-03-07 | 6450 |
| 4 | Delta | 2025-03-09 | 9400 |
| 5 | Gamma | 2025-03-08 | 7500 |
Autosheet in Excel Sonnet 5.5 · Medium
A2=LET(_xlpm.s,SORTBY(Data!A2:C11,Data!B2:B11,-1),_xlpm.u,_xlws.SORT(UNIQUE(Data!A2:A11)),HSTACK(_xlpm.u,XLOOKUP(_xlpm.u,INDEX(_xlpm.s,,1),INDEX(_xlpm.s,,2)),XLOOKUP(_xlpm.u,INDEX(_xlpm.s,,1),INDEX(_xlpm.s,,3))))
| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8300 |
| 3 | Beta | 2025-03-07 | 6450 |
| 4 | Delta | 2025-03-09 | 9400 |
| 5 | Gamma | 2025-03-08 | 7500 |
Claude in Excel Opus 5.5 · High
A2=LET(_xlpm.p,Data!A2:A11,_xlpm.d,DATEVALUE(Data!B2:B11),_xlpm.r,Data!C2:C11,_xlpm.u,_xlws.SORT(UNIQUE(_xlpm.p)),_xlpm.ld,MAP(_xlpm.u,LAMBDA(_xlpm.x,MAX(_xlws.FILTER(_xlpm.d,_xlpm.p=_xlpm.x)))),_xlpm.rev,XLOOKUP(_xlpm.u&"|"&_xlpm.ld,_xlpm.p&"|"&_xlpm.d,_xlpm.r),HSTACK(_xlpm.u,_xlpm.ld,_xlpm.rev))
| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8300 |
| 3 | Beta | 2025-03-07 | 6450 |
| 4 | Delta | 2025-03-09 | 9400 |
| 5 | Gamma | 2025-03-08 | 7500 |
Autosheet in Excel Haiku 5.5 · High
A2=INDEX(_xlws.SORT(UNIQUE(Data!$A$2:$A$11)),ROWS($A$2:A2))
| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8300 |
| 3 | Beta | 2025-03-07 | 6450 |
| 4 | Delta | 2025-03-09 | 9400 |
| 5 | Gamma | 2025-03-08 | 7500 |
Claude in Sheets Opus 5.5 · Medium
A2=IFERROR(__xludf.DUMMYFUNCTION("SORTN(SORT(FILTER(Data!A2:C1000, LEN(Data!A2:A1000)), 1, TRUE, 2, FALSE), 9^9, 2, 1, TRUE)"),"Alpha")
| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8300 |
| 3 | Beta | 2025-03-07 | 6450 |
| 4 | Delta | 2025-03-09 | 9400 |
| 5 | Gamma | 2025-03-08 | 7500 |
Claude Code + Sheets MCP Opus 5.5 · Medium
A2=IFERROR(__xludf.DUMMYFUNCTION("SORT(SORTN(SORT(FILTER(Data!A2:C1000,Data!A2:A1000<>""""),2,FALSE),9^9,2,1,TRUE),1,TRUE)"),"Alpha")
| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8300 |
| 3 | Beta | 2025-03-07 | 6450 |
| 4 | Delta | 2025-03-09 | 9400 |
| 5 | Gamma | 2025-03-08 | 7500 |
Autosheet in Excel GPT-6.1 Sol · Medium
A2=LET(_xlpm.records,SORTBY(Data!A2:C11,Data!A2:A11,1,Data!B2:B11,-1,ROW(Data!A2:A11),-1),_xlpm.products,INDEX(_xlpm.records,0,1),_xlws.FILTER(_xlpm.records,XMATCH(_xlpm.products,_xlpm.products)=SEQUENCE(ROWS(_xlpm.records))))
| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8,300.00 |
| 3 | Beta | 2025-03-07 | 6,450.00 |
| 4 | Delta | 2025-03-09 | 9,400.00 |
| 5 | Gamma | 2025-03-08 | 7,500.00 |
Claude Code + Autosheet MCP Opus 5.5 · Medium
A2=IFERROR(__xludf.DUMMYFUNCTION("ARRAYFORMULA(LET(src, FILTER(Data!A2:C1000, TRIM(Data!A2:A1000)<>""""), prod, PROPER(TRIM(INDEX(src,,1))), dt, MAP(INDEX(src,,2), LAMBDA(x, IF(ISNUMBER(x), x, IFERROR(DATEVALUE(TRIM(x)), 0)))), rev, INDEX(src,,3), rowNum, SEQUENCE(ROWS(src)), sorted, SORT"&"(HSTACK(prod, dt, rev, rowNum), 1, TRUE, 2, FALSE, 4, FALSE), names, UNIQUE(INDEX(sorted,,1)), HSTACK(names, XLOOKUP(names, INDEX(sorted,,1), INDEX(sorted,,2)), XLOOKUP(names, INDEX(sorted,,1), INDEX(sorted,,3)))))"),"Alpha")
| A | B | C | |
|---|---|---|---|
| 1 | Product | Latest Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8300 |
| 3 | Beta | 2025-03-07 | 6450 |
| 4 | Delta | 2025-03-09 | 9400 |
| 5 | Gamma | 2025-03-08 | 7500 |
ChatGPT + Autosheet MCP GPT-6 · Medium
A2=IFERROR(__xludf.DUMMYFUNCTION("SORT(SORTN(SORT(FILTER(Data!A2:C1000, Data!A2:A1000<>""""), 2, FALSE), 9^9, 2, 1, TRUE), 1, TRUE)"),"Alpha")
| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8300 |
| 3 | Beta | 2025-03-07 | 6450 |
| 4 | Delta | 2025-03-09 | 9400 |
| 5 | Gamma | 2025-03-08 | 7500 |
Autosheet in Sheets Haiku 5.5 · High
A2=IFERROR(__xludf.DUMMYFUNCTION("LET(s, SORT(Data!A2:C11, 1, TRUE, 2, FALSE), FILTER(s, MATCH(INDEX(s,,1), INDEX(s,,1), 0) = SEQUENCE(ROWS(s))))"),"Alpha")
| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8300 |
| 3 | Beta | 2025-03-07 | 6450 |
| 4 | Delta | 2025-03-09 | 9400 |
| 5 | Gamma | 2025-03-08 | 7500 |
ChatGPT in Excel GPT-6 Astra · Medium
A2=LET(_xlpm.records,SORTBY(Data!A2:C11,Data!B2:B11,-1),_xlpm.products,INDEX(_xlpm.records,0,1),_xlws.SORT(_xlws.FILTER(_xlpm.records,XMATCH(_xlpm.products,_xlpm.products)=SEQUENCE(ROWS(_xlpm.records))),1,1))
| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8300 |
| 3 | Beta | 2025-03-07 | 6450 |
| 4 | Delta | 2025-03-09 | 9400 |
| 5 | Gamma | 2025-03-08 | 7500 |
Claude in Excel Haiku 5.5 · High
A2=LET(_xlpm.prod,Data!$A$2:$A$11,_xlpm.dt,DATEVALUE(Data!$B$2:$B$11),_xlpm.rev,Data!$C$2:$C$11,_xlpm.u,_xlws.SORT(UNIQUE(_xlpm.prod)),_xlpm.ld,MAP(_xlpm.u,LAMBDA(_xlpm.p,MAX(IF(_xlpm.prod=_xlpm.p,_xlpm.dt)))),_xlpm.idx,MAP(_xlpm.u,_xlpm.ld,LAMBDA(_xlpm.p,_xlpm.d,XMATCH(1,(_xlpm.prod=_xlpm.p)*(_xlpm.dt=_xlpm.d)))),HSTACK(_xlpm.u,INDEX(Data!$B$2:$B$11,_xlpm.idx),INDEX(_xlpm.rev,_xlpm.idx)))
| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8300 |
| 3 | Beta | 2025-03-07 | 6450 |
| 4 | Delta | 2025-03-09 | 9400 |
| 5 | Gamma | 2025-03-08 | 7500 |
Claude in Sheets Haiku 5.5 · High
A2=IFERROR(__xludf.DUMMYFUNCTION("ARRAYFORMULA(LET(d,FILTER(Data!A2:C1000,Data!A2:A1000<>""""),s,SORT(d,2,FALSE),p,UNIQUE(INDEX(d,,1)),r,MATCH(p,INDEX(s,,1),0),SORT(CHOOSEROWS(s,r),1,TRUE)))"),"Alpha")
| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8300 |
| 3 | Beta | 2025-03-07 | 6450 |
| 4 | Delta | 2025-03-09 | 9400 |
| 5 | Gamma | 2025-03-08 | 7500 |
Autosheet in Excel GPT-6 Luna · High
A2=LET(_xlpm.products,_xlws.SORT(UNIQUE(_xlws.FILTER(Data!A2:A11,Data!A2:A11<>""))),_xlpm.productNames,Data!A2:A11,_xlpm.dates,Data!B2:B11,_xlpm.revenue,Data!C2:C11,_xlpm.latestDates,MAP(_xlpm.products,LAMBDA(_xlpm.product,INDEX(_xlws.SORT(_xlws.FILTER(_xlpm.dates,_xlpm.productNames=_xlpm.product),1,-1),1))),_xlpm.latestRevenue,MAP(_xlpm.products,_xlpm.latestDates,LAMBDA(_xlpm.product,_xlpm.latestDate,XLOOKUP(1,(_xlpm.productNames=_xlpm.product)*(_xlpm.dates=_xlpm.latestDate),_xlpm.revenue))),HSTACK(_xlpm.products,_xlpm.latestDates,_xlpm.latestRevenue))
| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8300 |
| 3 | Beta | 2025-03-07 | 6450 |
| 4 | Delta | 2025-03-09 | 9400 |
| 5 | Gamma | 2025-03-08 | 7500 |
Copilot in Excel Auto
A2=LET(_xlpm.p,Data!A2:A11,_xlpm.d,Data!B2:B11,_xlpm.r,Data!C2:C11,_xlpm.dn,IF(ISNUMBER(_xlpm.d),_xlpm.d,DATEVALUE(_xlpm.d)),_xlpm.u,_xlws.SORT(UNIQUE(_xlws.FILTER(_xlpm.p,_xlpm.p<>""))),_xlpm.md,MAP(_xlpm.u,LAMBDA(_xlpm.x,MAX(_xlws.FILTER(_xlpm.dn,_xlpm.p=_xlpm.x)))),HSTACK(_xlpm.u,_xlpm.md,MAP(_xlpm.u,_xlpm.md,LAMBDA(_xlpm.x,_xlpm.y,XLOOKUP(1,(_xlpm.p=_xlpm.x)*(_xlpm.dn=_xlpm.y),_xlpm.r)))))
| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8,300 |
| 3 | Beta | 2025-03-07 | 6,450 |
| 4 | Delta | 2025-03-09 | 9,400 |
| 5 | Gamma | 2025-03-08 | 7,500 |
ChatGPT + Drive MCP GPT-6 · Medium
A2=IFERROR(__xludf.DUMMYFUNCTION("SORT(SORTN(SORT(FILTER(Data!A2:C1000,Data!A2:A1000<>""""),2,FALSE),9^9,2,1,TRUE),1,TRUE)"),"Alpha")
| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8300 |
| 3 | Beta | 2025-03-07 | 6450 |
| 4 | Delta | 2025-03-09 | 9400 |
| 5 | Gamma | 2025-03-08 | 7500 |
ChatGPT in Excel GPT-6 Luna · High
A2=LET(_xlpm.d,_xlws.FILTER(Data!A2:C1000,Data!A2:A1000<>""),_xlpm.s,SORTBY(_xlpm.d,CHOOSECOLS(_xlpm.d,2),-1),_xlpm.p,_xlws.SORT(UNIQUE(CHOOSECOLS(_xlpm.d,1))),HSTACK(_xlpm.p,XLOOKUP(_xlpm.p,CHOOSECOLS(_xlpm.s,1),CHOOSECOLS(_xlpm.s,2)),XLOOKUP(_xlpm.p,CHOOSECOLS(_xlpm.s,1),CHOOSECOLS(_xlpm.s,3))))
| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8300 |
| 3 | Beta | 2025-03-07 | 6450 |
| 4 | Delta | 2025-03-09 | 9400 |
| 5 | Gamma | 2025-03-08 | 7500 |
Gemini in Sheets Auto
A2=IFERROR(__xludf.DUMMYFUNCTION("SORTN(SORT(FILTER(Data!A2:C1000, Data!A2:A1000<>""""), 2, FALSE), 9^9, 2, 1, TRUE)"),"Alpha")
| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8,300 |
| 3 | Beta | 2025-03-07 | 6,450 |
| 4 | Delta | 2025-03-09 | 9,400 |
| 5 | Gamma | 2025-03-08 | 7,500 |
ChatGPT in Sheets GPT-6 Astra · Medium
A2=IFERROR(__xludf.DUMMYFUNCTION("SORT(FILTER(Data!A2:C11,MMULT((Data!A2:A11=TRANSPOSE(Data!A2:A11))*(Data!B2:B11<TRANSPOSE(Data!B2:B11)),ROW(Data!A2:A11)^0)=0),1,1)"),"Alpha")
| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8300 |
| 3 | Beta | 2025-03-07 | 6450 |
| 4 | Delta | 2025-03-09 | 9400 |
| 5 | Gamma | 2025-03-08 | 7500 |
ChatGPT in Sheets GPT-6 Luna · High
A2=IFERROR(__xludf.DUMMYFUNCTION("SORT(UNIQUE(FILTER(Data!A2:A1000,Data!A2:A1000<>"""")))"),"Alpha")
| A | B | C | |
|---|---|---|---|
| 1 | Product | Date | Revenue |
| 2 | Alpha | 2025-03-05 | 8300 |
| 3 | Beta | 2025-03-07 | 6450 |
| 4 | Delta | 2025-03-09 | 9400 |
| 5 | Gamma | 2025-03-08 | 7500 |