Look Up across 2 files

[Heat004]

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.

 

[Blue Dragon]

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]

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]

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]

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]

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 ( ). It should be CELLS not CELL. Sorry.

 

[onlyadrafter]

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]

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.

 

[Heat004]

Thanks - sorted via vlookup - must get my head round macros