simple guide to merging 2 worksheets into one request

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I am a basic excel user with little knowledge! I have been asked to merge 2
worksheets together using a unique ID as the method to combine data, for
example,

WS 1 - membership no - address - phone no
WS 2 - membership no - name - date of birth

Required WS3 - membership no - name -address -phone no - date of birth

I have found lots of VB code but to be honest I have no idea what it is,
where it needs to go on the workbook or how to even start to use it. Is
there a very simple way to achieve this other than cut and paste (I have over
10k records)? If someone code provide an idiots guide to the solution I
would be very grateful.

Many thanks in advance

Reg
 
Hi Reg

one method is to use VLOOKUP to do this
copy the membership number column from worksheet1 to column A of worksheet 3
then column B of worksheet 3 gets the following formula
=VLOOKUP(A2,Sheet2!$A$1:$C$10000,2,0)
this says, lookup the value in A2 in the range A1:B10000 on sheet2 and
return the related information from column B where there is an exact match
column C of worksheet 3 gets the following formula
=VLOOKUP(A2,Sheet1!$A$1:$C$10000,2,0)
column D of worksheet 3 gets
=VLOOKUP(A2,Sheet1!$A$1:$C$10000,3,0)
column E gets
=VLOOKUP(A2,Sheet2!$A$1:$C$10000,3,0)

then fill down (easiest way, is to click on cell B2 of sheet3, move mouse
over bottom right hand of cell until you see a + then double click - repeat
for C2, D2 & E2)

now select all of sheet3 and copy then edit / paste special - values
(if your system is struggling with so many formulas and so many rows of
data - do each column separately, -ie do the VLOOKUP for the column, fill
down, do the copy, paste special values .. move to next column)

Hope this helps
Cheers
JulieD
 
Hi Julie, Many thanks for your response, I managed to get the example working
easily! I am now trying to apply the theory to the real spreadsheet and was
wondering if you (or someone) could confirm that I have a correct
understanding of all the component parts of the formula please

=VLOOKUP(A2,Sheet2!$A$1:$C$10000,2,0) this says, lookup the value in A2 in
the range A1:B10000 on sheet2 and return the related information from column
B where there is an exact match.

I understand this bit, the above explanation is for the following sections
of the formula (?) =VLOOKUP(A2,Sheet2!$A$1:$C$10000

Can you please let me know what the remaining ,2,0) values do?

Sorry if these are simple questions!

Many thanks

Reg
 
Hi Reg

always feel free to ask questions... that's what these ngs are all about
:) - the formula can be broken down as follows:

=VLOOKUP(A2, - lookup the value in A
Sheet2!$A$1:$C$10000, - in the range A1:B10000 on sheet2
2, - return the related information from column B (ie the 2nd table of the
column, this parameter is the column index number of the column with the
information that you actually want)
0) - where there is an exact match (the default is TRUE .. meaning an
approximate match .. if you want an exact match you can use either FALSE or
0 as the fourth parameter).

Hope this helps
Cheers
JulieD
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I mail merge using multiple worksheets? 1
How to create duplicate labels with mail merge? 3
Add standard header and footer to new worksheet 2
merge 2 files 3
Date formatting from user form to worksheet 6
Merging Data Into Spreadsheet 2
Merging worksheets together 1
How do I merge 2 worksheets into one consolidated worksheet? 1

Back
Top