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

Copper Contributor

# 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_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.

4 Replies

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

=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.

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

VBA:

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

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

@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.

good.
My pleasure.