Item Database: Automatically assign number to items(store products)

**AEvans190** · 10-22-2013, 12:27 PM

Hi everyone:

I am trying to automatically assign numbers in to a column based on text in another column.

In one column labeled "product_sort" I want a numerically value associated to another column called "product_name"

Sometimes I have many variations of the "product_name" ... so for example if there was 5 variations of a "product_name" then that same "product_name" would be listed on on 5 rows and would need the same numeric value in the column labeled "product_sort" .. for those 5 rows

I am building a product database and have over 3,000 items so I am trying automate this process.

Let me know if I need to clarify further. I seem to have trouble searching for the answer, let alone explaining it... I might not have the correct terminology.

Thank you in advance!

Andrew Evans
Empire Farrier Supply

**AEvans190** · 10-22-2013, 12:28 PM

Here is a screen shot.

The column with the yellow is what I am trying to automate... "Delta Keg Wide Unclipped" would all have the same number and "Diamond Pony Unclipped" would have the same number but a different from "Delta Keg Wide Unclipped"

http://i.imgur.com/DTwlufz.png

**FDibbins** · 10-22-2013, 12:57 PM

Hi and welcome to the forum

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you

Also, not all members can upload picture files (Company firewalls and stuff)

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**AEvans190** · 10-22-2013, 01:10 PM

Here is a sample excel document with just a portion of my database

**FDibbins** · 10-22-2013, 01:17 PM

This is probably not what you want...
=F2&" "&COUNTIF($F$2:F2,F2)

Perhaps if you could provide a few sample answers and explain how you arrived at them? You said you have "Sometimes I have many variations of the "product_name" ... ", but I dont see any variations, all you provided were a few duplicates of the same name?

**AEvans190** · 10-22-2013, 01:29 PM

Here is the file with me manually adding numbers to the "product_sort" column.

**AlKey** · 10-22-2013, 01:43 PM

I would suggest to create a list on another sheet with values and use VLOOKUP to get the ID number
Please see attached sheet.

**FDibbins** · 10-22-2013, 01:50 PM

I agree with AlKey. If you have a set list of product numbers and want to link them with a name every time you enter that name, the simplest would be to have a 2-column table with product name and product number, then use a simple vlookup

**AEvans190** · 10-22-2013, 01:51 PM

Originally Posted by AlKey

I would suggest to create a list on another sheet with values and use VLOOKUP to get the ID number
Please see attached sheet.

This solution seems to be working...

Could you explain further on why to use another sheet? Is it to get the solution to the VLOOKUP function and then paste the values back into the database?

**FDibbins** · 10-22-2013, 01:52 PM

IT does not have to be another sheet, but that keeps it out the way

**AEvans190** · 10-22-2013, 02:43 PM

So on another sheet I would need to list all the products I have? I am still working on the database and currently on line 2800 or so. How would I get a list of the products to another sheet?

Here is my complete database. What I am ultimately trying to do is have a ID for each product (not each variation). The reason you will see the same product for multiple rows is because there are multiple variations of that product. My indesign plugin will read each product name a group all the variations for that product together to display on a catalog.

Thanks for your help everyone!

**FDibbins** · 10-22-2013, 03:04 PM

Based on your data, take all of the data in column C and copy it to column A on sheet2.

Highlight the entire range on sheet 2 select Data Tab/remover duplicates.
you will be left with 47 entries - note that there is a training space in 1 of the "C&M Aprons"

You now have a list of unique names, give them whatever number you want in column B

Then in E2, copied down, use this...
=VLOOKUP(F2,Sheet2!$A$1:$B$100,2,0) I used 100 to allow for extra products, adjust this as needed

If you get an error message, check the product name on that row for extra spaces

**AEvans190** · 10-22-2013, 03:14 PM

do you mean take all the data from column F?

**AlKey** · 10-22-2013, 03:30 PM

Please see attached file. It all ready to go for you.

**AEvans190** · 10-22-2013, 03:31 PM

I am getting tag teamed

**AEvans190** · 10-22-2013, 03:35 PM

That seems to work! Back in college I had an amazing excel teacher I did everything from creating a ribbon to making my own pivot table... but I guess it is true if don't use it you lose it.

Thank you for all your help. You will probably be seeing another post from me soon. I am trying to refresh my brain with all this.

**AlKey** · 10-22-2013, 03:38 PM

Good luck and thank you for the feedback! Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

**AEvans190** · 10-22-2013, 03:39 PM

That looks like it is working! You will be hearing from me soon. I know I will have more issues with this project. Thank you AlKey & FDibbions!

**FDibbins** · 10-22-2013, 04:27 PM

Sorry, yes I meant F

Item Database: Automatically assign number to items(store products)

LinkBack

Thread Tools

Rate This Thread

Display

Item Database: Automatically assign number to items(store products)

Re: Item Database: Automatically assign number to items(store products)

Re: Item Database: Automatically assign number to items(store products)

Re: Item Database: Automatically assign number to items(store products)

Re: Item Database: Automatically assign number to items(store products)

Re: Item Database: Automatically assign number to items(store products)

Re: Item Database: Automatically assign number to items(store products)

Re: Item Database: Automatically assign number to items(store products)

Re: Item Database: Automatically assign number to items(store products)

Re: Item Database: Automatically assign number to items(store products)

Re: Item Database: Automatically assign number to items(store products)

Re: Item Database: Automatically assign number to items(store products)

Re: Item Database: Automatically assign number to items(store products)

Re: Item Database: Automatically assign number to items(store products)

Re: Item Database: Automatically assign number to items(store products)

Re: Item Database: Automatically assign number to items(store products)

Re: Item Database: Automatically assign number to items(store products)

Re: Item Database: Automatically assign number to items(store products)

Re: Item Database: Automatically assign number to items(store products)

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to use VBA to assign a reference number for each item?

[SOLVED] Assign a number to items from a keyword in the name

Excel 2008 : Assign Vendor to Item Number

Help To Lookup Items And If I Choose An Item Send The Info To My Database

Help To Lookup Items And If I Choose An Item Send The Info To My Database

Bookmarks

Bookmarks

Posting Permissions