Numeric Sort with Trailing Zeros

[Gsrgofass]

Hello,

I have a serial number column in my database where the leading and last number of the 6 digit number represents the YOM (year of manufacture) of the machine. I have it formatted as 'Special' in order to display a leading zero for 2020 models.
When doing a general search for a range of serial numbers choosing 'Smallest to Largest' is a popular choice.
Ex:
019742 (2020 YOM)
793821 (2017 YOM)
803061 (2018 YOM)
915471 (2019 YOM)

If I sort 'Smallest to Largest', I get the order as you see above. 2020 listed prior to any other year. When 2021 hits, I will have years 2019 and then see 2021 serial number with 2020 still at the top of the list.

Is it possible to sort but have the leading 0 come after a leading 9 and then allow the next leading number to be a 1, 2.... and so on?
793821 (2017 YOM)
803061 (2018 YOM)
915471 (2019 YOM)
019742 (2020 YOM)
115202 (2021 YOM)
213302 (2022 YOM)
etc....

 

[Gsrgofass]

Got it. Cells formatted as 'Number. with zero decimal places. Thanks!!
Any ideas on the formula to highlight repeated serial numbers in column F?

 

[Joe4]

Any ideas on the formula to highlight repeated serial numbers in column F?

Use Conditional Formatting. Let's say that your data is in cells F2:F100.
Then select F2:F100 and use this Conditional Formatting formula:

=COUNTIF(F$2:F$100,F2)>1

and choose your formatting color.

 

[Gsrgofass]

Got it. Thanks

Use Conditional Formatting. Let's say that your data is in cells F2:F100.
Then select F2:F100 and use this Conditional Formatting formula:

=COUNTIF(F$2:F$100,F2)>1

and choose your formatting color.

 

[Joe4]

You are welcome.
Glad we were able to help.

 

[Peter_SSs]

Any ideas on the formula to highlight repeated serial numbers in column F?

Apart from Joe's suggestion, Conditional Formatting also has a built-in setting for duplicates.

 

[Joe4]

Apart from Joe's suggestion, Conditional Formatting also has a built-in setting for duplicates.

That is good to know. I use Formulas about 99% of the time. So much so that I am not always aware of the built-in options!

 

[Gsrgofass]

I'm back again
It's not working out as I'd hoped. With the current formula in the YOM column =(RIGHT(F649,1)&LEFT(F649,1))+2000 and having the 6-digit serial number as 'number' in the column, the result is that the formula ignores the left most zero. IE, the serial number 015442 is interpreted as a 2021 YOM.

If I change the cell containing the serial number to 'text', everything works well for the YOM column but another worksheet suffers since the formula in the other sheet was written to look for a 'number' and not 'text'.

I changed the YOM formula to =((RIGHT(F648,1)&LEFT(F648,1))+2000)-1 and that works out and displays the correct 2020 YOM but then I get the wonderful error upper left triangle

Is there any other formula that comes to mind to fix this issue?

 

[kvsrinivasamurthy]

Ref POst #9

Select F2:F37 then formula for conditional formating.

=COUNTIF($F$2:$F$37,F2)>1

format for Fill Red

 

[Fluff]

@kvsrinivasamurthy
See post#12

@Gsrgofass
How about

=(RIGHT(F648)&IF(LEN(F648)<6,0,LEFT(F648)))+2000

copied down

 

[Gsrgofass]

kvsrinivasamurthy, It is not re: the conditional formatting. It is the formula required to use the left most zero in order to generate the YOM (Year Of Manufacture).
The formatting was solved by post #15.

Ref POst #9

Select F2:F37 then formula for conditional formating.

=COUNTIF($F$2:$F$37,F2)>1

format for Fill Red