+ Reply to Thread
Results 1 to 9 of 9

Potential ways to format binary data with a pivot table.

  1. #1
    Registered User
    Join Date
    04-16-2013
    Location
    Stuttgart, Germany
    MS-Off Ver
    Excel 2003
    Posts
    3

    Potential ways to format binary data with a pivot table.

    Hi,

    I am trying to create a summary of a large amount of data. The data shows a list of firms and in which countries they have business in. In my excel table a company is given a 1 if they have business in a country and a 0 if they don't.

    What I need to find out is the number of firms with business in numerous countries but I need to know the number for all possible combinations: e.g. in country 1 and 3 only, in country 1, 3 and 6 only etc.

    The pivot table I have created does show this but in a very complicated way. You have to read across taking 1 to mean it is present in the country and 0 to mean it isnt. Once you reach the end of the row you know which of the 8 countries it is in and then at the end a total of the number of firms which meet this criteria is given.

    Although I can follow this table, it needs to be formated in a way that someone could understand it without me having to explain. Additionally, in the future I could be adding up to 21 countries and this would make the current format very confusing.

    Any advice or suggestions you can give me would be hugely appreciated

    Thanks :-)
    Attached Files Attached Files

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,690

    Re: Potential ways to format binary data with a pivot table.

    Please check the attachment whether this helps
    Attached Files Attached Files


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,891

    Re: Potential ways to format binary data with a pivot table.

    @ Sixthsense
    I think it's a lot more complicated than that. How can you tell the number of firms with data for country 1 AND 3 only?
    Cheers
    Andy
    www.andypope.info

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,690

    Re: Potential ways to format binary data with a pivot table.

    @ Andy Pope,

    I just made the pivot to show the data in simple way not sure what is the expected output of the OP.

    Tabelle4 - Sheet
    A236:A243
    J236:J243

    Pivot - Sheet
    B4:I4
    B5:I5

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,891

    Re: Potential ways to format binary data with a pivot table.

    This builds a 0/1 string of countries.

    In order to make the result more readable I have included a UDF to report country index
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-16-2013
    Location
    Stuttgart, Germany
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Potential ways to format binary data with a pivot table.

    Thanks Andy!

    Already a large improvement on the complicated table I created!

    Once all 21 countries however are recorded, manually writng which countries are included to the left of the pivot table would be time consuming.

    Prehaps there is another way to summarise the data easily so that nothing needs to be entered manually?

    If not then thanks so much for your help so far :-)

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,891

    Re: Potential ways to format binary data with a pivot table.

    You can do that with some code.

    Please Login or Register  to view this content.
    I have extend the UDF to take a list of real country names.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    04-16-2013
    Location
    Stuttgart, Germany
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Potential ways to format binary data with a pivot table.

    Hi Andy,

    Thanks for this. Sorry to be a pain but I am not overly advanced in Excel. Would you be able to help me set up the UDF. I went on Visual Basic Editor and copied and pasted your code, however I'm not sure how I list it to a list of country names or where I need to write this list.

    Thanks for any advice you can give.

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,891

    Re: Potential ways to format binary data with a pivot table.

    The UDF code needs to be pasted in to a standard code module within the project.

    The list of country names is a named range. In the example file the range Tabelle1!N2:N9 contains the text and has been named COUNTRYNAMES.

    You can see this by using Name Manager dialog on the Formulas tab.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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