+ Reply to Thread
Results 1 to 6 of 6

Create unique identifier using index and match

  1. #1
    Forum Contributor
    Join Date
    05-28-2016
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    119

    Create unique identifier using index and match

    I have an inventory-type spreadsheet where i wish to create unique identifiers for each stock item.
    The source of these items names is on a "DropDownLists" tab in the table column "Item Name". The Unique identifiers will be generated in the column "Internal Code" on the same tab via a formula and it is hoped this self replicates as items are added to the "Item Name" column as does the rest of my formulas in the table.

    On the tab "Item Lists" the "Item Names" are replicated via formula, in a table that also tabulates "Vendors" etc. I would like to create an "Internal Code" on the "DropDownLists" tab that looks at the "Item Name" on the "DropDownLists" tab, checks where this is on the "Item Lists" tab, uses its corresponding "Vendor" name on the "Item Lists" tab (using 3 letters, all capitals), then adds a unique number (using 3 numbers). If there is only two letters in the Vendor then the blank can be a Zero. eg HP0-001, PEA-001.

    I suspect a combination of Index and match is needed with text and countif functions but cannot seem to get it to work. Any help would be greatly appreciated. I have uploaded a copy of my spreadsheet so far.
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Create unique identifier using index and match

    I'm not sure if this is what you want.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Contributor
    Join Date
    05-28-2016
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    119

    Re: Create unique identifier using index and match

    I appreciate your reply. The output format is certainly what i am after. However, id like to reference the range from the "Vendor" column on the "Item List" tab rather than the "DropDownLists" tab. Plus use the countif function to assign duplicates with unique numbers.
    I'll play around with your formula.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Create unique identifier using index and match

    I am still trying to get a grip on the requirement. As best I can see, all the items on the Item List Tabs are unique. So, the duplicates you talk about must be vendors.

    Take for example the first three products. All of them are HP. Do you want HP0001, HP0002 and HP0003 for these items? Then when we get to rows 13 & 14 HP0004, HP0005?

    If this is the case, we could arrange a formula to do this using countif. The identifiers will be unique but they may change if the list is sorted or has new information inserted.

    Then you want these identifiers "copied back" to the DropDownList sheet?

  5. #5
    Forum Contributor
    Join Date
    05-28-2016
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    119

    Re: Create unique identifier using index and match

    Basically yes.

    I played with the following formula overnight and got it to work, but I am not sure if it is too clunky. I could not get the countif function to reference a range in a table using "Table6[Item Name]" so had to use cell references alone.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Thank you for reminding me these will change if the data is rearranged. I have tried to think of all possible items we require for our project and there should not be much more. If it is I can live with leaving the source data out of order as long as the output can be sorted without changing its code.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Create unique identifier using index and match

    I am not clear about your requirement.
    Formulsa in A5 of DropDownLists Sheet
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 09-12-2017, 06:45 AM
  2. [SOLVED] Create Unique Identifier by either adding or removing a space
    By sherylt13 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-27-2017, 03:31 PM
  3. Need a formula to create a unique identifier for Duplicate names
    By asharma9 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-21-2015, 04:37 PM
  4. Replies: 2
    Last Post: 12-04-2013, 09:45 PM
  5. [SOLVED] Help to create a Index / Match list of unique values.
    By JasonNeedsHelp in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-16-2013, 04:44 PM
  6. [SOLVED] Match/Lookup?...No unique identifier
    By sc077yc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-16-2012, 05:31 AM
  7. [SOLVED] How do I create a unique identifier # when open excel file?
    By ritarowe in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-28-2005, 02:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1