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!

No comments:

Post a Comment