Counting and incrementing and then concatenate...

[Guest]

All,

I have a need to create an alphanumeric value that is a combination of two
alpha values and an incremented number off of existing data. For instance:

ABC-XYZ-001
ABC-XYZ-002
ABC-XYZ-003

The alpha values already exist in two separate fields. I have not done a
count or increment. I suspect what I need to do is to get each instance of
the ABC-XYZ to increment and then concatenate this new field together.

I've got the concatenate - I"m just not sure I'm finding a formula that will
count each record that has the ABC-XYZ combination - and I have ABC-LMN's as
well - the combinations are vast - I need to count when how many each
combination appears...
Thoughts?

 

[VBA Noob]

Hi Annie,

What about this countif

=COUNTIF($G$1:G1,G1)
=COUNTIF($G$1:G2,G2)

Format cells in custom as 000 if G3 is blank then "000" will appear s
you can turn off by going to Tools options View and unticking zer
values.
Note the first cell "G1" in this example needs to be a absolute

VBA Noo

 

[Ardus Petus]

Hi Annie,

Say your ABC/XYZ values are in columns A & B:
Enter in C1:
=A1&"-"&B1&"-"&TEXT(SUMPRODUCT((A$1:A$1=A1)*(B$1:B1=B1)),"000")
and drag down


HTH

 

[Ardus Petus]

Ooops:
=A1&"-"&B1&"-"&TEXT(SUMPRODUCT((A$1:A1=A1)*(B$1:B1=B1)),"000")

(forgot a $ sign)

 

[Guest]

Ardus,

This appears to work for the first combination but then doesn't afterwards.

My data would look like this:

OTH OTH
OTH COV
OTH OTH
MTS COV
MTS OTH
OTH OTH
ELE OTH
MTS COV

I would want
OTH-OTH-001
OTH-COV-001
OTH-OTH-002
MTS-COV-001
MTS-OTH-001
OTH-OTH-003
ELE-OTH-001
MTS-COV-002

I'm also wanting this to say valid AFTER I filter the data - using
AutoFilter to look at the data.

 

[Ardus Petus]

Corrected fomula (after my Oooops) gives expected results
=A1&"-"&B1&"-"&TEXT(SUMPRODUCT((A$1:A1=A1)*(B$1:B1=B1)),"000")

Cheers

 

[Guest]

Ardus, it's working but not past the 10th instance of the occurence...

 

[Ardus Petus]

Please post some sample data with which my formula does not work.

Cheers,