Friday, January 27, 2017

yogi_Look At Entry In Each Row Of Column D If The Entry Has Not Occured In Rows Above Mark 1 Otherwise Leave Blank

Google Spreadsheet   Post  #2114
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-27-2017
question by Brad O.:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/QCPcSjXiJVQ;context-place=forum/docs
Array Formula for a variable range
I've been trying hard to get an array formula to work for my specific issue and I need some help with it.

I need an array formula that will auto expand into new rows.  The formula needs to search all previous rows only for a value from the new row.  If it's present, insert a 1 into the cell, and if not, do nothing.  Basically, I'm just checking if this is the first time the value appears in the sheet and if so, mark it with a 1.  Future occurrences of the same value would be ignored.

Here's an example of the formula that works, if I manually copy it into each row: 

=IF(countif($D$2:D9,D9)=1,1,"")

I've tried every way I could think of to convert this to a working Array formula that would auto expand into all future rows but I cannot seem to find the right way to do it.

Any suggestions on how to make this happen, either with Array Formula or other ideas would be greatly appreciated!