Add incremental character to cell value at change in value 449becomes 449-A 510 becomes 510-B

  • Thread starter Thread starter JenIT
  • Start date Start date
J

JenIT

I do not know if this is even possible, but this is an example of what
I am starting with and what I would like it to become. Sheet has two
columns and would like to add to the invoice number an incremental
alphabet number when the order number changes - but if the invoice
number changes - the alpha number also has to start again.

The "I would like it to become" column can be in a separate column. I
can always concatenate or delete info to get final result if I can
just get the - alpha character somewhere.

order invoice I would like it to become........
449 12000 12000 - A
510 12000 12000 - B
510 12000 12000 - B these stay the
same because order number did not change
510 12000 12000 - B these stay the
same because order number did not change
535 12001 12001 - A
535 12001 12001 - A these stay the
same because order number did not change
540 12001 12001 - B
544 12001 12001 - C

I hope this makes sense and If anyone has any ideas I would love to
hear them. Thanks ahead of time.
Jen
 
Hi Jen,

Am Thu, 3 May 2012 09:28:58 -0700 (PDT) schrieb JenIT:
order invoice I would like it to become........
449 12000 12000 - A
510 12000 12000 - B
510 12000 12000 - B these stay the
same because order number did not change
510 12000 12000 - B these stay the
same because order number did not change
535 12001 12001 - A
535 12001 12001 - A these stay the
same because order number did not change
540 12001 12001 - B
544 12001 12001 - C
Click to expand...

order in column A and invoice in column B then in C2:
=B2&" - A"
in C3:
=IF(A3&B3=A2&B2,C2,IF(AND(A3<>A2,B3=B2),B3&" - "&CHAR(CODE(RIGHT(C2,1))+1),B3&" - A"))
and fill down


Regards
Claus Busch
 
Back
Top