Inane Whittering #God only knows

Are you a computermabob person?

Good, because in that case, I need YOU.

If not, then my apologies because the rest of this post will probably make even less sense to you than my “normal” entries.

Please note that yes, this is work related, but only because I’m not sure how to approach the problem and seeing as I will most likely learn something from this, I thought it was appropriate to post it here.

Right, so being the lowly Praktikantin, I have been given the joyous task of compiling a list of contacts and their information for an event we’re planning.

My problem is that we have 9 files with various contacts in, and a master document with 22 sheets in it. The number of entries goes into the thousands.

The challenge?

To concatonate concatEnate (better, Wuzel?) ALL the data (whilst keeping the 22 sheets in the master) into one document with unique information, i.e. without duplicate contacts.

So far, I have reached the point where I understand that I either need to write a programme to do it for me (because I have no intention of getting square eyes or worsening the Carpal Tunnel Syndrome that is currently plaguing both my wrists and forearms worse than God’s vengeance on the Egyptians in the Bible) OR, I can use the vba function in Excel to do the work for me.

Because, you know, the computer’s just sitting there running a browser so I can stare at facebook work, it could be pulling its weight.

So, Oh Wise Ones, what I’m looking for are any hints or tips for a newbie such as myself on how to tackle the beast – do I concatonate each sheet in a separate workbook and then merge the workbooks, or is there a better way? Or any decent places online for tutorials. I would say books, but I already have far too many of those for my box here in Munich and getting stuff delivered from Amazon.co.uk isn’t easy here.

The reward?

I’ve yet to decide, but it will probably involve a penguin, a teapot, a shaved gorrilla some chocolate and my extreme gratitude.

And maybe a furry.

Advertisements

3 thoughts on “Inane Whittering #God only knows

  1. Hi Becca,

    I hope you’ve had some luck solving your concatenation problem. I’m afraid I’m quite a basic spreadsheet user, and have never tried concatenating or merging different books and sheets. I think it’d probably take me quite a lot of trial, error and Googling ;-)

    Good luck and keep up the entertaining and informative blogging!!

    1. Hi Martin,

      It took patience, googling and a fair bit of cursing, but I eventually got it done! I think I can now claim to know a little about Excel.

      I ended up reformatting every sheet in the 9 files so that I had exactly the same columns and then created one massive list. I added a column at the front which I labelled “Groups” and the data from each file was given a group name because the grouping found in the “old” master only occurred there. Then I used the automatic filter function that Excel kindly provides and I was then able to sort through the lines of data accordingly.

      Part I done. Part II was to make sure that there were no duplicates. Talking to my father (who works with Excel a lot, it turns out) if you highlight a column and click on “conditional formatting” – at least, I think that’s what you call it in English; my version at work is in German – there’s an option to either highlight duplicates or unique entries. After that it’s a case of eye-balling the highlighted entries to make sure they really are the same (each “rule” applies to one column at a time).

      Out of interest, I added in another column to compare and count the number of duplicated entries. Out of more than 2,000, over 700 were duplicated.

      Once I’d trawled through the duplicates and weeded the list down to just over 1,000 entries, I then had to set about checking contact details and finding any missing information. That took time!

      I’m currently in the process of restructuring our website and transferring the entire thing over to a new CMS – Typo3. It’s written in PHP and uses MySQL to output to HTML, but the UI (as usual) has a lot left to be desired… At least it’s nowhere near as bad as this piece of software.

      Then again, I’d much rather be given the code in notepad and left to tinker ;)

      I’m glad you like my whitterings and escapades!

  2. Hi!

    Congratulations on your concatenations ;-)

    It’s good to hear that you’re working on your company website too! Yes, CMS (what’s the plural? CMSes?). We use a Java-based one at QM for the School and departmental websites. It has a rather complex interface and can be slow to work with, but one can still ftp in to add more customised content if needed.

    I’ve recently put a Twitter feed onto the Language Centre website, through which I’ve been posting language-related news: http://www.sllf.qmul.ac.uk/langcen/

    I’m always looking for new ideas to improve the site, so any suggestions would be very welcome :-)

    Anyway, have fun!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s