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:
View attachment Locations.xlsx
Appreciate any help
RA
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 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 |