Forum Discussion
Excel Custom Sort Beyond Default Range
- Thanks, Detlef, that's the example I was looking for! When I click on the "(More columns...)" and put SP in the box, then click OK, I get an error message. However, when I type in SP:SP it works perfectly. That's not intuitively obvious from the message, and I was unable to find that answer in any of the Help sections. Thanks again!
There are several columns in an Excel spreadsheet. From the Home page when clicking on Sort & Filter, then selecting Custom Sort, then going to the Sort by option it goes up to column SI, then gives a confusing format on how to select a column beyond SI. In this case the sort is on column SP.
"...SEVERAL columns" !!??!! Running beyond SI to (and beyond?) SP ???!!!
I can't even conceive of a table of data that has that many columns, and it's certainly more than "several," unless the first column is something like column SE. But even then, it boggles the mind to attempt to picture what sort of spreadsheet this is.
So I hope it's not asking too much to request that you give us an explanation of the overall structure of that Excel spreadsheet you're working with. Are columns A through column SP (and possible beyond) actually full of data? Does the table in question begin somewhere to the right of column A, and if so, where?
That aside--and I (and I suspect others) really would appreciate a clear description of what we're working with here--that aside, there is a SORT function, separate from the options available across the tool bar, and perhaps you could use it instead, but if we were sitting down face-to-face and I were a consultant you'd brought in, I'd want to understand your situation more fully before pointing you to any solution.
- I am working on a trust fund that has multiple accounts and several beneficiaries so I have to track the data for each bank and each individual.
Thanks for the reply and appreciate the comments.
We provide a marketing research service called PBRMarketTools. One of those services is ThePBR1000, which is tracking 1,000 of the largest publicly held corporations in the US. The spreadsheet has basic info like name & address info. In addition we provide 5 years of info on revenues, profits, earning per share, share outstanding, dividends, assets, equity, 5 years of many related ratios, number of employees, 10 years of monthend stock prices, several analytics on those stocks, 5 years of price earnings ratios, several years of monthend market capitalization values, number of equivalent shares, key information on the board, management, and the CEO, CEO pay ratios info, and much more. The spreadsheet starts in Column A, currently ends in Column XT.
The spreadsheet is also divided into 3 sections, all with the same format. The first third are the 1,000 rows of current The PBR1000 companies, there is a section of around 500 Prospect companies, and another section made of of around 500 companies that are no longer on The PBR1000 through acquisition, going private, going bankrupt, etc. In all there are about 2,500 rows.
Column SP is the date we last updated the CEO info. Periodically we sort from oldest to newest and update the info. We only do that for The PBR1000.
When we select the rows 8-1007(The PBR1000 companies), go to the Sort & Filter, Click on the Custom Sort, it only shows through Column SF, then has an option for more columns, but displays a format that is a little cryptic.
Hopefully that explains the structure of the spreadsheet for you and why it has so many columns.
Thanks again, hope this helps you understand the situation.
It seems you reached one of many restrictions in Excel.
It is very clear what the message says:
Click on the button on the right, select the column, click on the button on the right again, click OK.
Alternatively you can type the column reference into the box: TS:TS
- Thanks, Detlef, that's the example I was looking for! When I click on the "(More columns...)" and put SP in the box, then click OK, I get an error message. However, when I type in SP:SP it works perfectly. That's not intuitively obvious from the message, and I was unable to find that answer in any of the Help sections. Thanks again!
And I gather--inferring from what you've said--that each corporation's data is all in one row. That is to say, each new year's worth of information (up to five; or beyond?) is all on that same row. And so forth. You kind of imply--"currently ends in Column XT" [emphasis added]--that it could continue to grow.
You are--this shouldn't come as a surprise--pushing the limits of Excel as a database manager. Have you considered using Microsoft Access? From all you've said, although it is possible for one to create a relational database in Excel, I think you'd be better off storing all the data in Access and then conceivably doing whatever reporting you need to do within Access or via Excel, sourcing the data in Access and using Excel's Power Query capabilities to extract/summarize/sort/etc.
Basic point: however you do it, I'd recommend breaking up the database into a relational model, which will be much more robust. I suspect that what you currently have could be converted fairly easily. It might involve engaging the services of an expert. [In no way am I offering myself as that expert. I've been retired for over 20 years now, and haven't worked explicitly with Access in all that time, but have a deep respect for a well designed relational database.]
- Thanks again for the reply!
The table stays pretty much the same size. Each year we purge the oldest year's financial and stock related data. You are right, there is 1 row per company. We provide the updated data monthly. Our clients like it in Excel because it is the most common tool used by our users. Some of our more sophisticated users import a lot of the data into Access, Tableau, other hierarchical and relational databases, various CRM systems, a variety of graphics packages, etc. But again Excel is the preferred delivery method to our subscribers.
Once again, our issue is with the process and related syntax to do the sort on columns past SF using the Sort & Filter option on the Home page.
