Forum Discussion

Parrothead's avatar
Parrothead
Copper Contributor
Jan 25, 2024

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.

excel
Formulas and Functions

4 Replies

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Jan 26, 2024
    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
    • myester's avatar
      myester
      Copper Contributor
      Jan 27, 2024

      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.

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jan 25, 2024

    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.

Resources