Feb 17 2018
08:03 AM
- last edited on
Jul 25 2018
11:05 AM
by
TechCommunityAP
Feb 17 2018
08:03 AM
- last edited on
Jul 25 2018
11:05 AM
by
TechCommunityAP
I'm using the following formula in column I to provide me with a value ("1") for every unique instance in column G. The formula actually works great, except for one thing. In cell I88 it gives me a value of 1, with no other cells beyond with the same. Ultimately, I end up with an additional 1 in column I (Cell I88) to count than I need (my total range is I7:I1000, and G7:G1000 respectively, and the formula in Column I below should stop producing a value, or 1, at I87). I'm guessing the formula is seeing either a text value or numeric value in column G as it contains an "=LEFT" formula all of the way down the entire column, but with no actual value showing. Any help given is greatly appreciated.
'=IF(COUNTIF($G$7:$G$1000,G87)>1,IF(COUNTIF($G$7:G87,G87)=1,"1",""),IF(COUNTIF($G$7:$G$1000,G87)=1,IF(COUNTIF($G$7:G87,G87)=1,"1","")))
Feb 17 2018 08:39 AM
Kelly,
check LEN(G88).
If it is >0 then there is something in the cell.
Feb 17 2018 08:44 AM - edited Feb 17 2018 08:46 AM
Hi Kelly,
Sorry, but your explanation of the issue isn't clear enough!
Anyway, I have these two formulas for you which could help you:
Use this if you want to hide all the duplicate values including the first instance:
=IF(COUNTIF($G$7:$G$1000,G7)=1,1,"")
And this if you want to hide all the duplicate values excluding the first instance:
=IF(COUNTIF($G$7:G7,G7)=1,1,"")
Feb 17 2018 04:40 PM
My apologies - please see below, and attached, for a better explanation of my issue:
Ultimately, no matter where my formula stops in Column I, I would like to eliminate the extra “1” in the following cell. An example would be if my last matching value is in Cell G87, I would like to get rid of the extra (erroneous) value of “1” in Cell I88.
Again, thank you.
Feb 17 2018 05:37 PM
Kelly,
G88 houses the first instance of the null value thus calculating a 1.
Why don't you use Haythams suggestion with a little extra:
=IF(AND(G7<>"",COUNTIF(G$7:G7,G7)=1),1,"")
Feb 17 2018 06:40 PM - edited Feb 17 2018 07:02 PM
As Haytham and Detlef suggested, I will use something like
=IF(AND(LEN(G7)>0,<Condition>), 1, "")
In addition, if you do not care about the actual count of the occurances, but just the first occurance, you may try to compare the current row with the matching row in column G (the row of first occurance should be less than the current row).
=IFERROR(IF(AND(LEN(G7)>0,MATCH(G7,$G$7:$G7,0)+6=ROW(G7)),1,""), "")
If you have 1000 rows, I think using MATCH function will have better performance than using COUNTIF function.
Feb 18 2018 05:16 AM
Thank you Willy, Haytham, and Detlef, I greatly appreciate all of your assistance and time.