simple guide to merging 2 worksheets into one request

R

Reg

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
 
J

JulieD

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
 
R

Reg

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
 
J

JulieD

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
 
Top