• 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


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

proper tagging for each row

Hi Team,

may i seek an assistance with regards to the attached file;
I need to tag each row as "rolled over", "new case" or "handled" accordingly.
these were 2 sets of data; comparison of data produced every two hours
the latest hour contains columns B to E while the previous hour contains only column B to properly segregate each sets
categories for tagging were:
1.) Duplicate Entries on Column B are to be tagged as "rolled over" only on the Latest Hour
2.) Remaining Data (not duplicate) from latest hour are to be tagged "new case"
3.) Remaining Data (not duplicate) from previous hour are to be tagged "handled"

please find attached file and should there a need for clarification, i am more than willing to cooperate. :):):)


Thanks in advance,




Active Member
Does the attached help ?
The formula is =IF(AND(COUNTIF($B$2:$B$1541;$B2)>1;COUNTBLANK($C2:$E2)<>3);"rolled over";IF(AND(COUNTIF($B$2:$B$1541;$B2)=1;COUNTBLANK($C2:$E2)<>3);"new case";"handled"))
( replace the semicolons with comma if needed)


Hi pecoflyer,
this is good, although I have made some revision since when I dragged it downwards, all rows starting from previous hours would show "handled"
below is the revision i have made:
=IF(AND(COUNTIF($B$2:$B$1541,$B2)>1,COUNTBLANK($C2:$E2)<>3),"rolled over",
IF(AND(COUNTIF($B$2:$B$1541,$B2)=1,COUNTBLANK($C2:$E2)<>3),"new case","handled")))

Thanks for the help for I managed to figure it out. I also use the countif but i never remembered the countblank function. I tend to make a helper column using isblank function but got stucked because of too many arguments to use. :):):)

Thanks once again,



Active Member
You're welcome
Just be aware COUNTBLANK counts empty cells AND cells containing a null-string ("") which look empty...
ISBLANK does not recognize null strings