Page 1 of 11

Guide to Solving the WSJ Meta with Excel/Spreadsheets

Posted: Tue Jul 23, 2019 3:01 am
by Joe Ross
Below is presented a digital way to substitute for printing multiple copies on paper, an excellent way to attack metas, and eloquently presented here. Considering the wealth of knowledge & the incredible experience of these presenting authors, that thread should be the start of any new muggles' path to learning.
--------------------------
Post #11, below, gives a better way to create the basic spreadsheet. You may wish to start there!
--------------------------
I've studied drafting, have done a bit of it, and would rather print in block letters than write in cursive. For day-to-day printing, I much prefer gel ink over pencil.

WSJ Puzzle's online app for completing crosswords appeals to my need for speed (I solve puzzles much, much faster using it) and to my neat-freakishness, in that corrections are quickly & cleanly made, with no pencil (shudder) erasures on newsprint. I do subscribe to the online & paper WSJ.

I also use Excel daily and am comfortable with it.

Given the above, I use the online app & Excel to solve the puzzles & to work out metas. I offer it for consideration.

Routine:
- Open new spreadsheet page.
- Format all Row Heights to 17.3 (46 pixels), Columns A-E Widths to 15.3 (236 pixels), and Columns F+ Widths to 25.0.
- SAVE this spreadsheet as a blank for future puzzles.

- Open the Friday contest puzzle in PDF form.
- Copy the grid & clues of the PDF to the upper left corner of an Excel page using the Snippet Tool.
- Downsize PDF Snippet by dragging SE corner to make grid rows match Rows 1 through 15.

- Solve the grid using the WSJ online app.
- Copy the solved grid to the upper left corner of the puzzle PDF and downsize Snippet to match PDF grid.

- Highlight theme answers:
--- (in Excel) INSERT - ILLUSTRATIONS - SHAPES - RECTANGLES
--- Click & drag rectangle over a theme answer.
--- Format Shape: Fill @ 50% Transparency & select color. No Line for shape border.
--- Copy & paste rectangle to other themed and useful answers. Change colors as desired.

- Use Columns F & beyond as scratch pad for rabbit holes, brainstorming, etc. Highlight & format cells as desired.

These seem like a lot of steps, but the beauty of Excel is the ability to copy & paste, once the basic spreadsheet, PDF, grid, & highlighting shapes are established, even from week to week. Also, erasures are clean & simple with a couple of right & left mouse or mouse-pad clicks.

Paper Chase was a relatively simple solve, so the spreadsheet, below, is much too clean. Other weeks can look as if a clown vomited confetti all over them.

20190718 Paper Chase.gif

Re: "The Paper Chase" - July 19, 2019

Posted: Tue Jul 23, 2019 10:21 am
by Joe Ross
I should have mentioned that I also use the shapes to highlight grid clues, when I think that they may be part of the solution. If you don't copy & paste the PDF with clues, you cannot highlight the clues.

I wouldn't attempt to highlight paper as it cannot be erased nor altered.

Too, on the rabbit holey/confetti-ed puzzles, I may use lines, arcs, arrows, ovals, & circles to connect words, clues, & scratch pad lists. These are also easy to copy/paste/alter/erase.

Another way to highlight grid answers is to use straight lines, making them square-ended & increasing their widths to your desire. You may find this easier, however rectangles are a better tool for highlighting clues with multiple lines.

To investigate multiple lines of thought more cleanly, each Excel sheet can be copied & pasted to multiple tabs within the same file. In the example posted, there is only one sheet/tab (at the bottom), named "Sheet1".

Re: "The Paper Chase" - July 19, 2019

Posted: Tue Jul 23, 2019 10:32 am
by Joe Ross
The formatting of rows & columns mentioned fit my Surface with a 12" screen. Your display/pad/laptop may vary by a large degree.

There is no need to have the Snippeted PDF span Columns A through E, as I do. Column A could be widened to the width of the PDF Snippet, with the "scratch pad" columns starting with Column B.

If anyone has questions or wants help. please send a PM. I'll do my best.

Re: Joe Ross' Guide to Solving the WSJ Meta with Excel

Posted: Wed Jul 24, 2019 9:23 am
by Inca
Joe Ross wrote: Tue Jul 23, 2019 3:01 am

Given the above, I use the online app & Excel to solve the puzzles & to work out metas. I offer it for consideration.


--- Copy & paste rectangle to other themed and useful answers. Change colors as desired.

-
I tried this out to see if I could get it and it worked beautifully and quite quickly. (I'm still attached to my own Excel Meta Template, but it does take more time to set up.)

The only trouble I had was copying and pasting the highlighting to where I needed it since I am not that familiar with inserting illustrations/shapes. But, I did manage to figure out a way to do it in the end. Probably not the right way because it isn't as smooth as I know it should be. I actually added a highlighted rectangle off to the side of the puzzle so that it is ready for me to copy/paste as needed. If you could try to be more specific as to the right way to do that, I'd appreciate it (I know it is hard to describe in words instead of just showing it in person).

Re: Joe Ross' Guide to Solving the WSJ Meta with Excel

Posted: Wed Jul 24, 2019 5:39 pm
by Joe Ross
Inca,

You have the right idea about copying & pasting shapes, lines, rectangles, etc. You can open a previous puzzle's spreadsheet & copy as many different shapes from it to a new puzzle's spreadsheet. You'll not have to create new shapes, just copy, paste, move, & adjust them, then change their colors.

This is one of the time-saving benefits of Excel which gets lost in these necessarily long & detailed written directions.

I suggest using Lines instead of Rectangles. Here's how to create & manipulate both:

LINES

In Excel, Insert - Illustrations - Shapes - Lines

Click and Drag from middle of one side of grid word to other (left/right for Across, top/bottom for Down).

Right-Click on line to Format Shape:
- Change Color
- Adjust Transparency (50%)
- Adjust Width (16 pt on my machine).

Right-Click on line to Copy.

Right-Click anywhere to Paste.

To move/reposition a line, click on the body of line & drag line so that one end is where you'd like it to be, then click & drag on the circle on the other end of the line to adjust its length & direction.

See the sides of "CHICAGO", below, for the circles which will allow you to adjust a line's length & direction.

Highlight Line Chicago.gif


RECTANGLES

In Excel, Insert - Illustrations - Shapes - Rectangles

Click and Drag from any corner of grid word, clue, or other area to be highlighted to the opposite corner.

Right-Click on rectangle to Format Shape:
- Change Fill - Color
- Adjust Fill - Transparency (50%)
- Line - No line.

Right-Click on rectangle to Copy.

Right-Click anywhere to Paste.

To move/reposition a rectangle, click on the body of rectangle & drag so that one corner is where you'd like it to be, then click & drag on the circle on the opposite corner of the rectangle to its desired place.

You can also adjust the height & width only of a rectangle by clicking & dragging on a circle in the middle of any of its 4 sides.

See the borders of "CHICAGOPD" & clue 39 Across, below, for the circles which will allow you to adjust a rectangle's dimensions.

Highlight Rectangle Chicago.gif

Highlight Rectangle 39 Across.gif

Re: Joe Ross' Guide to Solving the WSJ Meta with Excel

Posted: Wed Jul 24, 2019 5:51 pm
by Joe Ross
Again, big thanks to our administrator, BrianMac, for setting up this muggle online community, for creating this topic, & copying its first three posts from The Paper Chase.

Re: Joe Ross' Guide to Solving the WSJ Meta with Excel

Posted: Thu Jul 25, 2019 6:49 pm
by Joe Ross
FWIW, I found solving this week's Poet's Corner puzzle via this method extremely useful, but I have to admit to being a newbie and to getting better all around with every puzzle.

I will post my Poet's Corner spreadsheet Monday morning, but I hope the following times help fellow muggles understand that this isn't as complicated as it may seem:

<3 minutes Open PDF, Snippet, Open preformatted Excel puzzle sheet, Paste to Excel
+/-20 minutes Solve grid
<2 minutes Snippet grid answers, Paste to Excel
<4 minutes Highlight theme clues & answers
+/-90 minutes Highlight more & add thoughts to scratch pad, Solve meta

Re: Joe Ross' Guide to Solving the WSJ Meta with Excel

Posted: Thu Jul 25, 2019 7:30 pm
by Inca
I'm not feeling well but I had to pop in and let you know that I used your method this week.
I really had almost the entire solution without writing or Excelling and normally I probably would have finished it that way, but being that I have fever, it was easier to see it visually with the highlighting and notating on the side. Much easier. I really recommend this method: Simple to use and you can play around (highlight/ erase the highlight etc) and the grid stays neat as ever.
It worked but my widths/heights have to be different than yours and so mine is a little wonky since I'm not so good at designing this. (I was going to insert a screen shot here but in the last minute I realized, "Ooops, the solution is right there!")
And, I still am having issues with the copy/paste of the lines. I don't get the Copy and/or Format option sometimes when I right click. I usually get Move Here, Copy Here, or Cancel. (Maybe I have a different version of Excel? or maybe I'm not selecting the shape right even though it looks like it is selcted). Anyway, I resolved the issue for myself by just selecting the line already on the grid and then copying it to where I want by using CTL+drag.
BTW, if you use lines, do you have to have 2 separate ones to copy, if you need both vertical and horizontal lines?

Re: Joe Ross' Guide to Solving the WSJ Meta with Excel

Posted: Fri Jul 26, 2019 6:33 am
by Joe Ross
Inca wrote: Thu Jul 25, 2019 7:30 pm I'm not feeling well but I had to pop in and let you know that I used your method this week.

I really had almost the entire solution without writing or Excelling and normally I probably would have finished it that way, but being that I have fever...
First and foremost, Inca, I hope you are well, soon!

This phone is not the right tool for best answering your formatting questions. I will return with the Excel version and screen shots of its right-click drop-down menus, &c.

Re: Joe Ross' Guide to Solving the WSJ Meta with Excel

Posted: Fri Jul 26, 2019 8:45 am
by KayW
Joe - thank you so much for taking the time to write this method up!! Since this week's meta was pretty easy, I am taking the time to re-work it using your method. Just so I'll have the template and mechanism in place for that week the inevitable KAS 4/5 rears its head.

I always wait for the Friday paper print edition - I'm a dinosaur who likes solving puzzles with paper and ink - but I think this will be extremely useful on those weeks I spend digging dead-end rabbit holes!

Re: Joe Ross' Guide to Solving the WSJ Meta with Excel

Posted: Fri Jul 26, 2019 8:59 am
by Joe Ross
Version of Excel on Joe's MS SurfacePro2018: File - Account
About Excel 2.gif
Inca wrote: Thu Jul 25, 2019 7:30 pm It worked but my widths/heights have to be different than yours and so mine is a little wonky...

I need to reboot on the initial Excel sheet set-up. What drives the initial setup is having the Puzzle PDF fit within your Excel window.

- Snippet the current week's Puzzle PDF & Paste it to the upper left corner of a blank Excel Spreadsheet.

- Click & Drag the lower right (SE) corner of the Snippet so that the Puzzle PDF fits within the height of the Excel window on your screen, with an extra row of space below it to allow for slightly other-sized future Puzzle PDF Snippets. The heights & widths of the clue sections may vary week-to-week.


Adjust the spreadsheet's row heights to match the heights of the puzzle grid:

- Select the entire spreadsheet. Click on the block with a green triangle above Row 1 numeral & to the left of Column A header:
Select All 2.gif

- Right-click on any Row numeral, then on Row Height... in the drop-down menu.
joe2.gif

- Enter 17.30 (this height works on my Surface) as the Row Height & check that Rows 1 through 15 match the row heights of the grid of the Puzzle PDF. Repeatedly adjust the Row Heights until they do.


Change the Column Width of Column A to match the width of the Puzzle PDF.

- Click on the Column A Header. RIght-Click on Column Width.... Enter 78.00. Adjust as necessary for your screen.


Adjust the Column Widths of Columns B & beyond to whatever works for you. You'll find yourself adjusting these as you solve each week's meta. Save the spreadsheet for the current week's puzzle.


Before going forward with the current week, delete the current week's Puzzle PDF from the spreadsheet, then
SAVE A FORMATTED, BLANK SPREADSHEET AS A BLANK FOR FUTURE PUZZLES:

- File - Save As & Enter a name such as "WSJ Excel BLANK"

Re: Joe Ross' Guide to Solving the WSJ Meta with Excel

Posted: Fri Jul 26, 2019 9:30 am
by Joe Ross
Inca wrote: Thu Jul 25, 2019 7:30 pm I still am having issues with the copy/paste of the lines. I don't get the Copy and/or Format option sometimes when I right click. I usually get Move Here, Copy Here, or Cancel.
When I right-click a line I get:

Right-Click Line.gif

Inca wrote: Thu Jul 25, 2019 7:30 pm Maybe I'm not selecting the shape right even though it looks like it is selected. Anyway, I resolved the issue for myself by just selecting the line already on the grid and then copying it to where I want by using CTL+drag.
When selected, a line should have circles at either end

Highlight Line Chicago.gif

and a Rectangle should have circles at each corner and at the middle of all 4 sides

Highlight Rectangle 39 Across.gif


If you mean creating a new shape:

Insert - Illustrations - Shapes -

Insert - Illustrations - Shapes.gif

Inca wrote: Thu Jul 25, 2019 7:30 pm BTW, if you use lines, do you have to have 2 separate ones to copy, if you need both vertical and horizontal lines?
Lines are controlled by the two circles at either end when selected. Click & Drag on either circle to rotate a line from horizontal to vertical.

Although we use lines as highlighting in only the horizontal & vertical, they can go in any direction. Rectangle sides are only vertical & horizontal. You may find rectangles easier to use & alter.

To answer your question, I created the following bank of horizontal & vertical lines and saved them to my formatted puzzle spreadsheet before starting this week's puzzle. I then copied & pasted them to winnow out the meta answer.

Selections of Formated Lines.gif

Re: Joe Ross' Guide to Solving the WSJ Meta with Excel

Posted: Sat Jul 27, 2019 11:06 am
by Joe Ross
Post #11's images were resubmitted. For the initial images, I had used screenshots of the current Poet's Corner puzzle. Please accept my apologies.

Thank you to BrianMac for his help!

Re: Joe Ross' Guide to Solving the WSJ Meta with Excel

Posted: Sun Jul 28, 2019 12:14 am
by Inca
You are so kind, Joe, to take all the time to help out.

After the solution posts, I will try to post snips of my puzzle showing the 2 shortcut menus I get: one the right one, and one different one. And maybe when you see it you will be able to tell me why I don't always get the same one. I will wait until after solution is up, because the snip would show part of the puzzle which would be a no-no.

Oh, and thanks for letting me know that I can move the line around by using one of the circles. I knew there must be a way and it is so straightforward that I don't know why I hadn't tried it.

Re: Joe Ross' Guide to Solving the WSJ Meta with Excel

Posted: Sun Jul 28, 2019 12:48 pm
by Joe Ross
POSTING PARTIAL SCREEN GRABS & WHOLE SPREADSHEET IMAGES

The following are for a Microsoft SurfacePro laptop/tablet, with a 12" screen. Any translations to Apple products, other PCs, or screen sizes are welcome.


There are 2 screen-grabbing tools to use:

1) The Snipping Tool app
Snipping Tool app.gif

The Snipping Tool allows you to lasso or select a rectangle of any part of your screen. It can then be Saved as-is, or pasted to an image editing app/program/feature.


2) The PrtScn (Print Screen) key

Pressing the PrtScn key copies everything on your screen (or multiples screens, if more than one) to your PC's Clipboard. It can then be pasted to an image editing app.


Use the older Paint app to edit screen grab images.
Paint app.gif

Paste the Snippet or entire screen grab to Paint.

For entire screen grabs, choose the area you wish to save.
Home - Select
Select tool.png
Click & Drag a rectangle from one corner of the desired area to its opposite corner.


Once you have the area selected,
Home - Crop
Crop.gif


Resize the image to fit onto the Muggle forum:
Home - Resize
Resize.gif

In the Horizontal box, change 100 to 50. The Vertical percentage will follow.
Resize 50pct.gif


Save the image as a GIF, which will reduce the size of the file & allow the file to be uploaded. The maximum size of an uploaded image is 250 KB approximately.

- File - Save As - GIF picture
File - Save As - GIF picture.gif

If you see a pop-up message about the color quality being reduced, click "Okay". This is the point of saving as a GIF file.


See BrianMac's excellent How to Post A Picture for final instructions.

Re: Joe Ross' Guide to Solving the WSJ Meta with Excel

Posted: Mon Jul 29, 2019 1:36 am
by Inca
Here are the 2 shortcut menus I get when I right click on a selected line. I cannot figure out why sometimes I get one and sometimes the other.
Right-click short menu.gif
BTW, when I then right click where I want to paste the line, I also sometimes get that 1st shortcut menu and if I select Copy Here, it gives me a 2nd copy of the entire filled in grid and even if I get the correct shortcut menu when I click on paste, it does paste the line but not where I clicked but somewhere else on the spreadsheet, usually the upper right corner of the grid.

Like I said, I am baffled about this and can't make sense of it but since I have discovered that dragging with the Ctrl key down gets me a copy where I put it, I'm OK with that. I was just trying to understand what is going on with trying to do it the regular copy/paste way.

I don't know why the image is so small and blurry; I hope it is clear enough for you to see that the line is actually selected in both and what the shortcut menus are.

Re: Joe Ross' Guide to Solving the WSJ Meta with Excel

Posted: Mon Jul 29, 2019 7:34 am
by Joe Ross
Poets' Corner was fairly simple, similar to last week's Paper Chase, so this spreadsheet is fairly simple also. See the extra "scratch pad" space/columns to the right of the screenshot, below. There is plenty of workspace for differing ideas, rabbit holes to explore, etc.

Stubbed a toe in the top of a rabbit hole by thinking that the answer may have been MACED (The only author in a "true" grid corner was MILLAY and the only 5-letter word in that southwest corner of the grid was 47 Across: MACED. Yeah, I know, I know...).

However, after floating away on a raft for awhile, I remembered how TENNY was a clunky answer, mind clicked on TENNYSON, saw its upturned 'corner', then - AHA! - quickly found the other Poets' Corners. I deleted the highlighting on MACED, added highlighting for the other 4 authors, & eventually deduced the 5 'cornered' answer letters in clue order.

20190725 Poets Corner 50pct.gif

Re: Joe Ross' Guide to Solving the WSJ Meta with Excel

Posted: Mon Jul 29, 2019 7:49 am
by Joe Ross
Inca wrote: Mon Jul 29, 2019 1:36 am BTW, when I then right click where I want to paste the line, I also sometimes get that 1st shortcut menu...

I don't know why the image is so small and blurry; I hope it is clear enough for you to see that the line is actually selected in both and what the shortcut menus are.
Inca,

I get that short pop-up menu, too, when I give the highlighting shape a sustained (more than a second) right-click. A quick right-click gives me the longer menu.

Hard Right Click on Highlight.gif

Your images are clear enough. You seem to be saving & posting more than the menus as an image, adding a lot of white space. See Post #15
POSTING PARTIAL SCREEN GRABS & WHOLE SPREADSHEET IMAGES

Re: Joe Ross' Guide to Solving the WSJ Meta with Excel

Posted: Fri Aug 02, 2019 3:32 am
by Joe Ross
Thanks to Inca for her suggestion that filling out the grid twice can help to start ideas for the meta answer.

Filling out the grid a second time using my phone. Initial thoughts recurred and blossomed, leading to the answer.

Re: Joe Ross' Guide to Solving the WSJ Meta with Excel

Posted: Mon Aug 05, 2019 11:32 am
by Joe Ross
CHAIN GANG in an 8 by 10 color glossy picture with circles and arrows and a paragraph on the back:

20190802 Chain Gang.gif

I didn't mine many rabbit holes this week, but have permission to highlight & document a fantastic rabbit-holey effort, here, by fellow muggle Emmaa. Look for it in a day, or two.