Forum Discussion
Hi! im really confused on how to sort these US patent numbers
thank you very much, i tried that, thanks for the info but im getting the same results Riny_van_Eekelen
you can look in the file attached. I dont know why some numbers are just not sorted correctly.
NoelGilling Thanks for attaching the file. That helped! It seems that the blank cells aren't really blank, and the behave odd when sorting. I wonder where the data comes from. Is it a download from somewhere or perhaps a csv file originally? And you do realise that we are sorting text here. Thus, "244/123" gets sorted before "244/79".
Furthermore, I noticed that you used TRANSPOSE/SORT/TRANSPOSE on the entire data array in one go. Hadn't thought about that, but it doesn't seem to work. You need to do it row-by-row and copy the formula down the column. Don't ask why.
The attached workbook arrived at sorted data in three steps. I took your raw data and replaced the "blanks" with "z". Then, I sorted that table using TRANSPOSE/SORT/TRANSPOSE row-by-row. And in the last sheet I replaced "z" by blank. Not the most elegant way, but it works.
Also attached a file that achieved the same result using power query.