Reordering numbers in a list in SharePoint Online

Question

Hello.  I have an issue in SharePoint that I need help with.  It is sort of confusing, but I will try my best to explain as clearly as possible.

I created a SharePoint list consisting of 531 projects with 6 categories: "Prioritized", "Pending", "Complete", "Canceled", Maintenance", and "Research".  Among the 531 items in the list is a subset of projects numbered 1-48, and the numbers are in a column named "Priority No".  The 48 projects are categorized as "Prioritized" in the list.

When one of the 48 projects is completed, I can change the category from "Prioritized" to "Complete" in the list (the column created is a "choice" and a "drop-down" list to change the category easily).  The item in the list changes to "Complete", and the prioritized number is now reassigned to the next project in the list, and so on.  The problem I have is, the numbers in the Priority No. column display in this following order: 

• 1, 10, 11, 12-19,
• 2, 20, 21, 22, 23, 24, 25-29,
• 3, 30-39,
• 4, 40, 41-48,
• followed by a group projects without a priority number (which is accurate)
• then projects with priority numbers 5-9 appear at the bottom of the list

This is incorrect.  I need the priority numbers to appear in numerical order (1-48) and at the top of the list, followed by the group of projects without a priority number.  I have added a few screenshots below to describe the issue better.  Any assistance you can provide will be greatly appreciated.  Thank you in advance.

Answer 1

Hi CY, 

Thanks for giving us more time for performing the further tests at our end.

After referring to the Excel workbook provided by you, we have done several tests by exporting the Excel table to a SharePoint list, we can reproduce the similar behavior at our end. So, we tried narrowing down the problem from the start i.e. with the Excel file only with Priority No column.

Methods tried at our end: (as the problem happens only with the Priority Number column and no problem with the Type/Source column, we took both columns as reference for finding the cause of the problem)

Method 1:

1. Tried to copy the Priority No column and Type/Source column to a new workbook and insert a Table.
2. Tried to export the table as a SharePoint list.
3. Found that Type/Source was converted it as a Number type column and Priority No was converted as a Text (Single line) type column. 

 

4. So, based on the Key Cell reference for the Priority No column, tried to fix the cell but still we are not able to convert the column format from Text (single line) to Number column.

Method 2:

1. As we can’t work by pasting the copied content normally, we used the Paste Special feature and utilized the Paste as Values feature for pasting the content (Priority No and Type/Source column).
2. Pasted the Priority No column as Text.
3. So, we converted each cell that which displays values as text to number.
4. After converting the values to numbers for the Priority No column, we tried to export the table as a SharePoint list. 
5. During the export, we found the Priority No column is converting as a number. 

  

6. After clicking on the Finish and accessing the created SharePoint list in SharePoint Online, Filter by shows the correct result: 

Based on our test, we can conclude this problem is caused due to the wrong data format in Excel workbook. In this case, we request you copy the complete Priority No column and paste it as values in the new Excel workbook. After pasting, convert each cell as numbers manually and check whether column is converting as a Number type. If yes, copy the Priority No column in the new workbook and paste it in the place of problematic workbook. 

 

Hope the information provided is clear and if you need any further help, please feel free to post back. Have a nice day!!

Best Regards,

Ivy

Answer 2

Hello Ivy.  My Internet was down over the weekend, and is now restored.  I will review your reply, and test it out on my end and advise this week.  Thank you so much for your assistance.

-CY

Answer 3

Hi CY,

No problem, you can update your results here when you are available. We will continue to focus on this thread.

Hope you are all well during this period.

Best Regards,

Ivy

Answer 4

Hi Ivy,

I hope you had a nice July 4th holiday weekend.  I have been working on other assignments and did not have time to look at this in detail.  Thank you so much for providing a solution.

Based on your response, it seems like I have to reimport the Excel spreadsheet again into the SharePoint list.  I would reimport the Excel spreadsheet after the Priority No. column has the correct data type, is that correct?  If so, then I will try that approach. 

Thank you again, and have a nice day.

-Carolyn

Answer 5

Hi Carolyn, 

Yes, you are correct.

The Priority No. column should be changed to Number data type wen importing the worksheet into SharePoint Online. You can test it again when you are free and please contact us if you have any further concerns.

Have a great day!!

Best Regards,

Ivy