Sorting Numbers with a "-" in it

grant53396

Hi all,

Probably my incompetence here but i have a confusing issue at the minute. I have a long list of job numbers and am trying to sort them in numerical order. 

There is a criteria that we use to create these that you should probably be aware of:

• Primary number is a 5 digit numerical number (i.e.21495)
• If there is multiple jobs to the identical specification we use the same primary and add -1, -2 etc. (i.e. 21495-1, 21495-2 etc...)
• If a customer rings up claiming warranty on the job, we add "W" to the end of the new number (I.e. 21495W or 21495-1W)
• If we sell a parts order, we add "P" to the end, or "F" represents an internal fastfit process

If i do a standard sort on the job number column, it just puts everything with a "-" or a letter to the bottom of the list, and sorts each section in order. 

I then seperated the number, so i had the primary number split out, and then ran a sort on the second column, and then on the first column. However if there was more than 9 variations of the same job (i.e. soon as i get to 21495-10), it sorts it by the first digit, then the second, etc...

The formula that i used for the above is:

=IF(FIND("-", [Job No]1) > 0, VALUE(MID([Job No]1, 1, 5)), [Job No]1)

Is there a way to overcome this? See below screen capture, and also a link to a dummy sheet-will obviously need to request access... (ignore the 3rd column!!)

https://app.smartsheet.com/b/home?lx=3m_7C3zu9FINS2JC-OmJvQ 

 

Paul Newcome

My initial thought is to parse it out so that all three parts are in separate columns and sort on those. There may be a way to efficiently generate the numbers/letters in hidden columns that you can sort by and then join those columns together for the displayed value.

 

I have requested access to the sheet so I can look at it in more detail if you'd like.

Mike Wilday

I agree with Paul. What about adding checkboxes for those segments where you currently put a W or a P. So that the user can just check off that those thing happened? 

Paul Newcome

I like the idea of a checkbox, but how would we append the correct letter to the end of the finalized string? Maybe a =JOIN([First Number Column]:[Last Number Column]) + JOIN(COLLECT()) kind of thing to bring in the values based on the checkboxes?

Paul Newcome

Grant,

 

I have requested admin access to be able to add columns and edit their properties. If you do not want me to have admin access to it, would you please add 3 additional columns and make them of the checkbox type?

Paul Newcome

Ok. So I have built a solution for you that will allow sorting to work the way you want it to and to help ensure no one messes it all up.

.

1. Row 1 has the 1's in it to keep that row up top when you sort it with the lowest Job Number first. If the highest job number is first, the values in row 1 will move to the bottom. To keep it at the top when sorting in descending order, simply replace the 1's with z's. Of course you can change the font and background colors to fit what you want as far as display goes.

2. I created a form that can be used for entry. The only fields available on the form are the short job number, Whether it is Warranty/Parts/Fast Fit, and any notes or comments. I also added a field where an attachment can be uploaded if they happen to have a digital copy of a work order or anything like that. It is currently set to add a new row to the bottom. 

3. As forms are entered, the count and add-ons will be automatically generated and combined into the Job No column. You can hide those last 5 columns if you wanted to only display the job number and the notes.

4. If a new Short Job Number is added, it will still automatically generate any counts and/or add-ons. (The last row with Short No of 21500 was generated by completing the form)

.

I hope this helps. Let me know if you have any further questions.

Mike Wilday

Looks like Paul has helped you out. I'd love to see the result. And provide any suggestions if any are needed!