I am trying to find the repeats in the column new and Excel is not recognizing th 0's in front, causing values such as "001" and "01" to be considered the same.
I am separating the first three characters from the code in idetificator and checking if they are occurring in the "allowed list". Then I separate the rest of the code and put it in new.
How do I create a formula that works for repeats since the one I'm using now is not working for values starting with "0"
Here is a better example (the code row has almost 10000 more cells, so it's just a small example):
I am separating the first three characters from the code in idetificator and checking if they are occurring in the "allowed list". Then I separate the rest of the code and put it in new.
How do I create a formula that works for repeats since the one I'm using now is not working for values starting with "0"
Here is a better example (the code row has almost 10000 more cells, so it's just a small example):
| Code [given] | identificator =IF(COUNTIF(Table3[allowed];LEFT(A2;3)&"*")>0; LEFT(A2;3);"None") | new =MID(A2;IF(MID(A2;4;1)="-";5;4);LEN(A2)) | repeats =COUNTIF(Table1[[#All];[new]];[@[new]]) |
| 123ABC123 | 123 | ABC123 | 1 |
| 999-00010 | 999 | 00010 | 3 |
| 989-0000010 | 989 | 0000010 | 3 |
| 989-001 | 989 | 001 | 3 |
