Interactively get a flat file list from a crosstab or Pivot Table (Excel)

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:

CrosstabToListUI-ColumnLabelBlock

The user selected the area in the dotted rectangle. The equivalent range is autofilled in the input box.

CrosstabToListUI-2-RowHeadingsBlock

‘Row headings’ are just one row deep. They provide a name for the domain of row labels beneath them.

CrosstabToListUI-3-CrosstabInitialRowSpecimen

Any single cell in the *top* crosstabulated data row is sufficient. (A crosstabulated value has both a row and a column label.)

CrosstabToListUI-4-Heading-ColumnLabelRow1

The column labels usually don’t get their own headings. You supply a word to cover the label values in the selected row.

CrosstabToListUI-5-Heading-ColumnLabelRow2

A second column label level in this case. Sometimes a single English noun is hard to find!

CrosstabToListUI-6-Heading-CrosstabValues

Finally, a name for the ‘dependent variable’ – the crosstabulated data. Usually a unit of some kind – e.g., hours, dollars, count.

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.

Advertisements

4 thoughts on “Interactively get a flat file list from a crosstab or Pivot Table (Excel)

    • Thanks for your comment, Smit. So long ago, sorry! I missed it come in and just saw it now when alerted to another.
      I hope the tool is helpful.

  1. Hi David, great work here for transforming a complex crosstab into an usable flat list, thanks.
    For picky developers scared of opening third party macro workbooks, I suggest you publishing a txt file with the entire code in this very same post. Thanks again.

    • Thanks, Lino. I’ve done that now. It’s included in the original post. Unfortunately, I can’t exactly load a .bas or a .txt with WordPress.com, but you have what you need: a clear text version, demonstrably free of unwanted side effects!

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s