Entry No to change with Ref Number

[Shabbo]

Dear Sir,

Column A entry no need to change as soon as Column B Reference no changes.

Eg : Ref # 2623324 Entry PAID001 should change to PAID002 as soon as reference no comes 2623328.

 

[Khalid NGO]

Hi Shabbo,

Just enter 1 in A2

Now enter this in A3:
=IF(B2=B3,A2,MAX(A$2:A2)+1)

copy down...

select your range in column A and format the cells as:
"PAID"000

Regards,

 

[Shabbo]

Hi Shabbo,

Just enter 1 in A2

Now enter this in A3:
=IF(B2=B3,A2,MAX(A$2:A2)+1)

copy down...

select your range in column A and format the cells as:
"PAID"000

Regards,

Dear Sir,

This excel formula works perfectly but I wanted entries like “PAID001” in actual because I need to import these entries into my accounting software, if I format "PAID"000 it can be seen correctly but can’t import into software because If I do Past special then Its coming 1 only.

 

[Khalid NGO]

Hi,

Yes that is because of cell formatting, you can apply the same logic with TEXT Function.
for example, make a helper column with following formula:
=TEXT(A2,"""PAID""000")


Or if you don't like helper columns:
Just Enter this in A2:
PAID001

And then enter this {array formula} in A3 and copy down:
=TEXT(IF(B2=B3,A2,MAX(SUBSTITUTE(A$2:A2,"PAID","")+0)+1),"""PAID""000")

{array formula needs to be committed using the key combination of
Ctrl+Shift+Enter (CSE) instead of just enter}

Regards,

 

[bosco_yip]

Another option without helper cell,

In A2, formula copy down :

="PAID"&TEXT(SUMPRODUCT(1/COUNTIF(B$2:B2,B$2:B2)),"000")

Regards
Bosco