• 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.

Reverse Countifs

rdepuydt

New Member
I have the below columns and I want to find the last occurrence of Yard Zone for each of the specific identifiers and put "Yard Zone" in another column when
it finds the last one.

TF-55143843Yard Zone
TF-55143843Yard Zone
TF-55143843Yard Zone
TF-55143843Yard Zone
TF-55143843Yard Zone
TF-55143843
TF-55143843
TF-55143850
TF-55143850
TF-55143851
TF-55143851
TF-55143852
TF-55143852
TF-55143853
TF-55143853
TF-55143854
TF-55143854
TF-55143854
TF-55143857Yard Zone
TF-55143860Yard Zone
TF-55143860Yard Zone
TF-55143860
TF-55143860
TF-55143860
TF-55143860Yard Zone
TF-55143860
TF-55143861
TF-55143861Yard Zone
 

Attachments

  • MASTER PRODUCTIVITY REPORT Sample.xlsx
    483.2 KB · Views: 7
One way is using aggregate to find the largest position
=IF(AGGREGATE(14,6,ROW($Y$10:$Y$98)/(($Y$10:$Y$98=Y10)*($Z$10:$Z$98="Yard Zone")),1)=ROW(),"Yard Zone","")
 

Attachments

  • Copy of MASTER PRODUCTIVITY REPORT Sample.xlsx
    484.5 KB · Views: 9
@GraH - Guido
Simpler than mine
Code:
= IF(
    ISREF(
      [@location]
      XLOOKUP("Yard Zone", IF([identifier]=[@identifier],[location]), [location], , ,-1)
      ),
    "Yard Zone",
    "..."
    )
I used XLOOKUP to return a reference to the final "Yard Zone" cell and then used ISREF and Range Intersection to determine whether it is the current row or not.
 
I have the below columns and I want to find the last occurrence of Yard Zone for each of the specific identifiers and put "Yard Zone" in another column when
it finds the last one.

TF-55143843Yard Zone
TF-55143843Yard Zone
TF-55143843Yard Zone
TF-55143843Yard Zone
TF-55143843Yard Zone
TF-55143843
TF-55143843
TF-55143850
TF-55143850
TF-55143851
TF-55143851
TF-55143852
TF-55143852
TF-55143853
TF-55143853
TF-55143854
TF-55143854
TF-55143854
TF-55143857Yard Zone
TF-55143860Yard Zone
TF-55143860Yard Zone
TF-55143860
TF-55143860
TF-55143860
TF-55143860Yard Zone
TF-55143860
TF-55143861
TF-55143861Yard Zone

This is awesome! Can you tell me why AC59 - AC69 have #NUM! in them?
 
Because there is no match. Simple solution is using IFERROR ( the formula; "" ). Haseeb's formula avoids that all together. Please test both in terms of speed.
 
Back
Top