Thursday, August 18, 2005

hosting memes: make excel do the dirty work

As we all know, hosting a meme can sometimes involve quite a bit of work by the host in putting together summaries. Sam wrote about this previously in "Hosting a meme - be careful what you wish for!" For the Eat Local Challenge month, I came up with a way to track what was going on via an excel spreadsheet. It's pretty geeky, and I wouldn't recommend it if you are not an excel junkie. But it is possible to enter the information for your participants and then have excel build the code for you.

By making a formula in excel using an ampersand, you can make excel combine text. In the example below, the formula =A1&" "&B1 is used to create "tom jones".


this formula can be copied into every cell of the C column, and the result will change in each row.

Things get a little more complicated when you try to do entire sentences, but it is possible. In the participant list that I created, you will notice that there is a pattern to every single blog listed. It's something like NAME from BLOGNAME (LOCATION). < reference =" '"> BLURB < / a >

The images below give you a clue as to how I built this in Excel.



Now, most people don't do their posts quite as uniform as I do. Different people like to have the post links within paragraphs and stories. You could still use Excel to do the main work, you would just have to be a little more creative. In the end, I was able to take all of the data from the "code" column and paste that straight into a blog post and it worked fairly seamlessly. I also cut and pasted all the urls from the "trackback ping" column to ping everyone I needed to.

For me personally, building the data in Excel and tracking it this way was much easier than trying to proof all of my code in html and typepad.

Other benefits to doing the meme post in this manner include:

* Easy ability to sort the blogs however you want them sorted
* You can see where there are blanks that need to be filled in and deal with that.
* You know that if the code is correct for one entry, it will be correct for all entries.

I have posted the actual excel sheet here if anyone would like to play around with it. Document password = "geekcode". If you'd like to try this method and need help, feel free to email me at fogcityblogATyahooDOTcom.

This is probably one of those things that only works for A-R freaks like me, but thought I'd put it out there in case it can help anyone else in the future.

7 comments:

sarah said...

from an AR freak to another...

that's awesome! lol! i am an excel monster (have to use it quite extensively for my day job, lol!) and use it for everything from recipes to calendars.

:)

Andrew said...
This comment has been removed by a blog administrator.
Sam said...

ha ha ha - this is a very nice idea except i am, like totally anti-Microsoft, don't use their software if i can help it.
I don't think i even have excel.


I have another organisational idea for hosting memes which i am going to test out when i next a host a meme. It's gonna be very soon, but i need to test it before I announce it.
too bad i didn't think of it time for Rachael Ray, which i think might turn out crazy.
(I have had some hints so far that they may be a contingent of non-food bloggers entering too!)

we'll see...

paul said...

Awesome tips.

I've managed to avoid doing any work in Excel, even though I've done years of corporate work. People send me memos and notes done in Excel, and I wonder about those people. But hey, I do the same thing to people with html.

I need to organize my food blog list like this. Not only would it allow for searching and categorizing, but it would make it easier to export to other systems for others who want to apply other tools to it.

Culinary Fool said...

Jen:
I think this is a great tip and tool! Kudos to you for sharing.

And for those of you who prefer not to use excel, this would work in any spreadsheet on the market.

~ B

Owen said...

For those that just want to track the URLs and take notes, check out backpack at www.backpackit.com - you get a free account with five pages and I just redo pages when a project is over - it is awesome for taking nots off web research.

I do Paper chef by tracking all the emails and comments together and then keeping a list of the URLs and then going to them one at a time and wrriting them up as I go. I can do one every three or four minutes that way.

Elise said...

Jen - wow. That is being organized! I had no idea you could use Excel that way. Thanks for posting the tip.