I track the movement of kids on a massive log. As they move one home (type
varies) and move into another. I would like to be able to track/summarize the
# of kids moving from a certain type of home into another. Problem is, each
of these movements are on a seperate row (record). I am very familiar with
pivot tables & advanced filtering. I would like to use the pivot table, but
I'm not sure how to go about it.

Assuming your table is similar to this:

Date Client# MoveFrom MoveTo
1/1/05 203 TypeA TypeB

In the Pivot table, add MoveFrom to the Row area, MoveTo to the Column
Area, and Client# to the Data area, as Count of Client#

Pivot tables & tracking data wrote:
I track the movement of kids on a massive log. As they move one home (type
varies) and move into another. I would like to be able to track/summarize the
# of kids moving from a certain type of home into another. Problem is, each
of these movements are on a seperate row (record). I am very familiar with
pivot tables & advanced filtering. I would like to use the pivot table, but
I'm not sure how to go about it.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Unfortunately, due to the # of moves these kids may have each row is a
separate record (adding moves in columns would be never ending). The table
looks more like this
Placement Date Placement Termination Date Placement
1/1/05 5/1/05 Jane, Mary
Placement Date Placement Termination Date Placement#
5/1/05 Jones,
Robert

Any suggestions? I need to know not only who moved on what date but where
they came from. The only way I've been able to do it is through an advanced
filter on the spreadsheet. But its not a reader friendly printout


"Debra Dalgleish" wrote:

Assuming your table is similar to this:

Date Client# MoveFrom MoveTo
1/1/05 203 TypeA TypeB

In the Pivot table, add MoveFrom to the Row area, MoveTo to the Column
Area, and Client# to the Data area, as Count of Client#

Pivot tables & tracking data wrote:
I track the movement of kids on a massive log. As they move one home (type
varies) and move into another. I would like to be able to track/summarize the
# of kids moving from a certain type of home into another. Problem is, each
of these movements are on a seperate row (record). I am very familiar with
pivot tables & advanced filtering. I would like to use the pivot table, but
I'm not sure how to go about it.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

My sample assumes one row per move, with two columns of Home Type
information, not multiple columns.
In your data, how do you determine the home type, or where they were
previously?

Pivot Table/Query wrote:
Unfortunately, due to the # of moves these kids may have each row is a
separate record (adding moves in columns would be never ending). The table
looks more like this
Placement Date Placement Termination Date Placement
1/1/05 5/1/05 Jane, Mary
Placement Date Placement Termination Date Placement#
5/1/05 Jones,
Robert

Any suggestions? I need to know not only who moved on what date but where
they came from. The only way I've been able to do it is through an advanced
filter on the spreadsheet. But its not a reader friendly printout


"Debra Dalgleish" wrote:


Assuming your table is similar to this:

Date Client# MoveFrom MoveTo
1/1/05 203 TypeA TypeB

In the Pivot table, add MoveFrom to the Row area, MoveTo to the Column
Area, and Client# to the Data area, as Count of Client#

Pivot tables & tracking data wrote:

I track the movement of kids on a massive log. As they move one home (type
varies) and move into another. I would like to be able to track/summarize the
# of kids moving from a certain type of home into another. Problem is, each
of these movements are on a seperate row (record). I am very familiar with
pivot tables & advanced filtering. I would like to use the pivot table, but
I'm not sure how to go about it.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

I have been capturing this data for about 8 months.For every move a new row
is created with the child's basic info, I then add their placement date for
the new placement & the resource used. The resource type depends on the name
of the home where the child is placed (v-lookup is used for that on my master
table so it prefills). When I use advanced filter I can use a formula to
capture say all kids placed or terminated placement between certain dates.
This allows me to see where they were previously and currently (the dates are
always the same for the previous termination date and the next placement
date, i.e. if a child leaves a home today, the next row should have a
placement date of today...).

"Debra Dalgleish" wrote:

My sample assumes one row per move, with two columns of Home Type
information, not multiple columns.
In your data, how do you determine the home type, or where they were
previously?

Pivot Table/Query wrote:
Unfortunately, due to the # of moves these kids may have each row is a
separate record (adding moves in columns would be never ending). The table
looks more like this
Placement Date Placement Termination Date Placement
1/1/05 5/1/05 Jane, Mary
Placement Date Placement Termination Date Placement#
5/1/05 Jones,
Robert

Any suggestions? I need to know not only who moved on what date but where
they came from. The only way I've been able to do it is through an advanced
filter on the spreadsheet. But its not a reader friendly printout


"Debra Dalgleish" wrote:


Assuming your table is similar to this:

Date Client# MoveFrom MoveTo
1/1/05 203 TypeA TypeB

In the Pivot table, add MoveFrom to the Row area, MoveTo to the Column
Area, and Client# to the Data area, as Count of Client#

Pivot tables & tracking data wrote:

I track the movement of kids on a massive log. As they move one home (type
varies) and move into another. I would like to be able to track/summarize the
# of kids moving from a certain type of home into another. Problem is, each
of these movements are on a seperate row (record). I am very familiar with
pivot tables & advanced filtering. I would like to use the pivot table, but
I'm not sure how to go about it.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

A pivot table summarizes data, so it can't detect what's in the next row
of the source data, or make a connection between termination and start
dates in different rows.

You could add another column, to record the new home type, and add that
to the pivot table.

Pivot Table/Query wrote:
I have been capturing this data for about 8 months.For every move a new row
is created with the child's basic info, I then add their placement date for
the new placement & the resource used. The resource type depends on the name
of the home where the child is placed (v-lookup is used for that on my master
table so it prefills). When I use advanced filter I can use a formula to
capture say all kids placed or terminated placement between certain dates.
This allows me to see where they were previously and currently (the dates are
always the same for the previous termination date and the next placement
date, i.e. if a child leaves a home today, the next row should have a
placement date of today...).

"Debra Dalgleish" wrote:


My sample assumes one row per move, with two columns of Home Type
information, not multiple columns.
In your data, how do you determine the home type, or where they were
previously?

Pivot Table/Query wrote:

Unfortunately, due to the # of moves these kids may have each row is a
separate record (adding moves in columns would be never ending). The table
looks more like this
Placement Date Placement Termination Date Placement
1/1/05 5/1/05 Jane, Mary
Placement Date Placement Termination Date Placement#
5/1/05 Jones,
Robert

Any suggestions? I need to know not only who moved on what date but where
they came from. The only way I've been able to do it is through an advanced
filter on the spreadsheet. But its not a reader friendly printout


"Debra Dalgleish" wrote:



Assuming your table is similar to this:

Date Client# MoveFrom MoveTo
1/1/05 203 TypeA TypeB

In the Pivot table, add MoveFrom to the Row area, MoveTo to the Column
Area, and Client# to the Data area, as Count of Client#

Pivot tables & tracking data wrote:


I track the movement of kids on a massive log. As they move one home (type
varies) and move into another. I would like to be able to track/summarize the
# of kids moving from a certain type of home into another. Problem is, each
of these movements are on a seperate row (record). I am very familiar with
pivot tables & advanced filtering. I would like to use the pivot table, but
I'm not sure how to go about it.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Okay. I played around with the pivot table & I have it almost perfect to my
needs I think. Only problem is this. I would like to be able to select a
placement date OR a termination date for the same row. I have accomplished
this with an advanced filter on my log, is it possible to select 1 OR the
other from 2 pivot table columns? It seems like its in AND mode.

"Debra Dalgleish" wrote:

A pivot table summarizes data, so it can't detect what's in the next row
of the source data, or make a connection between termination and start
dates in different rows.

You could add another column, to record the new home type, and add that
to the pivot table.

Pivot Table/Query wrote:
I have been capturing this data for about 8 months.For every move a new row
is created with the child's basic info, I then add their placement date for
the new placement & the resource used. The resource type depends on the name
of the home where the child is placed (v-lookup is used for that on my master
table so it prefills). When I use advanced filter I can use a formula to
capture say all kids placed or terminated placement between certain dates.
This allows me to see where they were previously and currently (the dates are
always the same for the previous termination date and the next placement
date, i.e. if a child leaves a home today, the next row should have a
placement date of today...).

"Debra Dalgleish" wrote:


My sample assumes one row per move, with two columns of Home Type
information, not multiple columns.
In your data, how do you determine the home type, or where they were
previously?

Pivot Table/Query wrote:

Unfortunately, due to the # of moves these kids may have each row is a
separate record (adding moves in columns would be never ending). The table
looks more like this
Placement Date Placement Termination Date Placement
1/1/05 5/1/05 Jane, Mary
Placement Date Placement Termination Date Placement#
5/1/05 Jones,
Robert

Any suggestions? I need to know not only who moved on what date but where
they came from. The only way I've been able to do it is through an advanced
filter on the spreadsheet. But its not a reader friendly printout


"Debra Dalgleish" wrote:



Assuming your table is similar to this:

Date Client# MoveFrom MoveTo
1/1/05 203 TypeA TypeB

In the Pivot table, add MoveFrom to the Row area, MoveTo to the Column
Area, and Client# to the Data area, as Count of Client#

Pivot tables & tracking data wrote:


I track the movement of kids on a massive log. As they move one home (type
varies) and move into another. I would like to be able to track/summarize the
# of kids moving from a certain type of home into another. Problem is, each
of these movements are on a seperate row (record). I am very familiar with
pivot tables & advanced filtering. I would like to use the pivot table, but
I'm not sure how to go about it.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Okay I figured it out. Here it is for anyone who may have this problem. The
main thing that I needed was to capture all movement on a specified day, ie.
moved from/move to on 6/1/05. I went into my spreadsheet and added 2 things.
1 was a cell with the date i was intested in & the other was a formula to
come up with a yes/no answer. I had it check the placement date & termination
dates to see if it falls between the requested dates I'm looking for, if
so...yes, if not..no. I then added this last column to my Pivot Table at the
Page level. I can then select all "YES" answers & see where the child came
from & where they went! Thank you so much for all of your help

"Debra Dalgleish" wrote:

A pivot table summarizes data, so it can't detect what's in the next row
of the source data, or make a connection between termination and start
dates in different rows.

You could add another column, to record the new home type, and add that
to the pivot table.

Pivot Table/Query wrote:
I have been capturing this data for about 8 months.For every move a new row
is created with the child's basic info, I then add their placement date for
the new placement & the resource used. The resource type depends on the name
of the home where the child is placed (v-lookup is used for that on my master
table so it prefills). When I use advanced filter I can use a formula to
capture say all kids placed or terminated placement between certain dates.
This allows me to see where they were previously and currently (the dates are
always the same for the previous termination date and the next placement
date, i.e. if a child leaves a home today, the next row should have a
placement date of today...).

"Debra Dalgleish" wrote:


My sample assumes one row per move, with two columns of Home Type
information, not multiple columns.
In your data, how do you determine the home type, or where they were
previously?

Pivot Table/Query wrote:

Unfortunately, due to the # of moves these kids may have each row is a
separate record (adding moves in columns would be never ending). The table
looks more like this
Placement Date Placement Termination Date Placement
1/1/05 5/1/05 Jane, Mary
Placement Date Placement Termination Date Placement#
5/1/05 Jones,
Robert

Any suggestions? I need to know not only who moved on what date but where
they came from. The only way I've been able to do it is through an advanced
filter on the spreadsheet. But its not a reader friendly printout


"Debra Dalgleish" wrote:



Assuming your table is similar to this:

Date Client# MoveFrom MoveTo
1/1/05 203 TypeA TypeB

In the Pivot table, add MoveFrom to the Row area, MoveTo to the Column
Area, and Client# to the Data area, as Count of Client#

Pivot tables & tracking data wrote:


I track the movement of kids on a massive log. As they move one home (type
varies) and move into another. I would like to be able to track/summarize the
# of kids moving from a certain type of home into another. Problem is, each
of these movements are on a seperate row (record). I am very familiar with
pivot tables & advanced filtering. I would like to use the pivot table, but
I'm not sure how to go about it.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html