Help with formula

[mrdzyyyrawr]

I'm looking for some help. I have some sales data from a report that i plan on running every day. So everyday more data will be added to the bottom of the existing data. I want to have a column on another workbook that will make a new list from certain columns within the data whilst ignoring any duplicates. For example, there are several entries with the same date so I'd like to have a column that auto-populates the date. I'm using a formula at the moment that has 2 problems.

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #006107}span.s2 {color: #ab30d6}span.s3 {color: #a54a29}span.s4 {color: #0057d6}</style>=IFERROR(INDEX(Data!$A$2:$A$999999, MATCH(0,COUNTIF($B$2:B3, Data!$A$2:$A$999999), 0)),"")

1. It takes ages to calculate because there's so many rows of data.
2. At the bottom of my new list I get a 0 instead of just being blank.

When i add the next day's report to the data I want it to automatically add the next date.

Any help would be much appreciated.

 

[AlanY]

1) try to replace the no of rows $A$999999 to the max no in your data. unless you're expecting around 1m rows of data

 

[mrdzyyyrawr]

The number of rows is variable so every day more will be added. Is there a way to get it to ignore any blank rows of data?

 

[AlanY]

to ignore blanks and speed it up you can try a dynamic range like this

=IFERROR(INDEX(INDIRECT("data!$A$2:$A$"&ROW(OFFSET(data!A1,COUNTA(data!A:A)-1,0))),MATCH(0,COUNTIF(data!$A$2:$A$11,"<"&data!$A$2:$A$11)-SUM(COUNTIF(data!$A$2:$A$11,"="&B$1:B1)),0)),"")

confirm with shift-control-enter

 

[mrdzyyyrawr]

Because every blank cell in the data is returned as 0 which always leaves a 0 in my new list. Is there something that can be added to the formula that ignores '0' and leaves is blank?

 

[AlanY]

could you post a small sample of your data here?