Return data based on two indicators including max date.

[hammcfly]

Hello all, I'm not sure if it is because I am new or just a user error, but I failed twice to post with sample data. I hope this is fairly straightforward, but can attempt to attach again if needed.

I have is a table where the budget amount for multiple projects are updated every few weeks. Essentially I have:

Column A is Project 1, Project 2, Project 3, etc.
Column B is the date the new entry was made.
Column C is the budget amount.

What I need to do is pull the most recent budget number into a unique cell for each project. So the formula would work down the table for all of Project 2, then find the most recent date, and populate the budget amount.

I tried this with only these columns but continued to get stuck with only the top result or largest budget amount. I then tried adding helper columns to avoid the multiple attribute requirements. I now have added:

Column D is an indicator such as 1, 2, etc.
Column E is a helper that is the indicator and the date combined.
Column F is a second helper that uses the value function to ensure the original helper is in fact a number. For example: 2 & 07/01/2022 becomes 244743.

I attempted to use this helper to create a more simple vlookup, however, I am now challenged with the max function and a wild card, for example 2*, so that if Project 2 has entries for 07/01, 07/15, and 08/01, the formula will pick up the most recent date.

I am looking for any solution that will simply allow me to retain all these project updates in a single table, but always pull the most recent entry by project number.

Any help is greatly appreciated!

[ELeGault]

A sample file will definitely help the situation. Follow the yellow banner and we can definitely help you work out a solution.

Blindly...

Sounds like you are saying you have entries for many projects, The project names are listed in Column A
Column B is the entry date, and from what I can tell one max daily?
Finally Column C is an amount of some sort that you wish to return and is the goal of your scenario...

So lets first start by having a location for the Project names... will you do a list at the same time? Or will this be a single entry lookup...

Either way we will start with F2 as Project 1 and if it is to be a list, list the unique names in column F below that (or your place of choosing)
Next we need to get the LAST DATE they were entered. Since you are on an older version of Excel you cannot utilize MAXIFS... However you can still do the same function just nested

Please Login or Register  to view this content.

Again, this assumes F2 is a project name, A is projects and B is Dates

Now we have a Unique Project name as well as a specific date!

Last thing to do is to bring in the amount or "Budget". Lets just sum since there is not more than 1 entry in a day it will only sum itsself but now we dont need 20 helper columns!

Please Login or Register  to view this content.

Again, if you listed all of your projects in the F column, your dates formula can be dragged down to get all the latest dates by project, and the budget or sumifs formula can be dragged down to obtain the latest budget value!


If this is off base, I will need to see the sample file to help further.
Good luck!

[hammcfly]

Thank you for your reply. The problem is that not all projects are entered daily. Project 1 may have updates 2 or times a month. So the formula needs to search for "PROJ1" for example, then search for the most recent date only for "PROJ1", then return that amount. All of my attempts are either always defaulting to the most recent date, which may not correspond to that Project ID or simply returning a zero.

See in attached (hopefully) I need it to search column A and B, but some projects have varying record dates. Also, I was incorrect about my Excel version, I have 2021, however, I ultimately need this to work in Google Sheets so I am looking for the most simple solution I can.

Thanks again!

[oeldere]

H2=IF(COUNTIF($A$2:$A2,$A2)=COUNTIF($A$2:$A$8,$A2),"yes","")

See the attached file.

[hammcfly]

Thank you, I don't see how that solves my original ask however.

What I am trying to do pull the most recent budget number from this table based on the project #.

So in an alternate sheet, I want a formula that would use this table and "PROJ1" to pull the $3M, and in a different cell, use "PROJ4" to pull the $1M.

Preferably, the formula would incorporate the term "PROJ1" or another helper, so that as other projects are added I can simply copy the formula and replace that indicator.

Thanks.

[oeldere]

How about a filter on column H on the criteria "yes".

[hammcfly]

This is just a sample set of data, the actual will be hundreds of rows so I cannot use a limited block.

Also, when I added a new line for 8/1 in your sample reply, PROJ1 now showed yes in both the 7/31 and 8/1 line.

I need it to simply find the newest date associated with PROJ1 and return the budget number on that line.

[oeldere]

With a pivot table.

See the attached file.

[hammcfly]

I'm not sure how that helps. I need a formula that will search columns A & B and return C.

Really I'd like to just use a sumifs but I can't figure out the max date part.

[oeldere]

Column C (based on what criteria / data)?

[ELeGault]

Thank you for your reply. The problem is that not all projects are entered daily. Project 1 may have updates 2 or times a month. So the formula needs to search for "PROJ1" for example, then search for the most recent date only for "PROJ1", then return that amount. All of my attempts are either always defaulting to the most recent date, which may not correspond to that Project ID or simply returning a zero.

See in attached (hopefully) I need it to search column A and B, but some projects have varying record dates. Also, I was incorrect about my Excel version, I have 2021, however, I ultimately need this to work in Google Sheets so I am looking for the most simple solution I can.

Thanks again!

Did you attempt to use what I provided?

I opened your document, put Proj1 - Proj4 into J2 through J5
Added my formulas I gave you to get the most recent date BY PROJECT first in column K

Please Login or Register  to view this content.

Then in column L the Associated Budget for that Proj/Date pairing

Please Login or Register  to view this content.

Which will ALWAYS be the accurate one.

Now if you want BUDGET 2 on your sheet to always be a updated "Latest" added budget we can nest the last date into the second formula and make it part of the table.. so in G2

Please Login or Register  to view this content.

Regardless of choice... what I previously provided does pull the LATEST Budget added by project

[hammcfly]

Sorry for the delayed reply, I was pulled into other priorities.

When I attempt your formula for most recent date, it just returns all zeros.

[hammcfly]

I think I just got it to work, thank you SO MUCH for your help!

[hammcfly]

I spoke too soon. Working in excel, not working in google sheets.