I need to pull data from a range into another sheet where the value in one column is repeated

[Parrothead]

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_Distributors as in the example below. In this example I need all four rows even though there are two row that do not have TSC_Distributors in column B. I'm stuck not sure if this can be done.

[#2]

@Parrothead 

=IF(COUNTIFS($E$2:$E$15,E2,$B$2:$B$15,"TSC-Distributors")>0,IFNA(INDEX($H$2:$H$21,MATCH(1,(B2=$I$2:$I$21)*(E2=$L$2:$L$21),0)),INDEX($H$2:$H$21,MATCH(E2,$L$2:$L$21,0))),"")

Perhaps this formula returns the intended result in column A (Original Agent). Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021. For illustration i've made an example within one worksheet. The formulas for columns C and D are similar you can see them in the attached file. The ranges of the formulas can be adapted as required.

[#3]

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

[#4]

@peiyezhu Thank you! This does exactly what I need. I had to use ChatGPT to understand exactly what the VBA was doing as I'm not that familiar with it. I learned something new so thank you for that.

[#5]

good.
My pleasure.