The tool here shows how you can get a clean table or list from a crosstab in Excel for more flexible reporting. Download the .xls file here: CrossTab-to-Flatfile-General .
(If you’re nervous about what that will do, you can instead inspect and load the code module file at the bottom of this post. )
Go to Sheet1. This is an example from Angela Wolff, whose work I’ve developed here. Her NHSExcel site explains the example and the objectives. You can also read my earlier post below.
Push the button “Convert to Flat File”. This will run a macro that builds a new worksheet just after the one you’re looking at, named “Sheet1_List“. (It will destroy any earlier worksheet of that name.)
You’ll be asked to identify parts of the worksheet. You can select the ranges with your mouse, or type them directly.
Using the sample worksheet Sheet1, these are your selections for the various prompts:
If you want insight into what’s going on, or like direct assurance that nothing is dangerous, I’ve included a version of the source code file here. (You can of course just go straight to the unprotected spreadsheet file and hit Ctrl-F11 to see this.) The free WordPress.com account I’m using restricts the file types you can load. That’s why internally I’ve attached the extra extension ‘.doc’ on the end. This will be evident when you download or ‘save the link’: Module1.bas . Rename it to Module1.bas before loading – or just open it, copy the text and paste into that module.
I’ve had the tool installed as an add-in, but this may be a bit of overkill for the amount of use it gets.