Return Multiple matched Values from different worksheets/ Workbooks

[amit_gupta123]

Hi All

I tried very hard to figure out of fetching multiple match results from different workbooks/ sheets. To brief out, I have named "desired output" having input data in sheet1. There are two different workbooks "Source 1" & Source 2" from which all data to be matched and fetched.

Scenario 2 may be, I have another workbook named " Source 3" which is having combined data of workbooks "Source 1" and "Source 2". Alternatively, "Source 3" can be used to fetch the data from multiple sheets. For better understanding, attaching herewith all the sheets.

Point which is very critical to note that, data runs into lakhs, hence Vlookup and other formulas are way slow and not sufficing the purpose. Though a macro might help in crunching the data and getting the desired result in very less time.

Kindly help

Regards

 

[Marc L]

Hi !​

​

Poor attachment …​

Unclear : what should be done ? Data from Source3 ?​

How many unique 'Lead' ?​

​

 

[p45cal]

Try the button in the attached.
It will ask you to select files one at a time (because order seems to be important and you can't guarantee the order in which files will be processed if you allow files to be multi-selected - this can be done if you want though). You can double-click a file to have it processed and just press the escape key when you have processed all the files you want to.

It assumes that only the first sheet in each file is to be processed.
It won't process the same file twice if you accidentally select the same file a second time.

Having written the macro, I would now do it in a different way, using a dictionary, which would probably be faster and certainly more robust.
If this takes to long still then say so.

I feel something like this might be better solved with Power Query but I'm not good enough at Power Query for that (yet!).

 

[Marc L]

data runs into lakhs

I was thinking about around a short 30 lines code whatever opening a workbook or accessing data via ADODB​

- expecting a clear answer of the way to go if processing multiples files with only a single data worksheet each​

or an unique workbook with several data worksheets -​

and maybe via a Dictionary but it depends on the number of unique Lead as Dictionary is not always the fastest way​

as for huge data when it reaches a level then with more elements it becomes slower & slower and also​

'cause of the Excel TRANSPOSE limit, so after all as I'm not a mind reader … ​

​

As processing under a database software is at least 50 times faster than under Excel …​

​

 

[amit_gupta123]

Regret for late reply Marc....

It would be multiple files with single worksheet (as given in "Source 1 & "Source 2"). You can eliminate "Source 3" file from consideration.

 

[amit_gupta123]

Thanks P45cal for revert...the challenge is, the attached sheet is trying to pull out unique values from all the sheets and filling the values against them. I am trying to lookup and pull out values for specific data set from the multiple sheets

 

[p45cal]

So while my macro returns ALL information from all the sheets (Source 1 and Source 2 or as chosen by the user), you want only a smaller subset ('specific data set' in your message above). That subset is in Source 3 (Just leads ABC and LMN)?

 

[Marc L]

According to the initial post a procedure around 30 codelines already does the job from only Source 1 & 2 files but :​

I am trying to lookup and pull out values for specific data set from the multiple sheets

As I yet wrote :​

as I'm not a mind reader …

So the challenge is you well explain at least all the necessary from 'A to Z', how, where, …​

Your Excel runs under Windows ?​

​

 

[amit_gupta123]

So while my macro returns ALL information from all the sheets (Source 1 and Source 2 or as chosen by the user), you want only a smaller subset ('specific data set' in your message above). That subset is in Source 3 (Just leads ABC and LMN)?

hi...

To clarify, "Desired data" sheet contains the subset and data to be pulled from sheet 1 of "Source1" and "Source2". Kindly ignore "Source 3" sheet.

 

[Marc L]

As explaining a need is at child level, why you did not bring us this information when creating this thread ?‼​

​

According to your 'explanation' and initial attachment (Source 1 & 2 workbooks must be closed),​

better is to paste this one shot starter demonstration (I won't amend it) to the result worksheet module :​

Sub Demo1s()
       Const S = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=#;Extended Properties=""Excel 12.0;HDR=Yes"""
         Dim Dic As Object, V, oCn As Object, N%, F$, C&
    With [A1].CurrentRegion.Rows
         If .Count = 1 Then Beep: Exit Sub
         Set Dic = CreateObject("Scripting.Dictionary")
         For Each V In .Item("2:" & .Count).Columns(1).Value2:  Dic.Add V, "":  Next
        .Offset(1, 1).Clear
    End With
        Set oCn = CreateObject("ADODB.Connection")
    For N = 1 To 2
               F = ThisWorkbook.Path & "\Source " & N & ".xlsx"
        If Dir(F) > "" Then
                 oCn.Open Replace(S, "#", F)
            With oCn.Execute("SELECT * FROM [Sheet1$]")
                V = .GetRows
                    .Close
            End With
                 oCn.Close
            For C = 0 To UBound(V, 2)
                If Dic.Exists(V(0, C)) Then Dic(V(0, C)) = Dic(V(0, C)) & V(1, C) & vbTab
            Next
        End If
    Next
        Set oCn = Nothing
        Application.ScreenUpdating = False
    With [B2].Resize(Dic.Count)
        .Value2 = Application.Transpose(Dic.Items)
        .TextToColumns .Cells(1), xlDelimited, , , True
    End With
        Application.ScreenUpdating = True
        Dic.RemoveAll
    Set Dic = Nothing
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[p45cal]

To clarify, "Desired data" sheet contains the subset and data to be pulled from sheet 1 of "Source1" and "Source2". Kindly ignore "Source 3" sheet.

Far from clarifying, your statement clouds the issue:
There is no "Desired Data" sheet (or workbook). There is a Desired Result workbook containing (apart from two empty sheets) a Sheet1 with some data in it.
As it happens, if you click the button in the file I attached to msg #3 (Chandoo42735DesiredResult.xlsm) and choose only 2 files, Source 1 and Source 2, it produces a new new sheet with EXACTLY THE SAME DATA as Sheet 1 of your Desired Result workbook.

Where, oh where, am I going wrong?

 

[Marc L]

For example remove 'LMN' from the result worksheet and run the procedure : If 'LMN' comes back​

so it's not what is expected as the procedure must return only the results for this column 'Lead'​

instead of all 'Lead' of both source workbooks but yes, the attachment is so poor …​

​