+ Reply to Thread
Results 1 to 6 of 6

Excel 2007 : Splitting Checkbox/Choice Data in Pivot Table

  1. #1
    Registered User
    Join Date
    12-17-2010
    Location
    Charleston, SC
    MS-Off Ver
    Excel 2003
    Posts
    5

    Splitting Checkbox/Choice Data in Pivot Table

    I have exported data from SharePoint to Excel 07. Some of the data comes from a checkbox field, and thus has multiple entries. Once exported, these are broken out with semicolons and hash signs. Is there any way to break out this data into my PivotTable so that I can count separately whenever each entry is selected? To give a solid example, in the attached image, how would I count "PRS Training/PRS Demo" multiple times? Thanks!
    Attached Images Attached Images
    Last edited by watsonlg; 01-05-2011 at 11:25 PM. Reason: solved

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Splitting Checkbox/Choice Data in Pivot Table

    Hello watsonlg,

    I'm afraid nothing much can be gleaned from your stamp sized screenshot. If you want help with an Excel problem, please post an Excel file, not an image (and an unreadable image at that).

    We're here to help, but you need to provide us with enough information to let us help you.

  3. #3
    Registered User
    Join Date
    12-17-2010
    Location
    Charleston, SC
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Splitting Checkbox/Choice Data in Pivot Table

    Uploading Excel document to help with issue. Thanks!
    Attached Files Attached Files

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Splitting Checkbox/Choice Data in Pivot Table

    Hello,

    the problem here is that Excel is not a relational database and can only display two-dimensional data sets.

    I've come across this issue with SharePoint survey results before and what I normally do is to insert a few columns (as many as required to capture the maximum number of answers in any one row) and then do a text-to-columns on that row, delimited by the ; sign.

    This will create a certain amount of overhead, but since a two dimensional table can not be normalised completely, this is just what you need to work with, unless you want to transfer the data into a true relational database system, like Access.

  5. #5
    Registered User
    Join Date
    12-17-2010
    Location
    Charleston, SC
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Splitting Checkbox/Choice Data in Pivot Table

    Thanks for the help. I actually figured out that instead of doing a text to columns, it makes more sense to do an ISNUMBER/SEARCH string to be able to pull in the data for each criteria.=IF(ISNUMBER(SEARCH("Increase usage",$V5)), "Increase usage", "")

  6. #6
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Splitting Checkbox/Choice Data in Pivot Table

    Hello,

    when I do SP list analysis over time, with data refreshes in between, I always keep the raw data in one sheet and then use formulas on another sheet to do the splicing, similar to what you've posted above. For a one-off, text to columns works better for me.

    cheers,

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1