I'm generating a spreadsheet from XML data, & it looks something like this: Client Name Total Val Cash Equities Mr Bloggs (Portfolio) £50,000 £3,000 £47,000 Mr Bloggs (Model) £60,000 £4,000 £54,000 Mr Bloggs (Difference) £10,000 £1,000 £7,000 .... Mr Fred (Portfolio) £30,000 £1,000 £29,000 Mr Fred (Model) £35,000 £2,000 £33,000 Mr Fred (Difference) £5,000 £1,000 £4,000 So client name in first column, total in second, cash in third, then the fourth onwards will be various share categories (equities, unit trusts, gilts etc - could be many columns). Each client has three rows - Portfolio, Model & Difference. There could be several hundred clients. The problem I've got is the people I'm doing this for want to be able to change the sort order in Excel. E.g. they may want it in ascending Total Val order, but obviously keeping each client's three rows grouped together (so in the above example Mr Fred's rows would move to the top). Then they might want to have a look by descending Equities, so Mr Bloggs' rows would move back to the top. My Excel skills extend to opening a spreadsheet, & on a good day, closing it again. I have no idea if what the client wants is even vaguely doable, let alone how to do it. Help! -- Krusty '03 Tiger 955i '02 MV Senna (for sale) '96 Tiger (for sale) '79 Fantic Hiro 250 (for sale) '81 Corvette (for sale)
Data > Sort By ... > [Sort Dialog] Sort By: Total Val Then By: Client Name ....because you can specify more than one sort column.
In communiqué Would that keep the three rows for each client grouped together? -- +----------------------------------------------------------------+ | Pete Fisher at Home: | | Voxan Roadster Gilera Nordwest * 2 Yamaha WR250Z | | Gilera GFR * 2 Moto Morini 2C/375 Morini 350 "Forgotten Error" | +----------------------------------------------------------------+
That mixes all the clients up. I'm pretty sure it can't be done by sorting - what I don't know is whether it can be done by macros or something. -- Krusty '03 Tiger 955i '02 MV Senna (for sale) '96 Tiger (for sale) '79 Fantic Hiro 250 (for sale) '81 Corvette (for sale)
Sadly not. -- Krusty '03 Tiger 955i '02 MV Senna (for sale) '96 Tiger (for sale) '79 Fantic Hiro 250 (for sale) '81 Corvette (for sale)
Not an Excel[1] expert, but IWHT that you would have to get each client's data all into one row first? [1]dislike it with a passion
The simplest way, although not elegant, would be to have additional columns. If, for example, the Equities value you're going to sort on is the Portfolio, you'd have an "Equities Sort" column with £47,000 in each of Mr Bloggs three rows and £29,000 in Mr Fred's three rows. You could then sort on this column first and the client name second. Personally I'd play around with pivot tables.
Heh, don't worry, I haven't forgotten. Just trying to get a new client live, then you're next on my list. -- Krusty '03 Tiger 955i '02 MV Senna (for sale) '96 Tiger (for sale) '79 Fantic Hiro 250 (for sale) '81 Corvette (for sale)
I did think of that as a last resort, but I really doubt they'd go for it. They're a fussy bunch. Seems to be getting lots of votes - I'll have a looksee. -- Krusty '03 Tiger 955i '02 MV Senna (for sale) '96 Tiger (for sale) '79 Fantic Hiro 250 (for sale) '81 Corvette (for sale)
We should form a sect. Once our site has been 'refreshed' am going to have to cobble up things in Excel that were an absolute doddle with a proper database 4GL. -- +----------------------------------------------------------------+ | Pete Fisher at Home: | | Voxan Roadster Gilera Nordwest * 2 Yamaha WR250Z | | Gilera GFR * 2 Moto Morini 2C/375 Morini 350 "Forgotten Error" | +----------------------------------------------------------------+
Actually I can't think of how to do it with a pivot table either. The problem is trying to sort on part of a column.
Shirely the wrong way around? Sort By: Client Name Then By: Total Val Would keep the names together. [/QUOTE] And the other two rows ? -- +----------------------------------------------------------------+ | Pete Fisher at Home: | | Voxan Roadster Gilera Nordwest * 2 Yamaha WR250Z | | Gilera GFR * 2 Moto Morini 2C/375 Morini 350 "Forgotten Error" | +----------------------------------------------------------------+
Having had a quick look, I can't see how they'd help, & there's no way on earth the client would be able to use them even if I could convince them to try. I think the only answer is to give up. -- Krusty '03 Tiger 955i '02 MV Senna (for sale) '96 Tiger (for sale) '79 Fantic Hiro 250 (for sale) '81 Corvette (for sale)
YTC. -- Krusty '03 Tiger 955i '02 MV Senna (for sale) '96 Tiger (for sale) '79 Fantic Hiro 250 (for sale) '81 Corvette (for sale)
You'll have to sub it out to WUN then. Having played about a bit with the Excel workbook that the NHCA archivist set up to record the times at hill climbs[1] and produce a results sheet, I'm sure. given the consistent worksheet structure, it can be done with VB macros, but WUN probably knows an easier way. [1] Whilst working out how to read it as an automation object from Visual Foxpro so as to display real time leader boards etc. -- +----------------------------------------------------------------+ | Pete Fisher at Home: | | Voxan Roadster Gilera Nordwest * 2 Yamaha WR250Z | | Gilera GFR * 2 Moto Morini 2C/375 Morini 350 "Forgotten Error" | +----------------------------------------------------------------+
Ranking and pivot table are the way to go. Use a formula to rank each client - ie in this case, Fred=1, Bloggs=2 then drop the data into a pivot table around the ranking. Off the top of my head, you should get something like: Client Name Total Val Cash Equities Ranking Mr Fred (Difference) £5,000 £1,000 £4,000 1 Mr Fred (Portfolio) £30,000 £1,000 £29,000 1 Mr Fred (Model) £35,000 £2,000 £33,000 1 Mr Bloggs (Difference) £10,000 £1,000 £7,000 2 Mr Bloggs (Portfolio) £50,000 £3,000 £47,000 2 Mr Bloggs (Model) £60,000 £4,000 £54,000 2 You can then apply a Macro to a button graphic so that your client can just hit the button to run the ranking and pivot table in future. If you can't figure it out from here, drop me an email as the headers and I'll try and knock up an example for you.
I think Simon has the right of it when he mentions getting your data in to single rows for each client. You can then construct a list and have blocks of three rows (on a different sheet if required) as you want with the data displayed linked to that master set of rows. When you change the order of any of the master columns the blocks of data will change. See screenshot here (prolly need to save it or go full screen to see all its beauty): http://www.ps-fisher.demon.co.uk/pickchurs/krusty.jpg Fucknose how to quickly replicate the client 'record' blocks though I had to construct them by hand. Then there is reading your XML source data to the master rows if it arrives in the format you showed , so wibble flip. In any case I think your sums are wrong for Mr Bloggs (Model) unless he has model loot stashed in something else. -- +----------------------------------------------------------------+ | Pete Fisher at Home: | | Voxan Roadster Gilera Nordwest * 2 Yamaha WR250Z | | Gilera GFR * 2 Moto Morini 2C/375 Morini 350 "Forgotten Error" | +----------------------------------------------------------------+
