|
Macro to add different characters to identical data in a column ?
I import the following data:
144S7950 146F2025 146F2431 146F2431 146F7008 146F7116 146F7127 146F7A01 146F7A01 146F7BHS 146F7BHS 146F7BHS I need to differentiate between the identical entries, because each of the above has different data in the next column. Is there a way to have Excel, when it finds duplicate entries, to add a chararacter in the front of the 2nd, 3rd, 4th, etc. data. Such as: 144S7950 146F2025 146F2431 A ~146F2431 B 146F7008 146F7116 146F7127 146F7A01 ~146F7A01 146F7BHS ~146F7BHS ^146F7BHS All the original data is 8 characters long. I'm using "lookup" to the above columns for a specific result, but the result is the first one it comes across, not necessarily the correct one. E.g., the 146F2431 has two entries, but the result needs to be B, and it's returning the first 146F2431 it finds, resulting in the incorrect A. I'm thinking that if I could differentiate between the duplicates, my lookup will provide the correct result. Thanks, Steve |
Macro to add different characters to identical data in a column ?
Hi Steve
The following will append an alpha character to all entries, according to their occurrence. =A1&CHAR(COUNTIF($A$1:A1,A1)+64) Copy down as required, having extended range to suit. -- Regards Roger Govier "Steve" wrote in message ... I import the following data: 144S7950 146F2025 146F2431 146F2431 146F7008 146F7116 146F7127 146F7A01 146F7A01 146F7BHS 146F7BHS 146F7BHS I need to differentiate between the identical entries, because each of the above has different data in the next column. Is there a way to have Excel, when it finds duplicate entries, to add a chararacter in the front of the 2nd, 3rd, 4th, etc. data. Such as: 144S7950 146F2025 146F2431 A ~146F2431 B 146F7008 146F7116 146F7127 146F7A01 ~146F7A01 146F7BHS ~146F7BHS ^146F7BHS All the original data is 8 characters long. I'm using "lookup" to the above columns for a specific result, but the result is the first one it comes across, not necessarily the correct one. E.g., the 146F2431 has two entries, but the result needs to be B, and it's returning the first 146F2431 it finds, resulting in the incorrect A. I'm thinking that if I could differentiate between the duplicates, my lookup will provide the correct result. Thanks, Steve |
Macro to add different characters to identical data in a colum
OK, thanks. Maybe close.
it added the same character to the duplicates. The individuals were the A's, but all the duplicates were B's. 146F7A01B 146F7A01B 146F7A02B 146F7A02B I need a different character for each set of duplicates, such as: 146F7A01A 146F7A01B 146F7A02A 146F7A02B Also, could the character be in front of the data ? Also, could I use non alpha-numeric characters, such as ~146F7A01 ? Thanks, Steve "Roger Govier" wrote: Hi Steve The following will append an alpha character to all entries, according to their occurrence. =A1&CHAR(COUNTIF($A$1:A1,A1)+64) Copy down as required, having extended range to suit. -- Regards Roger Govier "Steve" wrote in message ... I import the following data: 144S7950 146F2025 146F2431 146F2431 146F7008 146F7116 146F7127 146F7A01 146F7A01 146F7BHS 146F7BHS 146F7BHS I need to differentiate between the identical entries, because each of the above has different data in the next column. Is there a way to have Excel, when it finds duplicate entries, to add a chararacter in the front of the 2nd, 3rd, 4th, etc. data. Such as: 144S7950 146F2025 146F2431 A ~146F2431 B 146F7008 146F7116 146F7127 146F7A01 ~146F7A01 146F7BHS ~146F7BHS ^146F7BHS All the original data is 8 characters long. I'm using "lookup" to the above columns for a specific result, but the result is the first one it comes across, not necessarily the correct one. E.g., the 146F2431 has two entries, but the result needs to be B, and it's returning the first 146F2431 it finds, resulting in the incorrect A. I'm thinking that if I could differentiate between the duplicates, my lookup will provide the correct result. Thanks, Steve |
Macro to add different characters to identical data in a colum
Hi Steve
I don't think you copied and pasted my formula. =A1&CHAR(COUNTIF($A$1:A1,A1)+64) The $ signs around the first A1 are critical, and the starting point has to be A1, not the last used cell in column A. If you want it at the beginning, then =CHAR(COUNTIF($A$1:A1,A1)+64)&A1 If you want a number at the end, then =A1&TEXT(COUNTIF($A$1:A1,A1),"00") which will append 01, 02 up to 99 -- Regards Roger Govier "Steve" wrote in message ... OK, thanks. Maybe close. it added the same character to the duplicates. The individuals were the A's, but all the duplicates were B's. 146F7A01B 146F7A01B 146F7A02B 146F7A02B I need a different character for each set of duplicates, such as: 146F7A01A 146F7A01B 146F7A02A 146F7A02B Also, could the character be in front of the data ? Also, could I use non alpha-numeric characters, such as ~146F7A01 ? Thanks, Steve "Roger Govier" wrote: Hi Steve The following will append an alpha character to all entries, according to their occurrence. =A1&CHAR(COUNTIF($A$1:A1,A1)+64) Copy down as required, having extended range to suit. -- Regards Roger Govier "Steve" wrote in message ... I import the following data: 144S7950 146F2025 146F2431 146F2431 146F7008 146F7116 146F7127 146F7A01 146F7A01 146F7BHS 146F7BHS 146F7BHS I need to differentiate between the identical entries, because each of the above has different data in the next column. Is there a way to have Excel, when it finds duplicate entries, to add a chararacter in the front of the 2nd, 3rd, 4th, etc. data. Such as: 144S7950 146F2025 146F2431 A ~146F2431 B 146F7008 146F7116 146F7127 146F7A01 ~146F7A01 146F7BHS ~146F7BHS ^146F7BHS All the original data is 8 characters long. I'm using "lookup" to the above columns for a specific result, but the result is the first one it comes across, not necessarily the correct one. E.g., the 146F2431 has two entries, but the result needs to be B, and it's returning the first 146F2431 it finds, resulting in the incorrect A. I'm thinking that if I could differentiate between the duplicates, my lookup will provide the correct result. Thanks, Steve |
Macro to add different characters to identical data in a colum
Yep, you were right. I had extra $'s in the formula.
I moved the ref cell to the end, and used the CHAR 57 to get non alpha-numeric characters: =CHAR(COUNTIF($E$4:E77,E77)+57)&E77 The results are exactly what I was trying to get ( colon, semi:colon, etc): :146F7A01 ;146F7A01 :146F7A02 ;146F7A02 Much thanks for you perfect solution. Steve "Roger Govier" wrote: Hi Steve I don't think you copied and pasted my formula. =A1&CHAR(COUNTIF($A$1:A1,A1)+64) The $ signs around the first A1 are critical, and the starting point has to be A1, not the last used cell in column A. If you want it at the beginning, then =CHAR(COUNTIF($A$1:A1,A1)+64)&A1 If you want a number at the end, then =A1&TEXT(COUNTIF($A$1:A1,A1),"00") which will append 01, 02 up to 99 -- Regards Roger Govier "Steve" wrote in message ... OK, thanks. Maybe close. it added the same character to the duplicates. The individuals were the A's, but all the duplicates were B's. 146F7A01B 146F7A01B 146F7A02B 146F7A02B I need a different character for each set of duplicates, such as: 146F7A01A 146F7A01B 146F7A02A 146F7A02B Also, could the character be in front of the data ? Also, could I use non alpha-numeric characters, such as ~146F7A01 ? Thanks, Steve "Roger Govier" wrote: Hi Steve The following will append an alpha character to all entries, according to their occurrence. =A1&CHAR(COUNTIF($A$1:A1,A1)+64) Copy down as required, having extended range to suit. -- Regards Roger Govier "Steve" wrote in message ... I import the following data: 144S7950 146F2025 146F2431 146F2431 146F7008 146F7116 146F7127 146F7A01 146F7A01 146F7BHS 146F7BHS 146F7BHS I need to differentiate between the identical entries, because each of the above has different data in the next column. Is there a way to have Excel, when it finds duplicate entries, to add a chararacter in the front of the 2nd, 3rd, 4th, etc. data. Such as: 144S7950 146F2025 146F2431 A ~146F2431 B 146F7008 146F7116 146F7127 146F7A01 ~146F7A01 146F7BHS ~146F7BHS ^146F7BHS All the original data is 8 characters long. I'm using "lookup" to the above columns for a specific result, but the result is the first one it comes across, not necessarily the correct one. E.g., the 146F2431 has two entries, but the result needs to be B, and it's returning the first 146F2431 it finds, resulting in the incorrect A. I'm thinking that if I could differentiate between the duplicates, my lookup will provide the correct result. Thanks, Steve |
| All times are GMT +1. The time now is 11:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com