|
pivot tables & queries
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 |
| All times are GMT +1. The time now is 07:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com