Match data from different sheets

Blessy Clara

Board Regular
Joined
Mar 28, 2010
Messages
199
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 CriteriaEmail1Email2Email3Email4....and so on
Org1abc@gmail.comcgd@yeh.comhsjf@iorov.comehajdke.co
Org2andndn@gnand.comand so on
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,237
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Mar 28, 2010
Messages
199
Hi Thank you for that.
I am unable to attach MiniSheet - Here is the Data, Hope this helps
DATA IN SHEET1
IDORG
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
OrgEmail
Maguire & McInerney Lawyerscontacts@mandm.net.au
Maguire & McInerney Lawyersfelix@mandm.net.au
National Institute Of Accountantscontacts@niaaustralia.com.au
National Institute Of Accountantsadmins@niaaustralia.com.au
National Institute Of Accountantssales@niaaustralia.com.au
National Institute Of Accountantsmarketing@niaaustralia.com.au
Southwest Metal Roofing WAcontacts@swmetalroofing.com
Wongutha Christian Aboriginal Schoolcontacts@wonguthacaps.wa.edu.au
Taxi 131-008 Limitedcontactscb20@gmail.com
Professional Public Relations Pty LtdContactSydney@bcw-global.com


Result Required in Sheet 1 : From Column C
IDORG+PCEmail1Email2Email3Email4etc
1​
Maguire & McInerney Lawyerscontacts@mandm.net.aufelix@mandm.net.au
2​
Event Hire Co
3​
Zero One Imaging
4​
National Institute Of Accountantscontacts@niaaustralia.com.auadmins@niaaustralia.com.ausales@niaaustralia.com.aumarketing@niaaustralia.com.au
5​
043 Split Installations-4405
6​
0414Lawyer-2756
7​
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,237
Office Version
  1. 2010
Platform
  1. Windows
According to your attachment a VBA demonstration as a beginner starter to paste to the Sheet1 worksheet module :​
VBA Code:
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
 
Solution

Blessy Clara

Board Regular
Joined
Mar 28, 2010
Messages
199

ADVERTISEMENT

Hi

Marc L

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

Blessy Clara

Board Regular
Joined
Mar 28, 2010
Messages
199
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
 
Last edited:

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,237
Office Version
  1. 2010
Platform
  1. Windows
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 …​
 

Forum statistics

Threads
1,141,060
Messages
5,704,037
Members
421,323
Latest member
Exidous

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top