Forum Discussion
I need to pull data from a range into another sheet where the value in one column is repeated
I have a workbook with a sheet that contains 100,000 plus rows. I need to pull data from that sheet into another sheet where the value in column E is repeated more than once and column B contains TSC...
VBA:
Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='Excel 12.0; HDR=NO;';Data Source=c:\data.xlsx"
'Conn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='Excel 12.0; HDR=YES;';Data Source=c:\data.xlsx"
sql1="select b.* from [Sheet1$] as b inner join (select f5,sum(instr(f2,""TSC-Distributors"")) from [Sheet1$] group by f5 having(sum(instr(f2,""TSC-Distributors""))>0 and count(f5)>1)) as a on b.f5=a.f5"
msgbox sql1
set Rst=Conn.Execute(sql1)
ActiveCell.CopyFromRecordset Rst
'FilterArr=Rst.getrows()
Conn.close
Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='Excel 12.0; HDR=NO;';Data Source=c:\data.xlsx"
'Conn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='Excel 12.0; HDR=YES;';Data Source=c:\data.xlsx"
sql1="select b.* from [Sheet1$] as b inner join (select f5,sum(instr(f2,""TSC-Distributors"")) from [Sheet1$] group by f5 having(sum(instr(f2,""TSC-Distributors""))>0 and count(f5)>1)) as a on b.f5=a.f5"
msgbox sql1
set Rst=Conn.Execute(sql1)
ActiveCell.CopyFromRecordset Rst
'FilterArr=Rst.getrows()
Conn.close
