Merging Two Worksheets

ljknight

Board Regular
Joined
Oct 17, 2002
Messages
52
Hi board:
I have two worksheets: #1 has several columns of employee information, including a column with their email address. Worksheet #2 has same employees responses to a survey and each set of responses has the employee email address. I need to somehow merge these two worksheets so that I have all information on one worksheet (from which I can do a pivot table to divide responses by location, job description, etc.). I know I can match by the employee email address, but how do I do that?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Hi ljknight,

What you describe is ordinarily very easy to do with a few lines of VBA code. I think if you would post the specifics of your problem (i.e., which columns contain email addresses and employee responses, and which columns you want to appear on the final merged sheet), someone (maybe even I) will provide some code that will do the job.

It is also possible to do it entirely with worksheet functions without VBA, by using the MATCH or VLOOKUP function to lookup the email address in worksheet 2 and then index to the employee survey responses you want. But again, a specific example would require knowing something about the layout of worksheet 2.

Damon
 
Upvote 0
Hi Damon:
Thanks for the reply. OK, in worksheet #1 I have Employee Email in Column A, name in B, etc. for various employee info through column I. Worksheet contains 500 rows.
Worksheet #2 has Employee Email in coliumn A then responses to survey questions (all represented by a numeric value 1 - 5) in columns B through EE. Again, 500 rows.
I would like to be able to have sheet #2 say "where is this Email in sheet #1 (i.e. A4), OK, then append this survey data to sheet #1, row 4 starting at J.
So, then I would have all the employee personal data and their survey responses on one sheet and can then do a bunch of Pivot Tables on it.
Possible?
 
Upvote 0
Hi ljknight,

Here is a macro that I believe does what you describe. You should edit the code to insert your worksheet names and the starting row on the first worksheet (I assumed a 1-row header).

Sub XferAnswers()
'Transfers answers for each email address from sheet 2 to sheet 1
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim EmailRng As Range
Dim LastRow As Long
Dim iRow As Long 'row number on worksheet 1
Dim jRow As Long 'row number on worksheet 2

Const FirstRow = 2 'skip row 1 assuming it is a header row.

Set WS1 = Worksheets("Sheet1")
Set WS2 = Worksheets("Sheet2")

LastRow = WS1.[a65536].End(xlUp).Row

Set EmailRng = WS2.Range(WS2.[a1], WS2.Cells(LastRow, 1))

For iRow = FirstRow To LastRow

jRow = Application.WorksheetFunction.Match(WS1.Cells(iRow, 1), EmailRng, 0)

'copy from column B to EE (column 135) to worksheet 1 column J

WS2.Range(WS2.Cells(jRow, 2), WS2.Cells(jRow, 135)).Copy Destination:=WS1.Cells(iRow, 10)

Next iRow

End Sub


If you don't know how to install a VBA macro, it's really
quite easy. Just follow these steps:

1) Go to the Visual Basic Editor (VBE). Do this from Tools >
Macro > Visual Basic Editor (or simply keyboard Alt-TMV)

2) In the VBE create a new Macro Module: Insert > Module.
An empty code window pane will appear (Alt-IM).

3) Paste the code into this window. The macro or function is
now available for use from Excel. If it is a Function type
macro you can immediately use it as an Excel function. If
it is a Sub (subroutine) type macro you can run it from
the Excel Tools > Macro menu (Alt-TMM).

Happy computing.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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
Back
Top