[Solved] Using Data Pilot

Discuss the spreadsheet application

[Solved] Using Data Pilot

Postby williaba » Wed Jun 25, 2008 6:14 pm

Sheet 1
number-Column A contains dozens of numbers - some repeated.
text-Column B contains either yes or no against each number.
A number (say, 2345) could appear 10 times in Column A with 6 occurrences of yes and 4 occurrences of no in Column B.
This could vary weekly.

Sheet 2
number-Column A contains a distinct list of the numbers found in Sheet 1.
Column B has to indicate the number of occurrences of yes on Sheet 1 for each number on Sheet 2.
col A col B
2345 6

Is this possible? Thanks.
Last edited by williaba on Thu Jun 26, 2008 3:42 pm, edited 2 times in total.
Posts: 55
Joined: Tue Jan 08, 2008 7:05 pm

Re: Calc Questions

Postby Villeroy » Wed Jun 25, 2008 6:33 pm

This is what a data pilot is made for. Make shure, your list has a row of column headers.
Select your list (or entire columns if there is nothing else on the sheet)
[X] Create piot from current selection, [OK]
Next dialog...
Button [More Options]
Set A1 of second sheet as target for the resulting pilot
Check all other options
Drag the yes/no field (B) to "Column Fields"
Drag the categories (A) to "Row Fields"
Drag the categories (A) to "Data Fields", double-click and set function "Count"

Please change the subject line of your initial thread to something meaningful in order to keep this forum as a searchable knowlege base. I'd suggest "Counting list items" or something.
Attached an example with pilot on the same sheet. (target: $Sheet1.$D$1)
(17.3 KiB) Downloaded 136 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Posts: 28851
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Return to Calc

Who is online

Users browsing this forum: No registered users and 25 guests