|
Counting and incrementing and then concatenate...
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? |
Counting and incrementing and then concatenate...
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 -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=553303 |
Counting and incrementing and then concatenate...
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)),"0 00") and drag down HTH -- AP "Annie" a écrit dans le message de news: ... 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? |
Counting and incrementing and then concatenate...
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)),"0 00") and drag down HTH -- AP "Annie" a écrit dans le message de news: ... 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? |
Counting and incrementing and then concatenate...
Corrected fomula (after my Oooops) gives expected results
=A1&"-"&B1&"-"&TEXT(SUMPRODUCT((A$1:A1=A1)*(B$1:B1=B1)),"00 0") Cheers -- AP "Annie" a écrit dans le message de news: ... 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)),"0 00") and drag down HTH -- AP "Annie" a écrit dans le message de news: ... 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? |
Counting and incrementing and then concatenate...
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)),"00 0") Cheers -- AP "Annie" a écrit dans le message de news: ... 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)),"0 00") and drag down HTH -- AP "Annie" a écrit dans le message de news: ... 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? |
Counting and incrementing and then concatenate...
Please post some sample data with which my formula does not work.
Cheers, -- AP "Annie" a écrit dans le message de news: ... 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)),"00 0") Cheers -- AP "Annie" a écrit dans le message de news: ... 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)),"0 00") and drag down HTH -- AP "Annie" a écrit dans le message de news: ... 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? |
| All times are GMT +1. The time now is 03:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com