+ Reply to Thread
Results 1 to 4 of 4

Applying Formula to assign numeric values to text values in sequential order

  1. #1
    Registered User
    Join Date
    09-15-2016
    Location
    Lakeland, FL
    MS-Off Ver
    Windows 7 Professional
    Posts
    4

    Applying Formula to assign numeric values to text values in sequential order

    http://www.excelforum.com/showthread...5&goto=newpost

    I want to achieve a similar objective, but when I insert the formula, it will not insert a number to a respective duplicate value.

    After inserting the formula =if(COUNTIF(H$2:H2,H2)=1,MAX(I$1:I1)+1,VLOOKUP(H2,H$1:I1,0,FALSE))

    It returns:

    H~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~I
    Heat#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Blank)
    HT 414... Unique~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~1
    HT D07... Unique~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~2
    HT C21... Unique~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~3
    HT 414... Duplicate of first Unique Value~~~~~~~~~~~~~~~~(Blank)
    HT 234... Unique~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~4

    My question is how do I get the duplicates to show the value given to the original unique value so that, in this example, the second and subsequent HT 414... will show 1 and so on for all possible duplicates.

    For instance, if HT C21 reappears, the adjacent cell will return "3"

    Thank you in advance.

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Applying Formula to assign numeric values to text values in sequential order

    Try this...

    =IF(COUNTIF(H$2:H2,H2)=1,MAX(I$1:I1)+1,MATCH(H2,H$2:H2,0))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    09-15-2016
    Location
    Lakeland, FL
    MS-Off Ver
    Windows 7 Professional
    Posts
    4

    Re: Applying Formula to assign numeric values to text values in sequential order

    That did it!

    Thanks

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Applying Formula to assign numeric values to text values in sequential order

    You're welcome. Thanks for the feedback!

+ 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. Assign sequential numbers to unique values in a list
    By penfold in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-30-2017, 02:11 PM
  2. [SOLVED] Applying IF function for non-numeric values
    By wael_afifi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-17-2013, 04:09 PM
  3. [SOLVED] Assign underlying numeric values to text for the purpose of calculating an average.
    By neverenoughtime in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-25-2013, 02:10 PM
  4. Replies: 2
    Last Post: 05-25-2011, 01:40 PM
  5. Lots of text data, need to assign unique numeric values
    By dawsonsoo in forum Excel General
    Replies: 4
    Last Post: 08-28-2009, 08:56 AM
  6. Applying numeric values in one cell based on text in another
    By Chi_TechWriter in forum Excel General
    Replies: 3
    Last Post: 02-03-2006, 04:15 PM
  7. [SOLVED] Sort text Values in Numeric Order
    By Karl Burrows in forum Excel General
    Replies: 4
    Last Post: 08-08-2005, 03:05 AM

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