Counting unique items from different columns using pivot table

[F16Stevie]

Hi,

Using Pivot Tables, how do I count unique items from different columns?
For instance:

FRUIT 1 FRUIT 2
apple pear
pear pear
apple pear
banana peach
peach pear

DESIRED RESULT:
apple 2
banana 1
pear 5
peach 2


I have tried Pivot Tables
Row: FRUIT 1
Sum Values: FRUIT 1, FRUIT 2

However, this results in 2 columns of Fruit 1 counts.

[MarvinP]

Hi F16Stevie and welcome to the forum,

Pivot Tables work with Tables of data. When you have your fruits in two different columns then it isn't a valid table in the strict sense of what tables are. You will need to put all the like things in a single column to make it a valid "table" and then Pivots will work great.

See http://databases.about.com/od/specif...normalform.htm for some hints.

The idea is that each column of data should have unique types of information in it. If you had Col A = First Name and Col B = First Name also you have a bad data structure and can't filter a single column of for a first name. You have this type of problem by having Fruit in two different columns.

Read about Normal Form to get more with the idea of Tables of Data.

[F16Stevie]

Thanks Marvin,

I understand what you are saying, but how can I make 1 column with 2 pieces of data in it, let's say a filed that contains apple + pear, without losing the ability to count the individual items?

Stevie

[MarvinP]

Hi,

You change your data, using cut and paste from:
FRUIT 1 FRUIT 2
apple pear
pear pear
apple pear
banana peach
peach pear

To:
Fruit
apple
pear
apple
banana
peach
pear
pear
pear
peach
pear

Then do your pivot or simple table stuff with it.

[F16Stevie]

Yep, that would be the simple way, but how about the other table elements in a particular row, for instance Child First Name, Last Name, Class etc?

Stevie

[MarvinP]

Do you have a better example of what you need? Two columns of fruit don't fit with First Name, Last Name, Class, etc....

I've been working for about 2 years on a large Access genealogy database where people put first or last names in switched columns. I'm a little sensitive to having a single column with the same stuff in it. If the column head is Date of Birth, I don't want to see "died young". I really want a frickin DATE to be in the column. If a number is required in the field I don't want to see the letter "Oh" replacing all those zeros.

Your problem is close to this. You want to put fruit into two separate columns. If they are the same thing, they should be in the same column.

Give me a better example and maybe I can understand your problem.... Attach a workbook with some sample data. Click on "Go Advanced" below the message area and then on the Paper Clip Icon to attach a file.

[F16Stevie]

Example Incidents.xlsx

Hi,

Ref. attached. The issue is that I am coding aviation accidents/incidents using not more than 2 codes, which could be randomly assigned (Causal Factor 1, Causal Factor 2). Example of a code would be "Human-Error-aircrew" or "Environment", for example.

I have a list of about 20 different codes I use, but they can be assigned to either column.

With a rather large spreadsheet, I would like to count how many times each code is used in the 2 columns, i.e. I am not interested in the unique combination of 2 codes.

Thanks for your time.

[MarvinP]

OK - I have an answer..

First you need to create a list of unique Incidents (fruits) somewhere on your sheet. I copied the columns F and G over to L. I actually copied column F to L1 and then copied column G UNDER the last entry in L. Then, while I had Column L selected, I clicked on the Data Tab and on the Icon named "Remove Duplicates". This reduced the list to unique incident names. Then In Col M I put the CountIf formula to count the number of incidents in both columns that matched the word in Col L.

I hope that is what you were looking for. It makes a lot more sense now. It still can't be done using a Pivot Table unless all incidents are in the same column. I hope this new, non-pivot answer works for you.

[F16Stevie]

Thanks! Yes, that does solve my main issue. The reason why I wanted to use Pivot Table was to then possibly also do some more association analysis, which Pivot Table is so useful for, for instance KLM has relatively more Human-Error-aircrew incidents than Lufthansa.

However, thanks for the help as my main problem has been solved.