Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Pivot question
Hello - may I prevail on your collective knowledge please to solve a
problem? I currently have a pivot table which takes 6 data entry columns, tables the first entry vertical, the second horizontal, and puts the last 4 in the main body of the table, counting their incidence in relation to the first 2. A pretty bog standard pivot table in fact. My problem is this. In order to use a certain procedure, I have to rewrite this process to achieve the same end without using a pivot to get there. The authors of the procedure assure me it can be done using other data filtering functions within Excel but they don't say how. Anyone got any idea? Thank in advance teepee |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot question
Please give an example of your data - I personally cannot yet understand what you are asking. Bobf -- bob777 ------------------------------------------------------------------------ bob777's Profile: http://www.excelforum.com/member.php...o&userid=28504 View this thread: http://www.excelforum.com/showthread...hreadid=495710 |
#3
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Pivot question
Hi
With your data on Sheet1, in a range from say A1:F100, with A1:F1 holding the headers, set up a table on Sheet2 with unique values from Sheet1 column A in cells A2:An, unique values from Column B of Sheet1 in cells B1:?1 (n represents the last row number, ? represents the last column letter) then in cell B2 of sheet2 enter =SUMPRODUCT(--(Sheet1!$A$2:$A$100=$A2),--(Sheet1$B$2:$B$100=B$1),--(Sheet1!C$2:C$100<"")) Copy across for the width of the column headings created. Copy the whole row of formulae down for the extent of the entries in column A of Sheet2 You would then need to repeat the block from column B to column ?, 3 more times to represent each of the remaining 4 columns of data from Sheet1, but changing the last part of the sumproduct formula to Sheet1!$D$2:$D$100, E2:E100, and F2:F100 respectively. If you wanted to make the table smaller and aggregate the count for all 4 columns of data, then change the formula to =SUMPRODUCT(--(Sheet1!$A$2:$A$100=$A2),--(Sheet1$B$2:$B$100=B$1),--(Sheet1!C$2:F$100<"")) Regards Roger Govier teepee wrote: Hello - may I prevail on your collective knowledge please to solve a problem? I currently have a pivot table which takes 6 data entry columns, tables the first entry vertical, the second horizontal, and puts the last 4 in the main body of the table, counting their incidence in relation to the first 2. A pretty bog standard pivot table in fact. My problem is this. In order to use a certain procedure, I have to rewrite this process to achieve the same end without using a pivot to get there. The authors of the procedure assure me it can be done using other data filtering functions within Excel but they don't say how. Anyone got any idea? Thank in advance teepee |
#4
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Pivot question
thanks - will try
"Roger Govier" wrote in message ... Hi With your data on Sheet1, in a range from say A1:F100, with A1:F1 holding the headers, set up a table on Sheet2 with unique values from Sheet1 column A in cells A2:An, unique values from Column B of Sheet1 in cells B1:?1 (n represents the last row number, ? represents the last column letter) then in cell B2 of sheet2 enter =SUMPRODUCT(--(Sheet1!$A$2:$A$100=$A2),--(Sheet1$B$2:$B$100=B$1),--(Sheet1!C $2:C$100<"")) Copy across for the width of the column headings created. Copy the whole row of formulae down for the extent of the entries in column A of Sheet2 You would then need to repeat the block from column B to column ?, 3 more times to represent each of the remaining 4 columns of data from Sheet1, but changing the last part of the sumproduct formula to Sheet1!$D$2:$D$100, E2:E100, and F2:F100 respectively. If you wanted to make the table smaller and aggregate the count for all 4 columns of data, then change the formula to =SUMPRODUCT(--(Sheet1!$A$2:$A$100=$A2),--(Sheet1$B$2:$B$100=B$1),--(Sheet1!C $2:F$100<"")) Regards Roger Govier teepee wrote: Hello - may I prevail on your collective knowledge please to solve a problem? I currently have a pivot table which takes 6 data entry columns, tables the first entry vertical, the second horizontal, and puts the last 4 in the main body of the table, counting their incidence in relation to the first 2. A pretty bog standard pivot table in fact. My problem is this. In order to use a certain procedure, I have to rewrite this process to achieve the same end without using a pivot to get there. The authors of the procedure assure me it can be done using other data filtering functions within Excel but they don't say how. Anyone got any idea? Thank in advance teepee |
#5
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Pivot question
"Roger Govier" wrote With your data on Sheet1, in a range from say A1:F100, with A1:F1 holding the headers, set up a table on Sheet2 with unique values from Sheet1 It says you can only copy filtered data onto the active sheet 8-( |
#6
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Pivot question
To extract the data to a different sheet, start the Advanced Filter from
the destination sheet, as described he http://www.contextures.com/xladvfilter01.html#ExtractWs teepee wrote: "Roger Govier" wrote With your data on Sheet1, in a range from say A1:F100, with A1:F1 holding the headers, set up a table on Sheet2 with unique values from Sheet1 It says you can only copy filtered data onto the active sheet 8-( -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#7
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Pivot question
"Debra Dalgleish" wrote To extract the data to a different sheet, start the Advanced Filter from the destination sheet, as described he http://www.contextures.com/xladvfilter01.html#ExtractWs Thanks - I'm now told for the same reason I can't use pivot with the software I'm employing, it won't recognise advanced filter either, so I have to use arrays on their own 8-( Back to the drawing board |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot / general chart formatting question | Charts and Charting in Excel | |||
Pivot Table Question : If statment in Pivot Table?? | Excel Discussion (Misc queries) | |||
Pivot Table Question - a puzzler | Excel Discussion (Misc queries) | |||
Excel Pivot Table Refresh Question | Excel Discussion (Misc queries) | |||
Excel Pivot Table Question | Excel Discussion (Misc queries) |