Increment if two cells match

Rude Awakening

New member
Joined
Nov 4, 2013
Messages
4
Reaction score
0
Points
0
Excel Version(s)
365
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))))&"-")

View attachment Locations.xlsx

Appreciate any help
RA

Location Sub LocationReference Looking for
New YorkManhattan NY-M-NY-M-01
New YorkManhattan NY-M-NY-M-02
New YorkManhattan NY-M-NY-M-03
New YorkStaton IslandNY-SI-NY-SI-01
New YorkStaton IslandNY-SI-NY-SI-02
New YorkWest Orange CountyNY-WOC-NY-WOC-01
New YorkWest Orange CountyNY-WOC-NY-WOC-02
IllanoisChicago I-C-I-C-01
IllanoisChicago I-C-I-C-02
IllanoisChicago I-C-I-C-03
IllanoisRockford I-R-I-R-01
IllanoisArlington HeightsI-AH-I-AH-01
IllanoisArlington HeightsI-AH-I-AH-02
IllanoisArlington HeightsI-AH-I-AH-03
IllanoisGlendale HeightsI-GH-I-GH-01
CalaforniaLos AngelesC-LA-C-LA-01
CalaforniaLos AngelesC-LA-C-LA-02
CalaforniaLos AngelesC-LA-C-LA-03
CalaforniaSan DeigoC-SD-C-SD-01
CalaforniaSan DeigoC-SD-C-SD-02
CalaforniaSan DeigoC-SD-C-SD-03
CalaforniaSan JoseC-SJ-C-SJ-01
My HouseKitchen MH-K-MH-K-01
My HouseKitchen MH-K-MH-K-02
My HouseThe Bed RoomMH-TBR-MH-TBR-01
My HouseThe Bed RoomMH-TBR-MH-TBR-02
My HouseSitting RoomMH-SR-MH-SR-01
My HouseSitting RoomMH-SR-MH-SR-02
My HouseGarden MH-G-MH-G-01
My HouseGarden MH-G-MH-G-02
 

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
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,
 

Attachments

  • Locations_Example.xlsx
    11.2 KB · Views: 10
Top