Past EED rants


Live leaderboard

Poker leaderboard

Voice of EED

Wednesday 13 November 2002

Simple excel problem [am]

Once upon a time I used to be able to write Visual Basic macros for Excel but I've forgotten in the depths of time. Actually I don't know if I even need a macro but I've got a simple problem I'd like to solve - I've got a huge 3000 item excel list of stock names and a credit rating next to them. In fact there's only about 200 stocks and the rest is line for line duplication (they're sourced off different accounts). What I would like to do is quickly eliminate all the duplicate lines and just be left with one version for each position.

Now once upon a time I could write a quick parser but does anyone know of how to do this easily? Or if it has to be VBA, can someone remind me of the syntax for the language (I was reasonably proficient)? The sheet is just a version of this

Bond Stock Name XXX | Credit AA Bond Stock Name XXX | Credit AA Bond Stock Name XXX | Credit AA Bond Stock Name YYY | Credit B

- All I want to do is delete the dupes and be left with only line 1 and line 4 see. Should be a doddle for blokes with the brains the size of planets. Beer is teh reward!




  1. Save it to a tab delimited file and grab a win32 version of the unix utility 'uniq' from tucows then reimport it.

  2. Failing that I'll write you a script to do it. I'd need some example data (tab or comma delimited like spiny says) and that's it.

  3. Highlight the lot, Click Data, Filter, Advanced Filter and tick 'unique records only'. Bosh.

  4. ^^^^^^^Excel guru = IT manager