Look Up across 2 files

Heat004

New Member
Joined
Jan 6, 2004
Messages
8
I have a set of address on two Files, one file holds a complete list, other holds a subset of those in the complete list. The addresses are in columns, with number in first column then street name in adjacent column. There is data specific to each address in additional columns on the same row.

Using the subset sheet address list I want to look up the complete list and copy across data back into the subset sheet. Data is held in 3 cells in the same row as the address.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Blue Dragon

Board Regular
Joined
Jun 26, 2003
Messages
162
Okay, let's assume for the purposes of this that the sheets are called FULL and PARTIAL, and that the data you want to copy is in columns D, E and F.


Then we can use the following macro:

Sub dat_add()
with sheets("PARTIAL")
for r = 1 to .Range("A65535").End(xlUp).row
temp = .cell(r,1) & .cell(r,2)
k = 1
temp2 = ""
do until temp2 = temp or k >= Sheets("FULL").Range("A65535").End(xlUp).row
temp2 = sheets("FULL").cell(k, 1) & sheets("FULL").cell(k,2)
k=k+1
loop
if temp = temp2 then
.cell(r, 4) = sheets("FULL").cell(k-1, 4) 'Column D
.cell(r, 5) = sheets("FULL").cell(k-1, 5) 'Column E
.cell(r, 6) = sheets("FULL").cell(k-1, 6) 'Column F
end if
next r
end with
end sub

Obviously change is as needed.
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

To do it without VB you could in the full address file insert a column at D and enter

=A1&B1&C1

copy this down as fas as needed.

on the partial file in D1 enter

=VLOOKUP(A1&B1&C1,FULL!$D:$G,2)

in E1 enter

=VLOOKUP(A1&B1&C1,FULL!$D:$G,3)

in F1 enter

=VLOOKUP(A1&B1&C1,FULL!$D:$G,4).

Copy these down as far as needed.

Change FULL!$D.. to your actual filename, you may need to make other changes as necessay.
 

Blue Dragon

Board Regular
Joined
Jun 26, 2003
Messages
162
I like that idea, although that would need ammending each time another line was added to the FULL sheet, and it would make the file size much larger (for some reason with the way Excel handles VLOOKUP commands - I've had that issue before).

If it's a small file though this shouldn't be a massive issue.
 

Heat004

New Member
Joined
Jan 6, 2004
Messages
8

ADVERTISEMENT

The macro solution is a bit advanced for me at this stage - I put it in and had ' run-time error '438': 'object doesn't support this property or method' on debugging the line temp=.cell(r,1)..... was pointed out.

The vlookup solution is more understandable. I have done the =A1&B1 on both sheets, put the address in column A in both sheets and rewritten formula to =VLOOKUP(PARTIAL!A2,FULL!A2:BI2,2) this works in picking up the data in Full!B1 this copies down well until there is a double entry in the partial sheet - the formual should pick up the data relating to the look up value should it not. When I try fixing the range the result is #NA.

Any thoughts?

Regards,
 

Blue Dragon

Board Regular
Joined
Jun 26, 2003
Messages
162
Heat004 said:
The macro solution is a bit advanced for me at this stage - I put it in and had ' run-time error '438': 'object doesn't support this property or method' on debugging the line temp=.cell(r,1)..... was pointed out.

I'm always doing that ( :oops: ). It should be CELLS not CELL. Sorry.
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows

ADVERTISEMENT

Heat004 said:
The macro solution is a bit advanced for me at this stage - I put it in and had ' run-time error '438': 'object doesn't support this property or method' on debugging the line temp=.cell(r,1)..... was pointed out.

The vlookup solution is more understandable. I have done the =A1&B1 on both sheets, put the address in column A in both sheets and rewritten formula to =VLOOKUP(PARTIAL!A2,FULL!A2:BI2,2) this works in picking up the data in Full!B1 this copies down well until there is a double entry in the partial sheet - the formual should pick up the data relating to the look up value should it not. When I try fixing the range the result is #NA.

Any thoughts?

Regards,

Hello,

Sorry I forgot to mention that the FULL sheet should be sorted in ascending order from COlumn D- the =A1&B1 column.
 

Blue Dragon

Board Regular
Joined
Jun 26, 2003
Messages
162
onlyadrafter said:
Sorry I forgot to mention that the FULL sheet should be sorted in ascending order from COlumn D- the =A1&B1 column.

I think between the two of us we've just made the point that pobody's nerfect. :cool:
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,563
Messages
5,765,123
Members
425,262
Latest member
sabry

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
Top