Copying And Pasting Values Across Multiple Rows In A Filtered List In Microsoft Excel
Posted by Kieran - 30/04/08 at 11:04:37 amThat sounds like a complicated task in itself, without even considering how unhelpful MS Excel can be…. What exactly am I trying to achieve?! How frequently does this need occur?
I work with massive spreadhseets. Sure I should probably use a specific database application, but seeing as Access sucks balls and everybody in my company literally wants to marry Excel I have little choice in the matter… I run a couple of databases, the specifics of which are unimportant, which have around 30-40 thousand rows acting as records. I have the data set up with auto filters for the coloumn headers so that I can show data that matches specific criteria in each coloumn (show all rows where postcode starts “AB” for example) or order rows according to any specific coloumn (like sort rows in order of postcode).
The problem occurs when working on specific subsets of this data. Fow example, say I am working on all accounts within the AB postcodes, where there is no manager name. I can add a formula into the top cell, a vlookup for example, and autofill this formula to the bottom of the displayed rows. Excel intelligently only applies this formula to the visible cells which is great… the hidden rows between which have been excluded by the filter are unaffected, which makes sense.
Now, with 40,000 rows and 15 coloumns, my spreadsheet has 600,000 cells. Thats over half a million pieces of data to hold in relation to each other. Obviously Excel begins to labour - changing filter criteria can take a couple of seconds, even using the end key to navigate to the bottom of the data can take a couple of seconds where its normally instant.
This in itself is not an issue but the problem is compounded with formulas. Every time you change a filter, Excel works out which rows to show and then recalculates every formula in the sheet. The more formulae you have the longer it takes until eventually you’re waiting hours to show just one postcode.
Most people profficient or at least familiar with Excel formulas know copying and pasting the cells that contain formulas as values (eg Copy, Paste Special, Paste Values) cuts out this added complication.
But that isn’t that easy when working with filtered lists! Try and copy a selection of cells in a filtered list and then paste them back on top as values and you’ll more than likely get some errors. The problem stems from hidden rows between the rows you are copying/pasting. Excel fails miserably to ignore these hidden cells, and if it works at all it will almost certainly overwrite hidden data with the copied values, leave formulas in other rows and generally fuck about with your karma.
The obvious work around is to release all criteria on the filters (Data -> Filter -> Show All is the easiest method) and copy any coloumn affected by your newly added formulas and then immediately paste them as values. This effectively replaces all formulas in these ranges with their “answers” and leaves any formatting intact.
The problem is, when I’m repeating this process maybe 10 - 20 times a day it gets intensely frustrating. Once you’ve done this, you still need to reapply the criteria you had and in a big spreadsheet this whole workaround can take a couple of minutes as Auto Filter needs to recalculate its visible data and all the formulas you haven’t changed yet. These minutes soon add up!
So I’ve hit another solution - a very simple macro which I’ve currently got assigned to a toolbar button which gets more use than any other.
Public Sub Values()
Dim cell As ObjectSelection.SpecialCells(xlCellTypeVisible).Select
For Each cell In Selection
cell.Value = cell.Value
Next cellEnd Sub
Once I’ve copied the formula down (or across etc) I select the cells that contain formula in the usual way and then run the macro.
It first takes the selection and very quickly narrows the selection to only visible cells. This is the most important part because as discussed, any initial selection you make contains the hidden cells between the selected visible cells.
Lastly it cycles through each cell in this new narrowed selection and copies the value directly into the cell. So if the cell contained the formula =2+2, the macro will take the value 4 and reset the cell to that value. This works for any formula, however complex.
The result is the ability to copy formulas and paste their values into any selection of cells, whether the are filtered with Auto Filter or not. Its how Copy and Paste Values should work - in fact its how copy and paste should behave full stop, but thats a different less elegent problem.
Reading back through it all seems a bit over the top, but so far it saves me about half an hour a day which is about 120 hours a year (taking into account holidays!) - thats 3 and a half weeks of my working life I got back! If only I hadn’t just run out of things to do!!
Getting Married
Posted by Kieran - 29/04/08 at 12:04:43 pmWell, I asked shelley to marry me and she said yes! So I don’t know what that means - it certainly feels odd in that it feels different and the same all together.
I feel like I always did - Shelley makes me incredibly happy and everytime I see her I smile inside. That feeling hasn’t changed. We still laugh and smile with each other, we still get passionate about everything that happens in the world, we still cuddle at night and I still wake up every morning and smile because she lays next to me.
However it now feels different because I know she too feels this way for certain. I now know this is forever and we fully intend on spending the rest of our lives together.
Thats not as daunting as it sounds - we already bought a house together.
Anyway, over the next year we need to fix the date (Summer ‘09), settle on a colour scheme (I will have input!), book a venue, rob a bank to fund proceedings, buy a dress, hire suits, sort decorations and invites, get a cake (must be gluten free and fruit cake can fuck off), get wedding rings and about 100 other expensive things.
Crackin!
My Hero
Posted by Kieran - 24/04/08 at 12:04:52 pmIts funny as you get older, you get more cynical and your heroes become normal people. However, this guy is a true legend.
After waiting 8 months for payment he decided enough was enough and took the entire extension down with a sledgehammer…
Seems pretty cut and dried really, he provided something at a cost of £15,000. He never saw the money, so he “took the goods back”. Ok so he can’t reuse the bricks or get his time and effort back, but if he didn’t get paid why should Mrs Dovey get an extension for nothing?
Classicly though some people are already up in arms saying its “not her fault she couldn’t afford to pay” and this will have caused “intolerable stress”. Firstly, if i can’t afford something I don’t buy it. I don’t take it and then say I can’t pay - thats theft. Morever she wasted a week of this mans life. Furthermore any stress cause to Dovey is miniscule in comparison to that of the builder Mr Gray who said:
“It has been very stressful on my partner and family who have also had to deal with it all.”
He estimated that the building work, legal fees, bank charges and lost work cost him about £22,000. So really Dovey still owes him £5,000 and it would have been nice to see the council force her to pay, by use of baliffs if required, as they actually own the property.
The Bussiness Of Dying
Posted by Kieran - 24/04/08 at 12:04:42 pm
Finished On: - 24th April 2008
I picked this up after reading Relentless - a book which didn’t promise much but delivered beyond expectation.
The Business Of Dying is Kernick’s first novel which is apparent from the off - not quite as polished as his later books it still revolves around his strongest idea yet, that of Dennis Milne full time cop, part time murderer.
A man with a twisted moral compass, he’s sent to kill 3 drug dealers but it immediately goes awry when it becomes clear these men were not drug dealers and not corrupt.
The thing is the book doesn’t then descend into so much postulating about the rights and wrongs - Milne has this figured out already, he’s intelligent enough to realise this is a bad situation, but the importance of surviving compels him ever forward, and that’s what makes the book exciting. Its not a moral lesson, its a story about survival in a fucked up world.
The book is then pushed into overdrive as Milne becomes embroiled in an investigation that he can’t let go and slowly but surely the two intertwine - not in a coincidental slapdash way, but a very deliberate execution of a believable plot that drags you slowly in.
The final third of the book slips easily from twisted detective thriller to full on revenge action with a neat intelligent ending and the book makes no excuses for the actions of its central protagonist.
Some books maintain an even keel, some books dip in the middle and some books dip at the end. This is one of the few that gets better throughout until the biting crescendo.
Altered Carbon
Posted by Kieran - 18/04/08 at 08:04:35 pm
Finished On: - 9th April 2008
I used to read sci-fi all the time. And then something somewhere slipped - my sense of adventure, my technological interest (its hard when you work in the field to get excited by it) and the lack of decent sci-fi on supermarket bookshelves (I have a mortgage now, no excuses).
I was leant this book in an experiment - a challenge to myself to read something I would not normally pick up and something to inject some fun into reading. The unknown - how i felt years ago before the Da Vinci code ruined my opinion of humanity.
This book delivered that in spades - the story is cracking from start to finish, a real page turner despite some of the heavy prose, full of twists and turns. Essentially this is old fashioned “hard boiled” detective noir pasted into a rich tapestry of future technology. Even the writing style is straight from the 20th C, but with stunningly depicted future backdrops.
The real cleverness of the book is its ability to introduce technological advances with a brief mention or underhanded description - just like passing comment on that mundane coffe table. It makes you feel like you’re their - you even feel like you’ve been dragged across the universe with Kovacs the central character at the beggining of the book, such is the power of the descriptions.
All I can say is this book has whetted my appetite for more - more Richard Morgan and more Takeshi Kovacs.
An excellent (and surprising) novel….
Lindsay Lohan - When Will It End?
Posted by Kieran - 10/04/08 at 06:04:07 pmFully aware of the old adage “there’s no such thing as bad publicity”, everyones favourite not-quite-a-teenager-anymore crackhead Lindsay Lohan has hit gold with her latest plan to get back into the limelight (after snorting drugs off any white surface that was nailed down).
She’s going to get naked - she’s going to join the oldest profession in the world and go full frontal for cash. And it gets better…
She’s going to drop her kecks for the relatively modest (hahahaha) sum of £40,000 (about $10) in order “to build up an image as a mature, responsible actress.”
I shit you not.
An insider told The Sun:
“Lindsay doesn’t care she’s getting paid peanuts. She wants to remind people she can act and that she is worth hiring.”
Another source added:
“She is fully aware of the potential of her body. Lindsay wants to build up an image as a mature, responsible actress.”
Finally once her faff has been distributed around the world in high-def she’ll be releasing another album.
Says she:
“I want it to be kind of Kylie Minogue-meets-Rihanna. I hope to tour with it and I hope to really promote it.”
Can’t wait.
(And no shitbeaks, I don’t read the Fascist Mail Daily)
Power Play
Posted by Kieran - 10/04/08 at 11:04:30 am
Finished On: - 9th April 2008
I read Paranoia and thought it was amazing - the detail which added to the story, the story itself and the flawed characters all made for an exciting and interesting story.
Finders latest, Power Play was an interesting idea, with plenty of twists and turns - exceptionally tight its a real read in one go novel as the bulk of it takes place over a couple of hours (though before and after the event are spread over days). The characters were quite well written - they didn’t need to be fleshed out too much as they were detestable executives (faceless and childish you know the stereotype) whilst Jake the main character has an interesting past (told through a series of short sharp flashbacks) which contributes greatly to the central story.
Overall I’d have to say this book was good, but not excellent - the parts were all there, but together they didn’t quite capture the excitement of the situation for me. However its short and sharp which adds to the enjoyment. Its like an episode of 24 rather than an entire series - exciting enough but you’re still waiting for the payoff after the last page.
Hitlers Torch
Posted by Kieran - 09/04/08 at 09:04:22 amI came across this today (Hitler Passes The Olympic Torch To China) and whilst I don’t normally follow up my thoughts with further writing, the article raises a few pertinent points.
Whilst I wouldn’t go as far as to suggest the mass genocide of the Nazi’s is being emulated in anyway by the Chinese they are still “cleaning up” on a regular basis. Much aside from the continual presence of Chinese rule inside Tibet, the Chinese are forcefully removing thousands from their homes in order to build new facilities and roads, enslaving large groups of unemployed people to work on these programmes and even imprisoning those who complain.
Whats happening in Tibet is a moot point - its been happening for years and will continue to happen (unless the West decide to invade them to). Its not disimilar to the occupation of Iraq and Afghanistan by the West. The real issue is not even the continuing human rights abuses China overseas on a routine basis.
The real problem for me, and the reason the games should never have been awarded to China in the first place is the escalation of these abuses in the name of the games. The Olympics may well give much to the world of sport, but they are taking much more from innocent citizens who are unable to fight back.
Olympic Torch Protests 72 Years Too Late
Posted by Kieran - 08/04/08 at 09:04:56 amI love how this whole Olympic debate has blown up in recent days. Should Konnie Huq have stuck to her principles and refused to carry the torch? Should she have just fucked off back to kids TV?! Fair enough Steve Redgrave and Dame Kelly Holmes - but Peter Kenyon? The Sugababes? ON A BUS?!
The whole thing was a great example of how to fuck things up in a way only the British could. A £2m price tag for security that amounted to Rick and Jim from the Met rugby tackling anyone not wearing a shellsuit. A corporate jolly where Coca Cola and Samsung picked some runners (because the aforementioned Kenyon and Denise Van Outen do so much for track and field athletics, and are keen supporters of synchronised swimming I expect). Check the route - Wembley (a year late, £200m over budget), O2 Dome (remember that shambles, symbolism I tell you, of an empty political party with “no politik”) - in fact they could only have made it worse by flying it into Terminal 5 at Heathrow (fingers crossed it didn’t get buried under hundreds of lost bags).
Don’t even start on the politics - China is bad. Fine and accepted by everyone who isn’t within China’s vast borders. People in China, I’m not so sure they all hate the regime as much, although obviously the BBC will only ever give air time to the dissenting voices - there’s no fun in balance. But the point is, its all irrelevant.
The Olympic Torch relay was started by none other than spectacular Nazi fascist tosspot Adolf Hitler (well credit where credit is due, it was Goebbels idea). The idea was a flame travelling from Athens to Nazi Germany would symbolically link the blossoming Nazi regime with Olympian ideals. What utter horseshit! We shouldn’t have even crossed the water for that moral catastrophe but go we did and we even stood tall when Hitler refused to shake hands with black athletes.
So the best way to erase the whole sorry prelude to a gigantic war which left hundreds of millions of innocents dead? Bring on the flame! Good PR for the Nazi party = good PR for the International Olympic Committee. See, Hitler was closer to the cretins at the IOC than he could ever have possibly imagined.
So far the only nation to have picked up on this were the Australians in ‘56 - a student managed to hand over his own Olympic torch made of a tin can a chair leg and a pair of pants to the Mayor of Sydney. Suffice to say everyone was pretty embarrassed midway through the party when the real torch arrived.
Anyway, if current world ideals are anything to go by, in 2012 China may relax its rules of free demonstration and possibly even encourage civil unrest on its streets for once. The reason? Chinese campaigners speaking up for the British peoples freedom under the tyranny of a government who have taken it upon themselves to invade two countries in recent years and who shoot immigrants on underground trains.
Javascript - Numbers to Currency
Posted by Kieran - 07/04/08 at 12:04:04 pmSo a small project recently saw me create (or more accurately complete) a web page to be shown on a large screen tv at work - the idea being the page would track daily install figures and show remaining targets etc.
The problem was the system took numbers from a static file, which was updated daily. The targets were preset and the remaining figures were calculated from these numbers. Obviously using commas and pound signs would mess up the math. I could strip the special characters first, but then I’d have to rebuild the totals with special formatting. If I was going to format the numbers at any point i might as well store them as flat integers and format them everytime i wanted to show them.
So this function takes any number, rounds it to 2 decimal places, inserts commas and adds a pound sign. Swish. To call it, wrap anything you want to display as money in the money function… eg
' + money(array[i]) +';
That was fun. I need to get out more.
<SCRIPT LANGUAGE="JavaScript">
<!-- Kieran Delaney -->
<!-- http://kierandelaney.net/blog -->
<!-- Begin
function money(num) {
num = num.toString().replace(/\$|\,/g,'');
if(isNaN(num))
num = "0";
sign = (num == (num = Math.abs(num)));
num = Math.floor(num*100+0.50000000001);
pence = num%100;
num = Math.floor(num/100).toString();
if(pence<10)
pence = "0" + pence;
for (var i = 0; i < Math.floor((num.length-(1+i))/3); i++)
num = num.substring(0,num.length-(4*i+3))+','+
num.substring(num.length-(4*i+3));
return (((sign)?'':'-') + '£' + num + '.' + pence);
}
// End -->
</script>
Powered by WordPress with GimpStyle Theme heavily modified by Kieran Delaney.
Entries and comments feeds.
Valid XHTML and CSS. 16,543 spam comments ignored.