Match data from different sheets

[Blessy Clara]

Hi All,

I have an excel with two sheets
Sheet 1 has company information (org, phone, address,etc)
Sheet 2 has emails and org name and domain

I would like to merge the emails from sheet 2 to sheet 1 for specific Match Criteria(Col A: I will be using different combination to match).

The issue is all the organizations have more than one email - I would like each matched email to be placed in adjacent columns for Each Org as given below
Few Organization have more than 100 + emails

Result Table

Match Criteria	Email1	Email2	Email3	Email4	....and so on
Org1	abc@gmail.com	cgd@yeh.com	hsjf@iorov.com	ehajdke.co
Org2	andndn@gnand.com	and so on

 

[Marc L]

Hi,​

​

as we don't have any sheet so an attachment may help to help in particular if you are expecting some VBA solution …​

 

[Blessy Clara]

Hi Thank you for that.
I am unable to attach MiniSheet - Here is the Data, Hope this helps

DATA IN SHEET1
ID	ORG
1​	Maguire & McInerney Lawyers
2​	Event Hire Co
3​	Zero One Imaging
4​	National Institute Of Accountants
5​	043 Split Installations
6​	0414Lawyer
7​
8​

Sheet 2

Org	Email
Maguire & McInerney Lawyers	contacts@mandm.net.au
Maguire & McInerney Lawyers	felix@mandm.net.au
National Institute Of Accountants	contacts@niaaustralia.com.au
National Institute Of Accountants	admins@niaaustralia.com.au
National Institute Of Accountants	sales@niaaustralia.com.au
National Institute Of Accountants	marketing@niaaustralia.com.au
Southwest Metal Roofing WA	contacts@swmetalroofing.com
Wongutha Christian Aboriginal School	contacts@wonguthacaps.wa.edu.au
Taxi 131-008 Limited	contactscb20@gmail.com
Professional Public Relations Pty Ltd	ContactSydney@bcw-global.com

Result Required in Sheet 1 : From Column C

ID	ORG+PC	Email1	Email2	Email3	Email4	etc
1​	Maguire & McInerney Lawyers	contacts@mandm.net.au	felix@mandm.net.au
2​	Event Hire Co
3​	Zero One Imaging
4​	National Institute Of Accountants	contacts@niaaustralia.com.au	admins@niaaustralia.com.au	sales@niaaustralia.com.au	marketing@niaaustralia.com.au
5​	043 Split Installations-4405
6​	0414Lawyer-2756
7​

 

[Marc L]

According to your attachment a VBA demonstration as a beginner starter to paste to the Sheet1 worksheet module :​

Sub Demo1()
        Dim V, W, S$(), R&, X, F%
    With [A1].CurrentRegion.Columns
            V = .Item(2).Value2
            W = Sheet2.[A1].CurrentRegion.Value2
            ReDim S(1 To .Rows.Count, 0)
        For R = 1 To UBound(W)
            X = Application.Match(W(R, 1), V, 0)
            If IsNumeric(X) Then F = 1: S(X, 0) = S(X, 0) & IIf(S(X, 0) > "", vbTab, "") & W(R, 2)
        Next
        If F Then
                  Application.ScreenUpdating = False
                 .Item(3).Value2 = S
                 .Item(3).TextToColumns , 1, , , True
            With .Item(3).Resize(, UsedRange.Columns.Count - 2)
                 .AutoFit
                 .Rows(1).Value2 = Evaluate("""Email""&COLUMN(" & .Rows(1).Address & ")-2")
            End With
                  Application.ScreenUpdating = True
        End If
    End With
End Sub

 

[Blessy Clara]

Hi

Marc L​

Thank you very much. Yes it is the solution I wanted

 

[Blessy Clara]

Hi

@Marc L - I tested this for 100 of records it works. When I ran for 1 Million records it gives me Run time error Type Mismatch 13​

Is there something that has to be changed to make it work . It is an excel sheet with 1M records for which I have to pull emails from sheet 2 data

 

[Blessy Clara]

Never mind, I did it in batches Thanks Marc L

 

[Marc L]

​

Yes 'cause some Excel worksheet functions are limited to 65 536 elements.​

​

If your Excel version is under Windows for a workaround you can link a workbook sample on a files host website like Dropbox for example …​