Formula to identify multiple lines as one invoice but then add a number once it's changed

[Johnnymartin]

Hey all,

Apologies if this has been posted before I tried to have a look but couldn't find anything.

All I'm looking for is a formula in column A that identifies if invoice number in column E changes to add 1 to the PIN number like the example.

Sorry if that's a bit basic but can't figure it out! Any help would be much appreciated.

Jonathan

 

[FormR]

Hi, here's one option you can try with XL365:

Book3
	A	B	C	D
1	Import Num			Invoice Nunber
2	PIN 1			7812
3	PIN 1			7812
4	PIN 1			7812
5	PIN 1			7812
6	PIN 1			7812
7	PIN 1			7812
8	PIN 1			7812
9	PIN 1			7812
10	PIN 1			7812
11	PIN 2			4864
12	PIN 2			4864
13	PIN 2			4864
14	PIN 3			9999
15	PIN 3			9999
16	PIN 3			9999
Sheet1

Cell Formulas
Range	Formula
A2:A16	A2	=IF(D2=D1,A1,"PIN "&TEXTAFTER(A1,"PIN ",,,,0)+1)

 

[Johnnymartin]

That works perfectly in Cell A as well thank you!!