Count Occurrences In A Column, Then Display In A Table

[Guest]

I need to figure out how to count occurrences (all lined up in two columns),
that will then be displayed in a table. The occurrences are a series of
rankings (1 - 7) spaced over 20 intervals.

The column looks like this.
COLUMN 1 COLUMN 2
1 Sally
2 Sue
3 Sam
4 Suzy
5 Sherri
6 Sandra
7 Serena

The above is repeated twenty times. The COLUMN 1 will have "1" in the very
next row (say A9), with a different order for the seven girls.

I created a table that looks like this

NAME 1 2 3 4 5 6
7
SALLY
SUE
SAM
SUZY
SHERRI
SANDRA
SERENA

I need a formula that will populate how many times each girl was selected
first, second, third.... all the way to seventh.

SALLY might look like this.

NAME 1 2 3 4 5 6 7
SALLY 5 3 2 5 1 3
1

I tried creating a pivot table, but the pivot table is problematic since
this order is randomly generated by using the F9 key. Whenever I 'refresh'
the data in the pivot table, it re-randomize the numbers. The pivot table is
then displaying the old numbers. This is for a report, so all the numbers
must match. In short, I need this to run from a formula, and not a pivot
table.

THANKS!!!!

 

[Guest]

Assuming source data within A1:B140
and you have the names listed in D2 down, numbers 1-7 in E1:K1

Put in E2:
=SUMPRODUCT(($B$1:$B$140=$D2)*($A$1:$A$140=E$1))
Copy E2 across to K2, fill down to populate

 

[Guest]

Paperback Writer;

I would create the destination table as listed below and in each cell I
would use a match() statement. Create a destination table for each series of
columns. Then tabulate your findings using
sum(destinationtable1column1,destinationtable2column1,etc.)

This will give you the sum of the rank occurences for each person. I created
the same thing for a contact management spreadsheet that has a preference
selector for various criteria and then sum those that have a preference for
that criteria.

God Bless

Frank Pytel

http://groups.google.com/group/excel-applications-and-spreadsheet-programming

 

[Franz Verga]

Nel post:[email protected],

I need to figure out how to count occurrences (all lined up in two
columns), that will then be displayed in a table. The occurrences
are a series of rankings (1 - 7) spaced over 20 intervals.

The column looks like this.
COLUMN 1 COLUMN 2
1 Sally
2 Sue
3 Sam
4 Suzy
5 Sherri
6 Sandra
7 Serena

The above is repeated twenty times. The COLUMN 1 will have "1" in
the very next row (say A9), with a different order for the seven
girls.

I created a table that looks like this

NAME 1 2 3 4
5 6 7
SALLY
SUE
SAM
SUZY
SHERRI
SANDRA
SERENA

I need a formula that will populate how many times each girl was
selected first, second, third.... all the way to seventh.

SALLY might look like this.

NAME 1 2 3 4 5
6 7 SALLY 5 3 2 5
1 3 1

I tried creating a pivot table, but the pivot table is problematic
since
this order is randomly generated by using the F9 key. Whenever I
'refresh' the data in the pivot table, it re-randomize the numbers.
The pivot table is then displaying the old numbers. This is for a
report, so all the numbers must match. In short, I need this to run
from a formula, and not a pivot table.

THANKS!!!!

Hi Paperback Writer,

you can use a SUMPRODUCT formula, like this:

=SUMPRODUCT(($B$1:$B$34=$E5)*($A$1:$A$34=F$4))

where:

$A$1:$A$34 is the ranking for the girls

$B$1:$B$34 is the list with the all the entries of girls' names;

$E5 is the first name in the table;

F$4 is the first number in the table.

You will have a table like this:

1 2 3 4 5 6 7
Sally
Sue
Sam
Suzy
Sherri
Sandra
Serena

where Sally is in E5 while number 1 is in F4. So the above formula should be
written in F5. Then you can copy the formula across the columns and the rows
of the table.


--
Hope I helped you.


Thanks in advance for your feedback.


Ciao


Franz Verga from Italy