Dan Antion

Recap Explained

Caution – Geeks

It has been over a year since I started publishing the Thursday Doors Recap page on Sundays, but It’s only been about a year since the process of producing that page has been on autopilot. Several many lots of OK, a few people have shown some interest in the behind-the-scenes story.

That’s your cue to skip to the gallery – it gets geeky beyond this point. Before you go, if you’re a Thursday Doors participant, the Recap Page continues to generate over 100 additional clicks, and people who include ‘tags’ continue to receive the most.

When I first got the idea to create the recap page, I built most of the processing in Excel. It was fast, easy to create, and I could see the results as I built/tweaked the formulas.

I use JavaScript (a programming language) to gather the links on my Thursday Doors post. I fine-tuned that script to target only the Comment area of the post, greatly reducing the number of links. Still, each week, there are upwards of 1,000 links. See the example below.

10 links in this single exchange.

And no offense to GP Cox, but I don’t want any of those links in the recap. Fortunately, I can filter many of them out. I don’t want links containing “gravatar” and I don’t want any links containing “nofacilities.com.” Filtering out those two strings reduces 10 links to one – “https://pacificparatrooper.wordpress.com/” – I don’t want that one either, but extracting 100 links from my page was manageable, so I continued with that until I found a better way.

Here’s your chance to think like a programmer. I can feel you shudder.

“https://photographias.wordpress.com/” is a link to the parent page of Sofia Alves’s blog.

“https://photographias.wordpress.com/2022/04/21/thursday-doors-lisbon-7/” is the link to Shofia’s Thursday Doors post. How can I teach Excel to know which is which?

If you said “count the number of ‘/’ characters, you get 100 bonus points. Since you’re still here, I’ll explain. The formula below is how Excel counts the ‘/’ characters.

=LEN(B19)-LEN(B19)-LEN(SUBSTITUTE(B19,”/”,””))

LEN(B19) is the length of Sofia’s blog URL

LEN(B19)-LEN(SUBSTITUTE(B19,”/”,””)) is the length of her URL with all the ‘/’ characters removed.

The difference is the number of slashes removed. If it’s greater than three, it’s a link I want to keep. So, I sort the block of links by the column containing the number of slashes and I delete all the rows where the answer is three.

Initial sort on the number of ‘/’ characters.

That reduces the number of links from about 100 to about 45. This is a process I can move into my JavaScript script, but I haven’t yet. It only takes a few seconds in Excel, so my motivation is minimal.

If you’re thinking, “the recap page rarely has over 40 links…” Give yourself another 100 bonus points. You’re right. Some of those links are duplicates. Duplicates are caused by people who leave a comment on my blog and include a pingback on their blog. Don’t take this the wrong way – I love the pingbacks – you’re spreading the link to Thursday Doors by including those!

When I run my script against my blog page, I get a table of entries that look like the image below:

This table is the result of the JavaScript extract routine I run against my Thursday Doors post.

I paste this into Excel. Then I can work with it.

The relevant results extracted from my comments.
  1. Title
  2. URL
  3. Tags
  4. Order in which the URL was linked to my blog (either by comment or pingback).

Now, back to those duplicates. I sort the data area by the URLs. Then I use Excel’s Cell Highlight feature to highlight the duplicates and then I delete one of the rows of data. Note: I keep the row with the tags, but I give it the lower of the two numbers representing order of arrival.

I’ll keep the more informative text.

To get the tags to look nicer, I have a column with the formula below:

=IF(ISNUMBER(SEARCH(“#”,C14)),SUBSTITUTE(SUBSTITUTE(C14,”,”,” “),”#”,” “),” “)

In other words, if the Tags column includes a hash character, I replace the ‘#’ with blank spaces. I do the same for commas. That’s hard to see because the formula parameters are separated by commas.

Once I’ve manually eliminated the duplicates, I sort the remaining 30 or so data rows by the order in which they were posted. Now that the data is formatted and sorted, it’s time to create the table. Actually, since I’m using Excel, the table is constantly being formed and reformed.

If you’ve ever seen the Recap page, you know there are three columns: The URL to the blog post, the location of the blogger, and any tags they included. Here’s how I do that:

=HYPERLINK(G13,L13) – This formula creates a hyper link from the text in L13 and the URL in G13.

=IF(ISERROR(VLOOKUP(J14,BlogNames,3,FALSE)),””,VLOOKUP(J14,BlogNames,3,FALSE))

This function might not be familiar to many Excel users, but it’s one of the most power functions Excel offers. VLookup() and it’s companion HLookup() allow you to look for a string in a table and retrieve something associate with that string. In my case, I am looking for the URL of the blog’s parent page in a table of information provided by the form the bloggers filled out online. This gives me the friendly name of their blog (which I don’t get if they only leave a pingback) and the location they provided.

The lookup table I created in Excel from the online form data.

In layman’s terms, I look for the URL in the table. If I don’t find it (ISERROR), I leave the result blank. If I do find it, I take the result from the third column of that table. Then I copy the tags from the column where the ‘#,” was removed. The result table is shown below:

This is what I copy into the WordPress page.

Next, I open WordPress, edit the Recap Page, delete the old table, copy the result table from Excel and paste it into the WP Editor. Then I set a couple parameters and I’m done.

While it sounds like there’s a lot going on (there is), Excel is doing most of it. Preparing the Recap Page usually take me less than 15 minutes from the time I run the JavaScript script to the time I’m pressing the “Update Page” button in WordPress. If you made it this far, give yourself 1,000 bonus points.

Signs of spring – soccer goals and (mid-level right) porta-potty.
Moon fading – leaves thickening.
This Pileated Woodpecker has opened a diner in this tree.
Pileated Woodpecker working hard to get his breakfast.
“Hi there. Did you bring peanuts today?”
Maddie decided the warm Trex was too hard to pass without a nap.
Our smallest quince bush.
Lily-of-the-Valley mini-forest.
Well, that tulip didn’t last long.
This is good to see. Softball games in the park. Birdball can’t be far behind.
The sun is struggling to break through.
My seasonal landmark is filling out.
This bluejay can’t hide yet.
Bunny butt for River.
Our forsythia is still blooming, So I’m still taking pictures.
We are still having windy days, but this is always a good sight,

78 comments

  1. That was a very interesting explanation and merely reinforced my desire to never, ever become a host. However, I am grateful that lots of fellow bloggers are, including yourself, Dan.

    Liked by 3 people

    Reply

  2. My trouble is, I receive notifications in my email by those that click the Like, Comment or Follow – BUT – now I no longer get a link back to their site. I know I must have people thinking I’m just ignoring them. If I don’t already follow a blogger, I have very little chance of finding them otherwise.

    Liked by 1 person

    Reply

  3. I know you’ll forgive me skipping to the photos. Nice pileated shots, those boys are pretty hard to find around here though lots knows we hear them. Bunny butt -1. Tulip – 0.
    🥴

    Liked by 1 person

    Reply

    • Absolutely- no required reading today. I’ve been hearing that woodpecker for weeks, but this was the first time I saw him where I could get a picture. Otherwise, he’s a blurry bump in a distant tree. Yeah, bunny visited the salad bar 🙁

      Liked by 1 person

      Reply

  4. Yep, I went straight to the gallery! That I can understand.🤗

    Love seeing Maddie stretched out on her deck getting a few 💤💤! And that bunny butt is too cute. Mr. bluejay will soon be hidden. Great shot. Tulips don’t have a chance in your yard! You need to find a less “tasty” flower. Lol!

    Old Glory looks beautiful showing off in the breeze.

    Happy Monday Dan. Hope today is the start of a great week.
    Ginger

    Liked by 1 person

    Reply

    • I don’t blame you , Ginger. Maddie just plopped as soon as she felt how warm the step, I mean her deck was.

      I’m glad we got to see the tulip bloom for two days. Exactly two days, then munch, munch, munch.

      It’s less windy, and warmer than most mornings have been. I’ll take that as a good sign. I have to rearrange the shed (put the snowblower away – get the lawn mower out).

      I hope you have a great week.

      Like

      Reply

  5. I should never have tried to read all that! I understand only that I enjoy the recap and have a good time going down the chart. So thanks for doing all that! Thanks also for the woodpecker and fading moon — they helped undo the damage to my head. Not to mention “bunny butt.”

    Liked by 1 person

    Reply

  8. One day, Dan, I may show up again for Thursday doors. I think my post schedule is once every two years, and that’s not for awhile. So, I skipped to the photos.

    Are you over-feeding nut-seeking squirrel? He/she looks a little chubby, but perhaps like me, that’s a bit of leftover winter weight. I’m glad it’s looking like spring in your area, with sprouting and flowering vegetation. It’s good for the soul and for Maddie to hang out on the Trex, in the sun. Give all of the fur kids a few skritches from me and have a great week.

    Liked by 1 person

    Reply

    • Your schedule reflects your priorities, Mary. We can all respect that. The “chubby” squirrels might be the moms-to-be. Several are pregnant. I am enjoying the signs of spring, but now we have to start cleaning up the yard and getting ready for summer. There’s always something. Everyone with four legs is currently napping, although Maddie did go for a walk, and MuMu was scoping out the neighborhood at 5:30 from her perch in the window. Happy Monday, and have a great week.

      Liked by 1 person

      Reply

  10. ZZzz-zzzz, oh, I must have dozed off during this, Dan! 😉 Recap on Sunday. Got it. 😆 I am so thankful for all you do. Enjoy your week. Birdball coming soon??!!! Bring it on!

    Liked by 1 person

    Reply

  12. I love that moon shot. And the woodpecker. They re so crazy cool. Okay, am I understanding that if we have a pingback to your doors post there is no need to leave a link in your comments for that post?

    Liked by 1 person

    Reply

    • Because this is all in the cloud, and I can do it from any device, even if it isn’t mine. I thought about writing program to do the whole thing, but that would have restricted me to my laptop.

      Liked by 1 person

      Reply

  14. Well, your translation of that geek speak made it almost easy for me to understand!! At least it makes a little more sense to me what you’re doing and how you’re doing the recap so nicely.

    I am looking forward to Birdball. Spring is looking so lovely there. The Lilies of the Valley are really growing! I’m looking forward to seeing those too. I’d love to see a Pileated Woodpecker! I wonder if it gets a pain in the neck eating that way? 🤣

    I hope you have a good week!

    Liked by 1 person

    Reply

    • I’ve been hearing that woodpecker for weeks, but he’s been high in trees that are far away from the walk. When we turned the corner and I saw him, I was so happy. I didn’t get a great picture of him, he was on near constant motion. I’m hoping he comes back, now that he’s dug a nice picnic spot in that tree..

      It’s fun to go out in the morning and see what might be in bloom. I hope you have a great week.

      Liked by 1 person

      Reply

  15. The photos of the woodpecker are terrific, Dan. Good to see the park getting some action too. Looks like spring is going to take in your part of the country. Thanks for the discussion on Java and I didn’t get any bonus points. 😁

    Liked by 1 person

    Reply

  17. I did try to read the recap, honestly I truly did. But my eyes glazed over and my chin started to fall… I skipped to the pictures. Now I have to make a comment, your pictures seem particularly clear today. Have you switched what you’re using to take the pictures? The bunny butt was hilarious. You must have a great zoom or bunny loves you.

    Liked by 1 person

    Reply

    • Good eye, Pam. Sometimes, I remember to take my camera outside with me in the morning or when I’m out with Maddie. Usually, I just have my phone. The bunny has been letting us get closer. I guess he knows he’s safe, but that was with the zoom on my camera. I’ll give you 100 bonus points for trying.

      Like

      Reply

  20. I’m in awe of your geekness, but I’m forever lost in that world. I have fun with the artistry of creating a post, but otherwise, I depend on luck. 😊 Beautiful photos, Dan. When we lived in Missouri, the Pileated Woodpeckers were regular visitors. Though they are beautiful, I had to chase them off our shingles almost every day. Stubborn little pests! 😁

    Liked by 1 person

    Reply

    • When we had wood shingles on our house, they used to dig into one corner of our porch, every year. We replaced the siding with vinyl siding, the kind that looks like shingles. They tried pecking into that one year and then left it alone.

      The geeky stuff is fun now. I don’t do much, and I’m not under any pressure to do it, so I can enjoy it.

      Like

      Reply

  21. I found the whole thing fascinating….I admire your ability!!! You took me back a few years when I was working on some programming I was doing for my website…I have since not had the time and left it up to pre-programmed systems!! Also got to say…love the spring captures, especially since I now live somewhere where spring is real!!

    Liked by 1 person

    Reply

  23. Wow…just a wee bit overwhelmed! lol I had no idea you could do this kind of stuff with Excel. That said, I’m sending the link to this article to a friend of mine who’s far better at Excel than I will ever be. Bravo, Dan. :)

    Liked by 1 person

    Reply

  24. I used Excel a lot at work. I think I get 200 point for understanding what you said. If I go back to read four more times and do a hands-on, I may get some more points. I didn’t do all the programming stuff, one staff entered the formula in each cell of the spread sheet we used for student testing. :-)

    Liked by 1 person

    Reply

          • I scanned the students’ Scantron at the end of the school year, saved as text document, imported to Access, and used Access to join previous year and current years’ testing data by using the same student ID. After joining, deleted the previous year’s data to create a current year’s data, then print new Scantron with some completed (achieved) testing items for the current year’s teachers.

            There were so many steps and involved Access, Excel, and Text Document with a lot of importing and exporting. It took about six weeks to process 5,000 student data. I only needed to do it once a year so I wrote down all the steps because by the next year, I might forget certain steps!

            Liked by 1 person

            Reply

  25. Thanks so much for the pictures. The explanation regarding Thursday Doors that preceded it were about as understandable to me as if you had written it all in Latin. I am not Tech Savvy in the least and this reminded me how I am lacking in these skills. In fact, if it weren’t for my tech Savvy son I wouldn’t be here at all. Anyhoo, I’m impressed. I would love to participate but don’t even understand how to do THAT much less how you do what you do. :) Carry on.

    Liked by 1 person

    Reply

  27. HI Dan, that was a very interesting commentary on how you create the tables. I have never used some of those Excel functions because my spreadsheets are more driven by complex accounting that the type of functions you are using. My son uses this sort of functionality so I will send this post to him to read. He many learn something new. I’ll go back and look at the pictures now, I got distracted by the learning [smile].

    Liked by 1 person

    Reply

Add your thoughts or join the discussion. One relevant link is OK, more require moderation. Markdown is supported.

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

Gravatar
WordPress.com Logo

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

Facebook photo

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

Cancel

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.