Create different repeating numbers

[pansovic]

I created a formula that gives me codes by taking the first letters of one column and the last 3 numbers of other column. Though sometimes it give me the same code.
When it gives me the same I want to add "-1", "-2", "-3", etc. So the code should look like
CAM456, CAM456-1, CAM456-2.
Therefore I am looking for a formula that can detect that the new code is the same as previous one and automativally it adds in the new code a "-1", "-2" etc.
Anybody knows how to do that?

[jeffreybrown]

Could this sample work...

[pansovic]

This is very cool though my origenal formula is a bit more complicate because it consist of 1 condition though if that condition is not valid it takes another data for making the code.
You can see it in your attached file. I have used your formula from line 9 to 29 and works perfectly though it doesn't give me the right number in line 30 where it has to be "WOC616" and with your formula it gives me "WOC-1". This is because in column G there is no value and when there is no value I have to use other data. My origenal formula you can find in cell A30 and than you will see how it works.
Question is now how can I integrate my origenal formula in your's?

[Leith Ross]

Hello pansovic,

This formula will create the invoice if the cell in column "G" is not empty. I use a helper column "I" with this formula:

=LEFT(E9,3)&RIGHT(G9,3)

Add this formula to column "A":

=IF(G9<>"",IF(COUNTIF($I$9:$I9, LEFT(E9, 3)&RIGHT(G9,3))>1,LEFT(E9,3)&RIGHT(G9,3)&"-"&(COUNTIF($I$9:$I9, LEFT(E9, 3)&RIGHT(G9,3))-1),LEFT(E9,3)&RIGHT(G9,3)),"")

[pansovic]

Thisn does not add the -1, -2, -3 etc and when there is no value in colomn G it gives me in column A an empty value. This is not the solution.
JeffreyBrown has the correct formula though I am still figuring out how to integrate my formula into his formula.

[jeffreybrown]

I have used your formula from line 9 to 29 and works perfectly though it doesn't give me the right number in line 30 where it has to be "WOC616" and with your formula it gives me "WOC-1".

Looking at your attachment, A30 reads WOC616 and that is what you say you want.

If this is not what you want then can you right out your requirements instead of just the formula. More description is required please...

[Leith Ross]

Hello pansovic,

Are you sure you followed my directions correctly?

[pansovic]

This is on reply to Jeffreybrown:
Cell A30 is written my formula and that's why it gives "WOC616". In cell A29 is used your formula. If you copy your formula to A30 than you will get result "WOC-1"

[Leith Ross]

Hello pansovic,

How do you create WCO616 when there is no invoice number in G30? In the copy I downloaded G30 is blank.

[jeffreybrown]

If I copy the formula down to A30 from A29 in your attachment it shows WOC616.

[jeffreybrown]

Hi Leith,

The OP is getting WOC616 from B30 since G30 is blank. The month and day of the date

[pansovic]

Are you sure you followed my directions correctly?

OK, now I have used your formula in the Book1 excel file (before I used in my origenal file) and in Book1 file indead is working the -1,-2,-3 Though in the cell A30 it is still empty while it has to be WOC616

[Leith Ross]

Hello Jeff,

Thanks for solving that mystery. I must have missed that piece of information along the way. The First IF statement can be adjusted easily enough to fill that in.

[Leith Ross]

Hello pansovic,

Here is the adjusted formula for column "A".

=IF(G9<>"",IF(COUNTIF($I$9:$I9, LEFT(E9, 3)&RIGHT(G9,3))>1,LEFT(E9,3)&RIGHT(G9,3)&"-"&(COUNTIF($I$9:$I9, LEFT(E9, 3)&RIGHT(G9,3))-1),LEFT(E9,3)&RIGHT(G9,3)),LEFT(E9,3)&RIGHT(G9)&MONTH(B9)&DAY(B9))

[pansovic]

If I copy the formula down to A30 from A29 in your attachment it shows WOC616.

Indeed you are right though in my file still it is not working and I found out why. See now attached Book1 and I have added another line and than I get WOC-1. If following line is blank than indeed your formula is working.

[jeffreybrown]

Please try Leith's suggestion as it will get you were you need to be...

[Leith Ross]

Hello pansovic,

I have attached the workbook with the macros added in.

[pansovic]

Hello pansovic,

I have attached the workbook with the macros added in.

Really looks cool though still have 1, in fact new, problem.
Attached you can find your file though now I have added new lines and cell A41 should give me "AFP73-1".
This is same case as WOC (without value in column G) but it is repeating the same code.

[Leith Ross]

Hello pansovic,

Here are the new formulae. Column "A" is now:

=IF(COUNTIF($H$9:$H9,H9)>1,H9&"-"&(COUNTIF($H$9:$H9,H9)-1),H9)

Column "H"is now:

=IF(G9<>"",LEFT(E9,3)&RIGHT(G9,3),LEFT(E9,3)&MONTH(B9)&DAY(B9))

The attached workbook contains the changes.