How to assign 'ID' to 'manufacturer' in datafeed

[donavichi]

Hi,

I have a large datafeed from a supplier which needs to be remapped into my application. This can be done pretty easily but I foresee a problem with the long-term maintenance of updated data and so I am looking for help in solving this, if you'd be so kind. Please note, the solution is required for excel 2007

Problem: Each item in the feed has a 'manufacturer name' assigned to it, which is no problem in itself, but the 'remapped' version (or version to be imported) requires a 'manufacturer ID' in addition to the 'manufacturer name'. This means that each 'manufacturer name' currently has to be converted to a number (predefined by the 'manufacturer id' in the database), then put into the 'manufacturer id' column in the remapped datafeed, in order to be imported successfully (takes ages if you 'find and replace' in excel).

Solution wanted: I'm sure it's pretty simple for an excel expert - can you create a function that will take care of this automatically?

Let's say I have a worksheet in which column A is the predefined 'manufacturer ID', column B is the corresponding 'manufacturer name' for the whole database. (ie all manufacturers in a list for the whole application).

Next, we have a datafeed with, say, 3000 items in it, each item has a column for 'manufacturer name', which could be any one of the manufacturers in the database, but no 'manufacturer id'.

How do you convert those names to their corresponding 'IDs' to populate the 'manufacturer id' column, using excel?

I would think you'd paste the names into an excel worksheet and run some kind of script that looks for the manufacturer name and ID and replaces this data accordingly..?

hope that makes sense. Be a huge help if it can be explained, or a solution given.

Thanks in advance

[Richard Buttrey]

Hi,

There's no need for a VBA procedure. You can do this with standard Excel functions.

Assuming the Manufacturers name is in column A of your datafeed sheet, and your list of manufacturers is in a sheet called "Sheet1", do the following.

In the Data feed sheet where you want the Manufacturers ID enter

Please Login or Register  to view this content.

and copy down.

HTH

[rwgrietveld]

Reading your extensive question I think the solution would be in the Vlookup or Match.

Please provide a small WB with
Manufacturer ID, Manufacturer Name (Both unique!!)

... and a few lines of data with a few Manufacturer names.

If Richards' (solution above) Man ID, Man Name was organized a bit different (Sheet1 Column A and B swopped)

Please Login or Register  to view this content.

[donavichi]

Hi,

There's no need for a VBA procedure. You can do this with standard Excel functions.

Assuming the Manufacturers name is in column A of your datafeed sheet, and your list of manufacturers is in a sheet called "Sheet1", do the following.

In the Data feed sheet where you want the Manufacturers ID enter

Please Login or Register  to view this content.

and copy down.

HTH

So it is. Thanks so much for your help, not least for the astounding speed with which it was delivered.

[donavichi]

Reading your extensive question I think the solution would be in the Vlookup or Match.

Please provide a small WB with
Manufacturer ID, Manufacturer Name (Both unique!!)

... and a few lines of data with a few Manufacturer names.

If Richards' (solution above) Man ID, Man Name was organized a bit different (Sheet1 Column A and B swopped)

Please Login or Register  to view this content.

Thanks very much, although not too sure what you're doing there (i really should have stayed in school and paid attention to the excel tutor). How does this solution differ from Richard's? is there much of a difference/benefit?

[rwgrietveld]

lookup is an index and match in one. Therefore it does not differ much.