Lookup without duplicating
 

I'm not sure if I need VBA help or a formula to do this.

On worksheet "A" I have a column of part numbers that may be duplicated or
may not, but each entry has a unique serial number in the preceding column.
At times I need to identify units from another worksheet. "B", to replace
these with. These also may have duplicate part numbers but will have unique
serial numbers.

I want to fill a third column on sheet "A" with serial numbers from "B" by
referencing the part numbers but not repeating any serial numbers.

I hope this makes sense. Here are examples of the sheets:

SHEET "A"
Serial # Part # Replace With
160682 700000
870768 700000
677158 700495
89419 700574
628909 700835
21140 700923
346207 700923
10999 701537
170910 701537
654320 701537
878549 701537
140895 701538


SHEET "B"
Serial # Part #
9000 700000
123154 700000
126466 700495
41112 700574
132164 700835
1213 700923
164564 700924
117762 701537
882154 701537
315547 701538
654321 701661
98765 701665

Thanks for your help and all the great info posted.

Lookup without duplicating
 

Hi Dennis,

See Chip Pearson's duplicate page for a varirty of techniques:

http://www.cpearson.com/excel/duplic...tingDuplicates

See particularly the sections, at the foot of the page, entitled:

Extracting Values Common To Two Lists

and

Extracting Values On One List And Not Another


---
Regards,
Norman



"Dennis S." wrote in message
...
I'm not sure if I need VBA help or a formula to do this.

On worksheet "A" I have a column of part numbers that may be duplicated or
may not, but each entry has a unique serial number in the preceding
column.
At times I need to identify units from another worksheet. "B", to replace
these with. These also may have duplicate part numbers but will have
unique
serial numbers.

I want to fill a third column on sheet "A" with serial numbers from "B" by
referencing the part numbers but not repeating any serial numbers.

I hope this makes sense. Here are examples of the sheets:

SHEET "A"
Serial # Part # Replace With
160682 700000
870768 700000
677158 700495
89419 700574
628909 700835
21140 700923
346207 700923
10999 701537
170910 701537
654320 701537
878549 701537
140895 701538


SHEET "B"
Serial # Part #
9000 700000
123154 700000
126466 700495
41112 700574
132164 700835
1213 700923
164564 700924
117762 701537
882154 701537
315547 701538
654321 701661
98765 701665

Thanks for your help and all the great info posted.

Lookup without duplicating
 

Thanks for the quick response, however I couldn't find exactly what I'm
trying to do at this link.

Maybe I can attack ir from another angle. Is there a way to use IF with
VLOOKUP to prevent duplicate numbers from being returned. The formula would
look at the data returned above the cell and go to the next available match
in the vlookup table? I'm sure the answer is right in front if me, I just
can;t find it!!

Thanks Again,
Dennis

"Norman Jones" wrote:

Hi Dennis,

See Chip Pearson's duplicate page for a varirty of techniques:

http://www.cpearson.com/excel/duplic...tingDuplicates

See particularly the sections, at the foot of the page, entitled:

Extracting Values Common To Two Lists

and

Extracting Values On One List And Not Another


---
Regards,
Norman



"Dennis S." wrote in message
...
I'm not sure if I need VBA help or a formula to do this.

On worksheet "A" I have a column of part numbers that may be duplicated or
may not, but each entry has a unique serial number in the preceding
column.
At times I need to identify units from another worksheet. "B", to replace
these with. These also may have duplicate part numbers but will have
unique
serial numbers.

I want to fill a third column on sheet "A" with serial numbers from "B" by
referencing the part numbers but not repeating any serial numbers.

I hope this makes sense. Here are examples of the sheets:

SHEET "A"
Serial # Part # Replace With
160682 700000
870768 700000
677158 700495
89419 700574
628909 700835
21140 700923
346207 700923
10999 701537
170910 701537
654320 701537
878549 701537
140895 701538


SHEET "B"
Serial # Part #
9000 700000
123154 700000
126466 700495
41112 700574
132164 700835
1213 700923
164564 700924
117762 701537
882154 701537
315547 701538
654321 701661
98765 701665

Thanks for your help and all the great info posted.