Excel ROW() Reset

[MrOaks]

Morning all,

So I have a question about the ROW() function.

I'm using it with a combination of LEFT to create a unique REF in column A; for example, ABCD01.

It's made up of the first 4 characters of a document name and the ROW.

My question is: when the document changes down the list how can I get the ROW to reset to 1, so rather than being CDEF12 it would be CDEF01.

Current Code:

=IF(ROW()-3<10,CONCATENATE(UPPER(LEFT([@[Functional Area 
(BRE-130 Link)]],4)),"0",ROW()-3),CONCATENATE(UPPER(LEFT([@[Functional Area 
(BRE-130 Link)]],4)),ROW()-3))

It works great but I've never managed to figure out a way to restart at 1 in the same formula.

Hopefully, this makes sense.

 

[Special-K99]

Doesnt solve your problem but you can change that existing formula and shorten it to simply

=CONCATENATE(UPPER(LEFT([@[Functional Area(BRE-130 Link)]],4)),IF(ROW()-3<10,"0",""),ROW()-3)


Regarding your problem you need to clearly explain what you mean by "when the document changes"

 

[steve the fish]

I think he means something like this:

=IF(A2<>"",UPPER(LEFT(A2,4)&TEXT(SUMPRODUCT(--(LEFT(A2,4)=LEFT($A$2:A2,4))),"00")),"")

where A2 is the first cell with data in the table's column of interest.

 

[MrOaks]

Regarding your problem you need to clearly explain what you mean by "when the document changes"

Apologies. The spreadsheet has a column with a document name, so:
DOCUMENT1
DOCUMENT1
DOCUMENT1
DOCUMENT2
DOCUMENT2
DOCUMENT3
DOCUMENT3
DOCUMENT3

Column A is my REF that I want to reset ROW() to 1 when there's a new document on that row.

 

[MrOaks]

I think he means something like this:

=IF(A2<>"",UPPER(LEFT(A2,4)&TEXT(SUMPRODUCT(--(LEFT(A2,4)=LEFT($A$2:A2,4))),"00")),"")

where A2 is the first cell with data in the table's column of interest.

I just wanted to say this is awesome, and works really well.

Would it be possible to get some help in replacing spaces with "" as LEFT(XX,4) is giving spaces on Documents when their name is XXX XXXXXXX XXXXXXXX etc.

Thanks.

 

[MrOaks]

I just wanted to say this is awesome, and works really well.

Would it be possible to get some help in replacing spaces with "" as LEFT(XX,4) is giving spaces on Documents when their name is XXX XXXXXXX XXXXXXXX etc.

Thanks.

Never mind I figured it out...

=IF(L66<>"",UPPER(SUBSTITUTE(LEFT(L66,4)," ", "")&TEXT(SUMPRODUCT(--(LEFT(L66,4)=LEFT($L$4:L66,4))),"00")),"")