• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Please Help - Need to Identify the Latest Date

P

phillygirl777

Member
Hello,
I am working on an Excel project where I get rows and rows of data and need to identify the most recent invoice for a given customer. I am looking for a suggestion as to how to get that "latest" indicator out there without writing a macro if possible (this is a shared file, and macros are frowned upon).

Here is a simplified example, and I am looking to populate column E.

Your help is greatly appreciated
 

Attachments

with one Account # and one Account Name
What would be expected valid result in E-column?
If there will be more of those,
what would be expected valid results?
Could You show Your expected results in Your file?
 
In a cell in row 2:
Code: 
=MAXIFS($C$2:$C$23,$B$2:$B$23,B2)=C2
and copied down will show true/false (true if the latest date).
However, this assumes that your 'given customer' is based on column B (AccountName).
If this is not the case, the and the given customer is based on column A then it becomes:
Code: 
=MAXIFS($C$2:$C$23,$A$2:$A$23,A2)=C2

If you have a more recent version of Excel you'l be able to this in a single cell which spills down. In a single cell in row 2:
Code: 
=MAXIFS(C2:C26,A2:A26,A2:A26)=C2:C26
 
vletm said:
with one Account # and one Account Name
What would be expected valid result in E-column?
If there will be more of those,
what would be expected valid results?
Could You show Your expected results in Your file?
Click to expand...
Thank you! Here is the updated file. No, we are talking about a file with thousands of rows with multiple customers with monthly purchases over the period of almost 2 years. The file is currently not sorted.
 

Attachments

vletm said:
Try one of these ... yellows
... with or without table
Click to expand...
Thank you! For some reason, it didn't work in my main file. I tried checking the MAXIFS formula, and for some reason, it always returns 0. I am using Office 365. Are there any add-ins that I need to activate? I am familiar with the MAXIFS function and used it before.

Do I have to use column references as C:.C, or would the absolute reference syntax $C:$C work better?

Thank you
 
phillygirl777 said:
Thank you! For some reason, it didn't work in my main file. I tried checking the MAXIFS formula, and for some reason, it always returns 0. I am using Office 365. Are there any add-ins that I need to activate? I am familiar with the MAXIFS function and used it before.

Do I have to use column references as C:.C, or would the absolute reference syntax $C:$C work better?

Thank you
Click to expand...
Fixed it - one of the columns didn't have the right formats. Thanks again!
 
You must log in or register to reply here.
Back
Top