Results 1 to 4 of 4

Thread: Increment if two cells match

  1. #1

    Increment if two cells match



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hey Guys
    I am creating business requirement strings using the first letter of each word in columns.

    What I need is for EVERY two cells that are the same, for there to be an incremental number after this to differentiate it from the prior, making it a most unique BR business requirement number.

    Once two cells are the same I need the increment to continue, when two cells change to be different for the increment to restart at 1 and continue. What i am looking for is the content of Column D


    I have created a formula to grab the letters(column C), the increment will be added to this. Here is the formula in C2:
    =((LEFT(A2,1)&IF(ISERROR(FIND(" ",A2)),"",LEFT(MID(A2,FIND(" ",A2)+1,LEN(A2)),1))&IF(ISERROR(FIND(" ",MID(A2,FIND(" ",A2)+1,LEN(A2)))+FIND(" ",A2)),"",LEFT(MID(A2,FIND(" ",MID(A2,FIND(" ",A2)+1,LEN(A2)))+FIND(" ",A2)+1,1))))&"-"&(LEFT(B2,1)&IF(ISERROR(FIND(" ",B2)),"",LEFT(MID(B2,FIND(" ",B2)+1,LEN(B2)),1))&IF(ISERROR(FIND(" ",MID(B2,FIND(" ",B2)+1,LEN(B2)))+FIND(" ",B2)),"",LEFT(MID(B2,FIND(" ",MID(B2,FIND(" ",B2)+1,LEN(B2)))+FIND(" ",B2)+1,1))))&"-")

    Locations.xlsx

    Appreciate any help
    RA

    Location Sub Location Reference Looking for
    New York Manhattan NY-M- NY-M-01
    New York Manhattan NY-M- NY-M-02
    New York Manhattan NY-M- NY-M-03
    New York Staton Island NY-SI- NY-SI-01
    New York Staton Island NY-SI- NY-SI-02
    New York West Orange County NY-WOC- NY-WOC-01
    New York West Orange County NY-WOC- NY-WOC-02
    Illanois Chicago I-C- I-C-01
    Illanois Chicago I-C- I-C-02
    Illanois Chicago I-C- I-C-03
    Illanois Rockford I-R- I-R-01
    Illanois Arlington Heights I-AH- I-AH-01
    Illanois Arlington Heights I-AH- I-AH-02
    Illanois Arlington Heights I-AH- I-AH-03
    Illanois Glendale Heights I-GH- I-GH-01
    Calafornia Los Angeles C-LA- C-LA-01
    Calafornia Los Angeles C-LA- C-LA-02
    Calafornia Los Angeles C-LA- C-LA-03
    Calafornia San Deigo C-SD- C-SD-01
    Calafornia San Deigo C-SD- C-SD-02
    Calafornia San Deigo C-SD- C-SD-03
    Calafornia San Jose C-SJ- C-SJ-01
    My House Kitchen MH-K- MH-K-01
    My House Kitchen MH-K- MH-K-02
    My House The Bed Room MH-TBR- MH-TBR-01
    My House The Bed Room MH-TBR- MH-TBR-02
    My House Sitting Room MH-SR- MH-SR-01
    My House Sitting Room MH-SR- MH-SR-02
    My House Garden MH-G- MH-G-01
    My House Garden MH-G- MH-G-02

  2. #2
    Good afternoon,

    Please see the attached file. Based on your table, it looked like the number needed to be two digits. That's why there is a TEXT function in the middle. If this is not important, you can remove the TEXT piece (or if it needed to be 3 digits, always, then add another zero, etc).

    Hope this helps,
    Attached Files Attached Files

  3. #3
    Thanks a mill BGoree
    Worked perfectly

  4. #4
    Excellent

Posting Permissions

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