Automatic Sequencing

[pauldaddyadams]

Hi,

I wonder if anyone can help with a couple of formulas.

Formula 1

I have a formula:
=IF(COUNTIF($F$5:F6,F6)=1,MAX($E$4:E5)+1,"")

Which adds one to a cell if the name is unique. This currently works but I need it to display “NW 1”, “NW 2” etc etc

Formula 2

I would like a formula which automates the Contract number based on the Contract Name, again it needs to add 1 like in the sample.

Can anyone help please?

[Fotis1991]

Hi

As an idea using 2 helper & hidden columns.

="NM"&" "&G6

=IF(COUNTIF($D$6:D6;D6)=1;H5+1;H5)

="1.005000."&H6

[pauldaddyadams]

Hi,

Thanks Fotis, its so close. In column B I need it to also display the zero e.g. currently its .1 and I need .01

Can this be achieved?

[Fotis1991]

Yes. In B6 and copy down use this.

="1.005000."&TEXT(H6,"##.#00 _€;-##.#00,00 _€")

[pauldaddyadams]

Hi That didnt work

In B6 the new formula returns 1.005000.1.00 but I need 1.005000.01

Any ideas?

[Pete_UK]

You can apply a custom format to the cells E6:E16, of

"NM "0

which will give you an appearance of NM 1, NM 2 etc. with your existing formula.

Hope this helps.

Pete

[pauldaddyadams]

It would only need to put in this zero if the last digit is less than .10 otherwise it works

[Fotis1991]

Should be a local settings issue then.

In workbook that i uploaded everything works fine for me! Doesn't for you??

What's if you try in B6?

="1.005000."&TEXT(H6,"##.#00" )

[pauldaddyadams]

Hi, that didnt work either.

I got around it with

=IF(H6<10,"1.005000."&TEXT(H6,"0##"),"1.005000."&TEXT(H6,"##"))

Im unsure if this is the best way?

[Fotis1991]

Your attachment is without my formulas so i am not able to test it, BUT i upload again my file. In columnA i have (using copy>paste special>only values) the results that i get in column B using my suggested formula.

Don't you get the same results?

[pauldaddyadams]

Strangly I dont, I get these:
1.005000.1.00
1.005000.2.00
1.005000.2.00
1.005000.2.00
1.005000.2.00
1.005000.3.00
1.005000.4.00
1.005000.5.00
1.005000.6.00
1.005000.7.00
1.005000.8.00

[romperstomper]

Small tweak:

="1.005000."&TEXT(H7,"00" )

[pauldaddyadams]

Ah! That did it!