Group and sort data

Hi! this is Xedar, new kid on the block. i am working as a Project Asst with the Patient Care Ctr Project Office, Victoria. Our d/base in Excel runs to 30,000 rows. I tried 'data group by rows' and sorting based on Room ID. Simple? But no, the data keeps running out of sync - cross-integrating- despite the properly numbered Room ID and despite the grouping of data. Please, if someone can advise on how this can be fix. it seems to be a software problem.  

You didn't supply enough information to generate a reply.

Suggest you provide your excel version and attach a workbook (see options tab)  to your reply showing...
1. a small portion of your current data
2. how you want that data to look
3. expanded explanation

'---
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html
(independent review of add-in with 30 ways to sort)

Jim

I have uploaded part of the Excel sheet. The task require that I sort data via Room ID1 (Rm ID1) - a simple function.

However, due to some technical glitch, at times one or two rows from another room will get jumbled into a different room ID. I decided to group data by rows according to room ID to lock data in place prior to sorting but this has not helped. Altogether there will be 18,000 to 20,000 rows so quite manageable. I am not sure why the rest of the 5 fields (columns) do not get sorted in sync with the Rm ID, the key. Your input is much appreciated.

Zedar T.
Project Asst, PCC Project Office
Victoria BC

I forgot another thing is that the sort function failed in maintaining the Rm ID according to its sequential listing eg. N315, N316, N317...... S315, S316, S317... N319, S319 (OK) .... S320, S321 (N320 is jumbled elsewhere) and entire IDs say, N322, S322, N324, S324 missing. I confirm that all these data fields were entered as I had them printed out yesterday. When I sorted today, I found the data jumbled. One explanation I cd think of, is that I'd created Rm ID2 whereby the digits came prior to the letter so 315-N, 315-S, 316-N, 316-S which I had felt allowed me the manoeuvrebility of sorting data according to room numbers rather than the North-N and S-South. In the Advance sort function, the dialog box allows for entry of primary key and value/cell color, etc. to enable sort which i had complied with - but apparently, it does not work. Thanks for recommendation on how to fix this. Zedar

You are not making things easy...
The worksheet has no column titles.
I assume column b or column c is the ID column, but that is as close as I can get.

In what priority is the sort to be done:  
By the alpha designation or by the numbers...
    Does A400 come before B300?  Does 321S come before 324N?
Is there a secondary sort column and if so what is the priority there?

Is the data imported from some other source?  If so has the data been cleaned?
There is conditional formatting on the sheet.  Be aware that both CF and cell borders will not sort with the data.  Both CF and borders should be applied after the sort is done.
'---
Jim Cone

My mistake. I forgot to copy the title fields (col) over.  This is how it is arranged:

The sorting facilitates my checking of data to ensure that all rooms on every level has been given an entry.

The Rm ID : N315, S315 if sorted A-Z would automatically sequence the rows as N315, S315, N316, S316 - that is automstiv since alphabet N comes before S and 315 before 316 therefore it is not complicated in terminology.  Your example: A400 and B300 : there is some difficulty in syntax here. So far, according to Sort A-Z, N315, N316, N317.....(untill all N rows are exhausted) ...S315, S316, S317......  It cd be the case that Excel will have an issue when faced with an alphabet order versus numeric order. I tried to resolve this with the Rm ID2 which relist N315 into 315-N so that sort need only consider the numeric in the first instant. Your further input is welcomed. Thank you.  

See the attached workbook.  It has a copy of sheet1 that has been sorted.
I sorted by Column B and then by the new "Helper" column that just contains numbers.
The True/False column indicates which cells were moved during the sort...False means moved.
'---

Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Formats & Styles lists or removes unused styles or number formats - in the free folder)

Hi Jim, thank you so much. This has been truly helpful. I have copied all my data into a new sheet. However, instead of "Helper", I have decided to

call it simply "Numeric ID".  Are you an Excel coach. I will give you a tinker on how my DBase works out once I get through all the rows of data - ;-)

Xd