# Potential ways to format binary data with a pivot table.

1. ## 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 :-)

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

Please check the attachment whether this helps

3. ## 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?

4. ## 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. ## 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

6. ## 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. ## 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.

8. ## 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. ## 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.

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

#### 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