Formula to find the most recent date based on row of data

grandprix321 · Jun 29, 2019

Hello,

I pulled a lot of data, and I need to mark which one is the most current. Is there a formula I can use to do this? Someone told me I might need to use an array formula.

Essentially I am trying to get a formula that will print the text in yellow in the picture.

https://imgur.com/a/RDJYrSO

Aryatect · Jun 29, 2019

Hi,

I am assuming that in your requirement Column A to G can change and you want to give the status only when all the Columns from A to G form a set:

Book1

A

B

C

D

E

F

G

H

I

J

1

Oper Unit

Proj Code

Supplier Name

Bid Rate Desc

Bid code

Tax Cat

UofM

Supplier Rate

Date

Status

2

FAF

*

Name

DryStart

1000

1

SF

0.72

11/30/2018

Current

3

FAF

*

Name

DryStart

1000

1

SF

0.75

11/15/2018

Past

4

FAF

*

Name

DryStart

1000

1

SF

0.57

11/16/2018

Past

5

FAF

*

Name

DryStart

1000

1

SF

0.47

11/17/2018

Past

6

FAF

*

Name

DryStart

1000

1

SF

0.19

11/18/2018

Past

7

FAF

*

Name

DryStart

1000

1

SF

0.45

11/19/2018

Past

8

FAF

*

Name

DryStart

1000

1

SF

0.43

11/20/2018

Past

9

SAF

*

Name

DryStart

1000

1

SF

0.78

11/21/2018

Current

10

FAF

*

Name

DryStart

1000

1

SF

0.65

11/22/2018

Past

11

FAF

*

Name

DryStart

1000

1

SF

0.22

11/23/2018

Past

12

FAF

*

Name

DryStart

1000

1

SF

0.2

11/24/2018

Past

Sheet6

Cell Formulas
Range	Formula
J2	{=IF(AGGREGATE(14,6,IF(($A$2:$A$12&$B$2:$B$12&$C$2:$C$12&$D$2:$D$12&$E$2:$E$12&$F$2:$F$12&$G$2:$G$12=TEXTJOIN("",FALSE,$A2:$G2)),$I$2:$I$12),1)=$I2,"Current","Past")}
Press CTRL+SHIFT+ENTER to enter array formulas.

If you do not have Excel with TEXTJOIN formula use below:

Book1

J

2

Current

Sheet6

Cell Formulas
Range	Formula
J2	{=IF(AGGREGATE(14,6,IF(($A$2:$A$12&$B$2:$B$12&$C$2:$C$12&$D$2:$D$12&$E$2:$E$12&$F$2:$F$12&$G$2:$G$12=$A2&$B2&$C2&$D2&$E2&$F2&$G2),$I$2:$I$12),1)=$I2,"Current","Past")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Please do note that Array formula will slowdown the calculation in case of a large file.

DanteAmor · Jun 29, 2019

It can be with the "regular"formula Sumproduct

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:79.84px;" /><col style="width:66.53px;" /><col style="width:127.37px;" /><col style="width:96.95px;" /><col style="width:73.19px;" /><col style="width:59.88px;" /><col style="width:47.52px;" /><col style="width:116.91px;" /><col style="width:96.95px;" /><col style="width:90.3px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Oper Unit</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Proj Code</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Supplier Name</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Bid Rate Desc</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Bid code</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Tax Cat</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">UofM</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Supplier Rate</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Date</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Status</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#dbe5f1; ">FAF</td><td style="background-color:#dbe5f1; ">*</td><td style="background-color:#dbe5f1; ">Name</td><td style="background-color:#dbe5f1; ">DryStart</td><td style="background-color:#dbe5f1; text-align:right; ">1000</td><td style="background-color:#dbe5f1; text-align:right; ">1</td><td style="background-color:#dbe5f1; ">SF</td><td style="background-color:#dbe5f1; text-align:right; ">0.72</td><td style="background-color:#dbe5f1; text-align:right; ">30/nov/2019</td><td style="background-color:#dbe5f1; ">Current</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#dbe5f1; ">FAF</td><td style="background-color:#dbe5f1; ">*</td><td style="background-color:#dbe5f1; ">Name</td><td style="background-color:#dbe5f1; ">DryStart</td><td style="background-color:#dbe5f1; text-align:right; ">1000</td><td style="background-color:#dbe5f1; text-align:right; ">1</td><td style="background-color:#dbe5f1; ">SF</td><td style="background-color:#dbe5f1; text-align:right; ">0.75</td><td style="background-color:#dbe5f1; text-align:right; ">15/nov/2019</td><td style="background-color:#dbe5f1; ">Past</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#dbe5f1; ">FAF</td><td style="background-color:#dbe5f1; ">*</td><td style="background-color:#dbe5f1; ">Name</td><td style="background-color:#dbe5f1; ">DryStart</td><td style="background-color:#dbe5f1; text-align:right; ">1000</td><td style="background-color:#dbe5f1; text-align:right; ">1</td><td style="background-color:#dbe5f1; ">SF</td><td style="background-color:#dbe5f1; text-align:right; ">0.57</td><td style="background-color:#dbe5f1; text-align:right; ">16/nov/2019</td><td style="background-color:#dbe5f1; ">Past</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#ffff99; ">XYZ</td><td style="background-color:#ffff99; ">*</td><td style="background-color:#ffff99; ">Name</td><td style="background-color:#ffff99; ">DryStart</td><td style="background-color:#ffff99; text-align:right; ">1000</td><td style="background-color:#ffff99; text-align:right; ">1</td><td style="background-color:#ffff99; ">SF</td><td style="background-color:#ffff99; text-align:right; ">0.47</td><td style="background-color:#ffff99; text-align:right; ">17/nov/2019</td><td style="background-color:#ffff99; ">Past</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#ffff99; ">XYZ</td><td style="background-color:#ffff99; ">*</td><td style="background-color:#ffff99; ">Name</td><td style="background-color:#ffff99; ">DryStart</td><td style="background-color:#ffff99; text-align:right; ">1000</td><td style="background-color:#ffff99; text-align:right; ">1</td><td style="background-color:#ffff99; ">SF</td><td style="background-color:#ffff99; text-align:right; ">0.19</td><td style="background-color:#ffff99; text-align:right; ">18/nov/2019</td><td style="background-color:#ffff99; ">Past</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#ffff99; ">XYZ</td><td style="background-color:#ffff99; ">*</td><td style="background-color:#ffff99; ">Name</td><td style="background-color:#ffff99; ">DryStart</td><td style="background-color:#ffff99; text-align:right; ">1000</td><td style="background-color:#ffff99; text-align:right; ">1</td><td style="background-color:#ffff99; ">SF</td><td style="background-color:#ffff99; text-align:right; ">0.45</td><td style="background-color:#ffff99; text-align:right; ">19/nov/2019</td><td style="background-color:#ffff99; ">Current</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#ffff99; ">XYZ</td><td style="background-color:#ffff99; ">*</td><td style="background-color:#ffff99; ">Name</td><td style="background-color:#ffff99; ">DryStart</td><td style="background-color:#ffff99; text-align:right; ">1000</td><td style="background-color:#ffff99; text-align:right; ">1</td><td style="background-color:#ffff99; ">SF</td><td style="background-color:#ffff99; text-align:right; ">0.43</td><td style="background-color:#ffff99; text-align:right; ">15/nov/2019</td><td style="background-color:#ffff99; ">Past</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#ffff99; ">XYZ</td><td style="background-color:#ffff99; ">*</td><td style="background-color:#ffff99; ">Name</td><td style="background-color:#ffff99; ">DryStart</td><td style="background-color:#ffff99; text-align:right; ">1000</td><td style="background-color:#ffff99; text-align:right; ">1</td><td style="background-color:#ffff99; ">SF</td><td style="background-color:#ffff99; text-align:right; ">0.78</td><td style="background-color:#ffff99; text-align:right; ">16/nov/2019</td><td style="background-color:#ffff99; ">Past</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#ccc0da; ">DAM</td><td style="background-color:#ccc0da; ">*</td><td style="background-color:#ccc0da; ">Name</td><td style="background-color:#ccc0da; ">DryStart</td><td style="background-color:#ccc0da; text-align:right; ">1000</td><td style="background-color:#ccc0da; text-align:right; ">1</td><td style="background-color:#ccc0da; ">SF</td><td style="background-color:#ccc0da; text-align:right; ">0.65</td><td style="background-color:#ccc0da; text-align:right; ">17/nov/2019</td><td style="background-color:#ccc0da; ">Past</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#ccc0da; ">DAM</td><td style="background-color:#ccc0da; ">*</td><td style="background-color:#ccc0da; ">Name</td><td style="background-color:#ccc0da; ">DryStart</td><td style="background-color:#ccc0da; text-align:right; ">1000</td><td style="background-color:#ccc0da; text-align:right; ">1</td><td style="background-color:#ccc0da; ">SF</td><td style="background-color:#ccc0da; text-align:right; ">0.22</td><td style="background-color:#ccc0da; text-align:right; ">20/nov/2019</td><td style="background-color:#ccc0da; ">Current</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#ccc0da; ">DAM</td><td style="background-color:#ccc0da; ">*</td><td style="background-color:#ccc0da; ">Name</td><td style="background-color:#ccc0da; ">DryStart</td><td style="background-color:#ccc0da; text-align:right; ">1000</td><td style="background-color:#ccc0da; text-align:right; ">1</td><td style="background-color:#ccc0da; ">SF</td><td style="background-color:#ccc0da; text-align:right; ">0.2</td><td style="background-color:#ccc0da; text-align:right; ">19/nov/2019</td><td style="background-color:#ccc0da; ">Past</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >J2</td><td >=IF(SUMPRODUCT(MAX(($A$2:$A$12=A2)*($B$2:$B$12=B2)*($C$2:$C$12=C2)*($D$2:$D$12=D2)*($E$2:$E$12=E2)*($F$2:$F$12=F2)*($G$2:$G$12=G2)*($I$2:$I$12)))=I2,"Current","Past")</td></tr></table></td></tr></table>

Note: Sumproduc also evaluates an array of data, but does not require Shift + Control + Enter to be accepted.

lrobbo314 · Jun 29, 2019

Power Query version.

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    ToDate = Table.TransformColumnTypes(#"Added Index",{{"Oper Unit", type text}, {"Proj  Code", type text}, {"Supplier Name", type text}, {"Bid Rate Desc", type text}, {"Bid code", Int64.Type}, {"Tax Cat", Int64.Type}, {"UofM", type text}, {"Supplier Rate", type number}, {"Date", type date}}),
    Group = Table.Group(ToDate, {"Oper Unit"}, {{"Custom", each _, type table}}),
    MaxDate = Table.AddColumn(Group, "MaxDate", each Table.Max([Custom],"Date")),
    ExpandTable = Table.ExpandTableColumn(MaxDate, "Custom", {"Proj  Code", "Supplier Name", "Bid Rate Desc", "Bid code", "Tax Cat", "UofM", "Supplier Rate", "Date", "Index"}, {"Proj  Code", "Supplier Name", "Bid Rate Desc", "Bid code", "Tax Cat", "UofM", "Supplier Rate", "Date", "Index"}),
    ExpandRecord = Table.ExpandRecordColumn(ExpandTable, "MaxDate", {"Date"}, {"Date.1"}),
    Status = Table.AddColumn(ExpandRecord, "Status", each if [Date] = [Date.1] then "Current" else "Past"),
    Sort = Table.Sort(Status,{{"Index", Order.Ascending}}),
    Remove = Table.RemoveColumns(Sort,{"Date.1", "Index"})
in
    Remove

Formula to find the most recent date based on row of data

grandprix321

New Member

Excel Facts

Aryatect

Active Member

DanteAmor

Well-known Member

lrobbo314

Well-known Member

Similar threads

Forum statistics

Share this page

Formula to find the most recent date based on row of data

grandprix321

New Member

Excel Facts

Aryatect

Active Member

DanteAmor

Well-known Member

lrobbo314

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock