Counting and incrementing and then concatenate...

[Annie]

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 so you can turn off by going to Tools options View and unticking zero values.
Note the first cell "G1" in this example needs to be a absolute

VBA Noob

[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
--
AP

"Annie" <[email protected]> a écrit dans le message de news:
[email protected]...
> 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?

[Ardus Petus]

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

(forgot a $ sign)

--
AP

"Ardus Petus" <[email protected]> a écrit dans le message de news:
[email protected]...
> 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
> --
> AP
>
> "Annie" <[email protected]> a écrit dans le message de news:
> [email protected]...
>> 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?
>
>

[Annie]

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" wrote:

> 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
> --
> AP
>
> "Annie" <[email protected]> a écrit dans le message de news:
> [email protected]...
> > 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?
>
>
>

[Ardus Petus]

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

Cheers
--
AP

"Annie" <[email protected]> a écrit dans le message de news:
[email protected]...
> 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" wrote:
>
>> 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
>> --
>> AP
>>
>> "Annie" <[email protected]> a écrit dans le message de
>> news:
>> [email protected]...
>> > 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?
>>
>>
>>

[Annie]

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



"Ardus Petus" wrote:

> Corrected fomula (after my Oooops) gives expected results
> =A1&"-"&B1&"-"&TEXT(SUMPRODUCT((A$1:A1=A1)*(B$1:B1=B1)),"000")
>
> Cheers
> --
> AP
>
> "Annie" <[email protected]> a écrit dans le message de news:
> [email protected]...
> > 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" wrote:
> >
> >> 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
> >> --
> >> AP
> >>
> >> "Annie" <[email protected]> a écrit dans le message de
> >> news:
> >> [email protected]...
> >> > 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?
> >>
> >>
> >>
>
>
>

[Ardus Petus]

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

Cheers,
--
AP

"Annie" <[email protected]> a écrit dans le message de news:
[email protected]...
> Ardus, it's working but not past the 10th instance of the occurence...
>
>
>
> "Ardus Petus" wrote:
>
>> Corrected fomula (after my Oooops) gives expected results
>> =A1&"-"&B1&"-"&TEXT(SUMPRODUCT((A$1:A1=A1)*(B$1:B1=B1)),"000")
>>
>> Cheers
>> --
>> AP
>>
>> "Annie" <[email protected]> a écrit dans le message de
>> news:
>> [email protected]...
>> > 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" wrote:
>> >
>> >> 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
>> >> --
>> >> AP
>> >>
>> >> "Annie" <[email protected]> a écrit dans le message de
>> >> news:
>> >> [email protected]...
>> >> > 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?
>> >>
>> >>
>> >>
>>
>>
>>