I need help cross referencing...
 

We got a new system at work for inventory and now have new part numbers.
I have a file with the old part numbers and one with the new numbers and need to combine the two in one sheet. For example:
[70250133][SLEEVE,BEARING,5",SAWC,498D772G004] is the old number and part, number being in B1 and part in C1. And on the other file I have
[1055017][SLEEVE,BEARING,5",SAWC,498D772G004] is the new number, number being in
A4251 and part in B4251. Is there a formula to help put these two(and the thousands of other parts) together even being in separate files?

I need help cross referencing...
 

ethyn520 wrote:

We got a new system at work for inventory and now have new part numbers.
I have a file with the old part numbers and one with the new numbers and
need to combine the two in one sheet. For example:
[70250133][SLEEVE,BEARING,5",SAWC,498D772G004] is the old number and
part, number being in B1 and part in C1. And on the other file I have
[1055017][SLEEVE,BEARING,5",SAWC,498D772G004] is the new number, number
being in A4251 and part in B4251. Is there a formula to help put these
two(and the thousands of other parts) together even being in separate
files?

Define "put together". Given those two sample inputs, what do you want the
output to be?

FWIW, several years ago I wrote a fairly short VBA script that did pretty
much what you want, but rather than have duplicate entries for each item
(which I was specifically told to eliminate), it renamed the "ITEMNUMBER"
field in the database to "OLDITEMNUMBER" and created a new "ITEMNUMBER"
field. (Scripted because the item numbers were partly based on the physical
dimensions of the item, which had to be translated from the old format to the
new.)

--
No, let Courier Six carry the package.

I need help cross referencing...
 

On Friday, February 5, 2016 at 1:42:34 AM UTC-6, Auric__ wrote:
ethyn520 wrote:

We got a new system at work for inventory and now have new part numbers.
I have a file with the old part numbers and one with the new numbers and
need to combine the two in one sheet. For example:
[70250133][SLEEVE,BEARING,5",SAWC,498D772G004] is the old number and
part, number being in B1 and part in C1. And on the other file I have
[1055017][SLEEVE,BEARING,5",SAWC,498D772G004] is the new number, number
being in A4251 and part in B4251. Is there a formula to help put these
two(and the thousands of other parts) together even being in separate
files?

Define "put together". Given those two sample inputs, what do you want the
output to be?

FWIW, several years ago I wrote a fairly short VBA script that did pretty
much what you want, but rather than have duplicate entries for each item
(which I was specifically told to eliminate), it renamed the "ITEMNUMBER"
field in the database to "OLDITEMNUMBER" and created a new "ITEMNUMBER"
field. (Scripted because the item numbers were partly based on the physical
dimensions of the item, which had to be translated from the old format to the
new.)

--
No, let Courier Six carry the package.

I need the new part number and the old part number side by side so...
[1055017][70250133][SLEEVE,BEARING,5",SAWC,498D772G004], being in A1-C1.

I need help cross referencing...
 

I need the new part number and the old part number side by side so...
[1055017][70250133][SLEEVE,BEARING,5",SAWC,498D772G004], being in A1-C1.

Also they're in random order on both files. It wouldn't match up if I sorted them in any order and tried to copy and paste, due to new parts in the new item number file as well.

I need help cross referencing...
 

Hi,

Am Fri, 5 Feb 2016 04:54:27 -0800 (PST) schrieb :

I need the new part number and the old part number side by side so...
[1055017][70250133][SLEEVE,BEARING,5",SAWC,498D772G004], being in A1-C1.

Also they're in random order on both files. It wouldn't match up if I sorted them in any order and tried to copy and paste, due to new parts in the new item number file as well.

try:
=INDEX([Book1.xlsx]Sheet1!$A:$A,MATCH(C1,[Book1.xlsx]Sheet2!$B$1:$B$5000,0))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional