# Excel Formula for Unique Records Only

• ### Question

• I am writing a vb.net app that uses Imports Microsoft.Office.Interop.Excel.

Example:

```        MyExcel.Workbooks.Open("FileName.xls")
MyExcel.Sheets("Sheet1").activate()
MyExcel.Visible = False

MyExcel.Range("A1").Activate()
MyExcel.Range("A1").NumberFormat = "0"
MyExcel.ActiveCell.Formula = "=COUNTA(A3:A65000)"
TextBox1.Text = MyExcel.Range("A1").Text

MyExcel.ActiveWorkbook.Close(SaveChanges:=False)
MyExcel.Quit()```

I open excel and pass formulas and get data from the spreadsheet.  I need a way to do a Advanced Filer "Unique records only" from a formula.  something like this "Range("A3:B65000").AdvancedFilter Action:=xlFilterInPlace, Unique:=True"  B65000 is max it's sometime 1,000 or 50,000 but in a valid formula not a macro.   any help is greatly appreciated.

Work Smarter Not Harder

Wednesday, January 7, 2015 5:05 PM

• {=COUNT(1/FREQUENCY(IF(A3:A65000&B3:B65000<>"",MATCH(A3:A65000&B3:B65000,A3:A65000&B3:B65000,0)),ROW(A3:A65000)-ROW(A3)+1))}

Work Smarter Not Harder

• Marked as answer by Wednesday, January 7, 2015 7:12 PM
Wednesday, January 7, 2015 7:12 PM

### All replies

• {=COUNT(1/FREQUENCY(IF(A3:A65000&B3:B65000<>"",MATCH(A3:A65000&B3:B65000,A3:A65000&B3:B65000,0)),ROW(A3:A65000)-ROW(A3)+1))}

Work Smarter Not Harder

• Marked as answer by Wednesday, January 7, 2015 7:12 PM
Wednesday, January 7, 2015 7:12 PM
• Thank you for sharing your solution here.

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.