I posted a question last week and a wonderful person provided me with these two formulas to my problem:

In Excel, it would be this on Sheet2:

Cell A2 (array):
=INDEX(Master1!$B$2:$B$12,MATCH(0,COUNTIF(Master1!$B$2:$B$12,"<"&Master1!$B$2:$B$12),0))

Cell A3 (array):
=IF(COUNTIF(Master1!$B$2:$B$12,">"&Master1!$B$2:$B$12),INDEX(Master1!$B$2:$B$12,MATCH(COUNTIF(Master1!$B$2:$B$12,"<="&A2),COUNTIF(Master1!$B$2:$B$122,"<"&Master1!$B$2:$B$12),0)),"")

the plan was on my sheet 2 the array would search duplicated data on Master1 (sheet 1) and only add its to sheet 2 if and only if its is not already listed on sheet 2. so the first formula set up works great and so does cell A3 formula. However my two problems with A3 formula:1. the master1 has blanks (this is because its a working sheet and data is constantly being added) the second formula (A3) once i drag past all the data entered from master1 it just pulls it again but not all the data. Just chunks. I need the formula in the cell but no data if no data is listed.

2. if i add temp data to master1 and "hide" it using conditional formatting it finds all the actual data and only some of the temp date. the array at this point to run can take up to 6 minutes and locks me out of excel, no good.

if i can get so more assistance that would be great. i estimate that by the end of the year i will have over 2000 entries to calculate the array. so the array needs to be built to accommodate the amount just not all at one time, and does not require the user to drag down the formula past the 2000 row.

i have a short mock up of data on a google doc here: https://docs.google.com/spreadsheets...it?usp=sharing