Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Occurrences In A Column, Then Display In A Table
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!!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Occurrences In A Column, Then Display In A Table
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!!!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Occurrences In A Column, Then Display In A Table
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,destinationtable2colu mn1,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!!!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Occurrences In A Column, Then Display In A Table
Nel ,
Paperback Writer 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)) whe $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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count number of occurrences | Excel Discussion (Misc queries) | |||
Need column evaluated to count total of occurrences of "Y". How? | Excel Worksheet Functions | |||
Count Number of Occurrences in a Column | Excel Worksheet Functions | |||
Count occurrences of values in a column??!! | Excel Worksheet Functions | |||
Count unique occurrences of name | Excel Discussion (Misc queries) |