How do I amalgamate worksheets

[Julia Underwood]

How do I alagamate worksheets with some fields of same data, ie ID No and
name on both sheets but differing other data, so that I have one worksheet
with which to use for merging to Word?

 

[JulieD]

Hi Julia

one way is by using the VLOOKUP function ... say you have
sheet 1

.......A....B........C..........D..................E
1...ID..Name....Age.....Eye Colour..Hair Colour
2...1.....Fred.......33.......Blue..........Need info here
and on sheet 2 you have

........A......B......C.......................D.
1....ID....Name...Hair Colour...Shoe Size

and you want the hair colour and shoe size information on Sheet 1
select the data in sheet 2 (ie from row 2 to the last row) and click IN the
name box (little box with drop down arrow above column A and to the left of
the formula bar) and type a name for the range (i.e. mydata) and PRESS
ENTER

now in cell E2 on Sheet 1 type
=VLOOKUP(A2,mydata,3,0)
this says, lookup the ID number in the mydata range and return the
associated information from the 3rd column of that range where there is an
exact match.

this formula can then be copied down column E

for Shoe size in sheet1!F2 the formula would be
=VLOOKUP(A2,mydata,4,0)

etc

then once you've done all of this, select columns E through to the last used
one in sheet1 and choose copy, then edit / paste special - values. This
will change the formulas into the actual value.

Hope this helps
Cheers
julieD