linking two sheets in a third sheet

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

Guest

Hey,

I have a problem and want to solve it in excel.
I have two sheets as follows:

Sheet1 : Sheet2:
Nr Name Nr address zip code ...
1 A 1 xx xx
4 X 2 yy yyy
6 Z 3 zz zz
4 ee ee
5 ss ss
6 dd dd

I want to make a third sheet with all the information of sheet 1 and only the informatie of sheet 2 for the lines with an identical number in sheet1 (linking sheet1 and sheet2 via Nr)

Results of sheet 3 :
Nr Name Nr address zip code ...
1 A 1 xx xx
4 X 4 ee ee
6 Z 6 dd dd

So, for all the lines in sheet1, I searched the linked information in sheet2 and shows alle the information per number in sheet3

Anyone an idea how to do this. Maybe I have to write VBA, but I never did that.
An other way is to read all the informatie in Access (but there are to much sheets to link and I want to know if there is now way in excel or in vba)

thanks in advance
Nic
 
Hi
try the following
- copy your data from sheet1 to your third sheet
- in column C (cell C1) of sheet3 enter
=VLOOKUP(A1,'sheet2'!$A$1:$D$100,2,0)

and copy down

in D1 enter
=VLOOKUP(A1,'sheet2'!$A$1:$D$100,3,0)
 
One way ..

In Sheet3
------------
Put in A1: =OFFSET(Sheet1!$A$1,ROW(A1)-1,COLUMN(A1)-1)

Copy across to B1, then down as many rows as there is data in Sheet1

Copy the labels in C1, D1, etc from Sheet2 > paste in Sheet3's corresponding
cells

Put in C2:
=IF(ISNA(MATCH($A2,Sheet2!$A:$A,0)),"",OFFSET(Sheet2!$A$1,MATCH($A2,Sheet2!$
A:$A,0)-1,COLUMN(A1)))

Copy C2 across to D2*, then down as many rows as there is data in Sheet1
*or across as many cols as necessary to extract from Sheet2

For a neater look, we can suppress extraneous zeros from showing via:
Tools > Options > View tab > Uncheck "Zero values" > OK

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
Nic said:
Hey,

I have a problem and want to solve it in excel.
I have two sheets as follows:

Sheet1 : Sheet2:
Nr Name Nr address zip code ...
1 A 1 xx xx
4 X 2 yy yyy
6 Z 3 zz zz
4 ee ee
5 ss ss
6 dd dd

I want to make a third sheet with all the information of sheet 1 and only
Click to expand...
the informatie of sheet 2 for the lines with an identical number in sheet1
(linking sheet1 and sheet2 via Nr)
Results of sheet 3 :
Nr Name Nr address zip code ...
1 A 1 xx xx
4 X 4 ee ee
6 Z 6 dd dd

So, for all the lines in sheet1, I searched the linked information in
Click to expand...
sheet2 and shows alle the information per number in sheet3
Anyone an idea how to do this. Maybe I have to write VBA, but I never did that.
An other way is to read all the informatie in Access (but there are to
Click to expand...
much sheets to link and I want to know if there is now way in excel or in
vba)
 
Back
Top