Does sorting scramble data?

Question

Hey all,

Our question is whether or not sorting data scrambles in Excel 2016 scrambles the data.  Onside insists it doesn't; that my fears are based on old versions.  I've recently found evidence of scrambled data after I have used sorts but the proof I've found is relatively small (3 to 6 mismatched entries out of literally thousands).  He's insisting that this is due to cutting and pasting while filters are active.  I'm pretty sure I've worked really hard not to do this.

More importantly, I do have large stretches of data that need to be consolidated and merged into other columns.  Our current database, for example, exports 240 columns with 60 of these dedicated to address (Primary_address_Address_Address, Work_address, User_submitted_address, Primary_submitted, address, etc.).  I need a faster means of consolidating these columns down by finding "units" or "clusters" of related data (such as blanks at the top or longer addresses at the top, etc.) without compromising data quality.

Any suggestions?

Answer 1

I. Does this issue occurs while sorting in all Excel files or is it specific to particular Excel file?

I have not dared try sorts with this file because of past files going from Office 2003 (or possibly earlier) to the last few months.  Of course, numerous files along the way.

II. How exactly are you sorting the data in Excel file?

We are very careful to always:

1. CTRL + A to make sure we have the entire document.  I recently discovered that Excel treats all empty columns as "end of the line."  In other words, it will stop at the first completely blank column it finds and treats everything thereafter as a second list.  Excel used to treat rows the same way.  I know that filters work that way.
2. Data | Sort & Filter | Sort
3. If the "Sort Options" dialogue box appears (and it rarely does because we are careful to make sure the entire document is selected) we are always very careful to select the "all columns" radio button
4. We then do sorts by multiple criteria, most typically last name, first name, address, city, state & zip or some variation

Answer 2

Hello,

Please provide us more information before we proceed.

1. Does this issue occurs while sorting in all Excel files or is it specific to particular Excel file?
2. How exactly are you sorting the data in Excel file?

I suggest you to try creating a new Excel file and then try sorting the data in Excel using the suggestion mentioned in following Microsoft article to check if it work as expected.

https://support.office.com/en-us/article/Sort-data-in-a-range-or-table-f92f26c6-470d-4b09-a0e9-e5c3b60a8a7a

You may use consolidate data feature of Excel to merge the data of multiple worksheet to one worksheet. To do so, please refer to the suggestion mentioned in the following Microsoft article and check if it helps.

https://support.office.com/en-us/article/consolidate-data-from-multiple-worksheets-in-a-single-worksheet-69c84929-5f67-48cf-b48e-e55b20cda2ef

I hope the above information helps. Let us know if you need further assistance.

Thank you.