A question regarding Pivot Table

[georgeth]

Hi,

My table looks like this:

<table dir="RTL" width="864" border="0" cellpadding="0" cellspacing="0"><col style="width: 54pt;" width="72" span="12"> <tbody><tr style="height: 14.25pt;" height="19"> <td class="xl66" dir="LTR" style="height: 14.25pt; width: 54pt;" width="72" align="right" height="19">A</td> <td class="xl66" dir="LTR" style="width: 54pt;" width="72" align="right">B</td> <td class="xl66" dir="LTR" style="width: 54pt;" width="72" align="right">C</td> <td class="xl66" dir="LTR" style="width: 54pt;" width="72" align="right">D</td> <td class="xl66" dir="LTR" style="width: 54pt;" width="72" align="right">E</td> <td class="xl66" dir="LTR" style="width: 54pt;" width="72" align="right">F</td> <td class="xl66" dir="LTR" style="width: 54pt;" width="72" align="right">G</td> <td class="xl66" dir="LTR" style="width: 54pt;" width="72" align="right">H</td> <td class="xl66" dir="LTR" style="width: 54pt;" width="72" align="right">I</td> <td class="xl66" dir="LTR" style="width: 54pt;" width="72" align="right">J</td> <td class="xl66" dir="LTR" style="width: 54pt;" width="72" align="right">K</td> <td class="xl66" dir="LTR" style="width: 54pt;" width="72" align="right"> L</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" dir="LTR" style="height: 14.25pt; width: 54pt;" width="72" align="right" height="19">1</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">2</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">1</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">2</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">5</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" dir="LTR" style="height: 14.25pt; width: 54pt;" width="72" align="right" height="19">2</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">1</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">2</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">1</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">2</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">5</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" dir="LTR" style="height: 14.25pt; width: 54pt;" width="72" align="right" height="19">1</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">2</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">1</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">1</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">2</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" dir="LTR" style="height: 14.25pt; width: 54pt;" width="72" align="right" height="19">1</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">2</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">1</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">1</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">2</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" dir="LTR" style="height: 14.25pt; width: 54pt;" width="72" align="right" height="19">2</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">2</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">1</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">1</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">1</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">2</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">3</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" dir="LTR" style="height: 14.25pt; width: 54pt;" width="72" align="right" height="19">3</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">1</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">2</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">1</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">2</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">7</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" dir="LTR" style="height: 14.25pt; width: 54pt;" width="72" align="right" height="19">1</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">1</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">2</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">7</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" dir="LTR" style="height: 14.25pt; width: 54pt;" width="72" align="right" height="19">1</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">2</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">1</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">2</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">3</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" dir="LTR" style="height: 14.25pt; width: 54pt;" width="72" align="right" height="19">3</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">2</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">1</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">1</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">0</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">2</td> <td class="xl65" dir="LTR" style="width: 54pt;" width="72" align="right">3</td> </tr> </tbody></table>
I want to count how many times each number (every column has its own numbers) appears in each one of the columns. For example:
In column A: 1 appears 5 times, 2 appears 2 times, 3 appears 2 times.
In column L : 0 appears 2 times, 5 appears 2 times, 3 appears 3 times and 7 appears 2 times

What can I do to? Can I organize all this data in 1 pivot table? What is the best and most efficient way?

Thanks a lot!
George

 

[georgeth]

Can anyone please help me?

 

[snoopyhr]

Say in Sheet2 setup a table with unique number starting from A1 and down, and cross have you column headings A to K

then in A2 type this formula:

=COUNTIF(sheet1!A$2:A$10,sheet2$A2)

And copy down and cross, you should get the count of all numbers per column.

 

[georgeth]

Thank you very much for your help.

However, I don't understand how exactly the table on sheet 2 should look like. Can you please upload an Excel file with both sheet1 and sheet2 (include the formula) so I can look at it? It will be very much appreciated.

George

 

[snoopyhr]

This will be your data on sheet 1, first tab


A B C D E F G H I J K L
1 0 2 0 0 0 0 1 0 0 2 5
2 1 2 0 0 0 0 1 0 0 2 5
1 0 2 1 0 0 0 1 0 0 2 0
1 0 2 1 0 0 0 1 0 0 2 0
2 0 2 1 0 0 1 1 0 0 2 3
3 1 2 0 0 0 0 1 0 0 2 7
1 1 0 0 0 0 0 0 0 0 2 7
1 0 2 0 0 0 0 1 0 0 2 3
3 0 2 1 0 0 0 1 0 0 2 3

Second sheet, called Sheet2 constructed as:

numbers from 0 to 7 in column A, and
column titles in row B1 to M1

A B C D E F G H I J K L M <----- This is ex cel defualt columns headings
1 A B C D E F G H I J K L <----- this is your headings
2 0
3 1
4 2
5 3
6 4
7 5
8 6
9 7
The first column refers to excel row numbers, the second column is your numbers 0 to 7
Place the formuloa in B2 copy cross and down to fill the table.
the partial result if you format to show "0" as blank will be like:

For Data set A , there are 5 1's 2 2's and 2 3's etc.
A B C D E F G
0 6 1 5 9 9 8
1 5 3 4 1
2 2 8
3 2
4
5
6
7

when posted the columns shifted to the left, sorry my excel jeane is not working to post the spreadsheet properly.

 

[georgeth]

Thanks a lot!!