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

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Paperback Writer" wrote:
> 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!!!!

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...et-programming

"Paperback Writer" wrote:

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

Nel post:E2DF7FA5-C693-42B9-980B-(E-Mail Removed),
Paperback Writer <(E-Mail Removed)> ha scritto:
> 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