Pivot question

[teepee]

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

[bob777]

Please give an example of your data - I personally cannot yet understand what you are asking.

Bobf

[Roger Govier]

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

[teepee]

x-no-archive: yes

"bob777" wrote in message
news:[email protected]...
>
> Please give an example of your data - I personally cannot yet understand
> what you are asking.

Thanks for the response Bob. I'll post this rich text so you can see the layout better

So a sample of the original data from CSV

120145,1000,,,Z,
120156,1000,,Y,,

and a sample of the pivot. The hash is added as a reference to the point made below


time Data 1000 1001 1002 1004 1005 1006
120000 Count of w
Count of y
Count of z #
Count of x
120001 Count of w
Count of y
Count of z
Count of x
120002 Count of w
Count of y
Count of z
Count of x
120003 Count of w
Count of y
Count of z
Count of x
120004 Count of w
Count of y
Count of z
Count of x




It basically counts the number of w, x, y, and z against each time frame. In the
bit above there weren't any matches but if there had been a line in the data csv
saying:

120000,1000,,,Z,

then you'd have seen the number 1 appear where I've put the hash in.

I'd be grateful for your thoughts

best

tp

[teepee]

thanks - will try

"Roger Govier" <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >

[teepee]

"Roger Govier" <[email protected]> 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]

To extract the data to a different sheet, start the Advanced Filter from
the destination sheet, as described here:

http://www.contextures.com/xladvfilter01.html#ExtractWs

teepee wrote:
> "Roger Govier" <[email protected]> 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

[teepee]

"Debra Dalgleish" <[email protected]> wrote

> To extract the data to a different sheet, start the Advanced Filter from
> the destination sheet, as described here:
>
> 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