I have an Excel 2003 spreadsheet with Position Title, Location, Pay Plan,
Position Series, Position Grade, and FTEs. I want to organize this data by
Position Title, then Location, then Grade, and finally FTEs.

For example, I have multiple rows of Human Resources Specialist in different
locations, in different grades each with a FTE of 1. I'd like to consolidate
each row of Human Resources Specialist in the same location in the same grade
and have the row display the total number of FTEs for that title in that
grade at that location.

Thanks in advance for your help!

A pivot table might be a good option...

http://peltiertech.com/Excel/Pivots/pivotstart.htm
--
HTH...

Jim Thomlinson


"Jon in the U&L" wrote:

I have an Excel 2003 spreadsheet with Position Title, Location, Pay Plan,
Position Series, Position Grade, and FTEs. I want to organize this data by
Position Title, then Location, then Grade, and finally FTEs.

For example, I have multiple rows of Human Resources Specialist in different
locations, in different grades each with a FTE of 1. I'd like to consolidate
each row of Human Resources Specialist in the same location in the same grade
and have the row display the total number of FTEs for that title in that
grade at that location.

Thanks in advance for your help!

Thanks, Jim. I have been trying to use the Pivot Table function, however,
the ways I have set it up results in a table that extends way off to the
right as it runs through each location, grade, and series.

I have about 3000 positions spread across 60 locations with multiple series
and grades...

"Jim Thomlinson" wrote:

A pivot table might be a good option...

http://peltiertech.com/Excel/Pivots/pivotstart.htm
--

Pivot tables allow you to move the fields around to get the best possible
representation of the data...

Here is a sample set based on your fields
Position Title Location Pay Plan Position Series Position Grade FTEs
A Here Small 1 1 1
B There Medium 2 1 2
C Here Large 3 1 3
A There Jumbo 4 1 4
B Here Small 5 1 5
C There Medium 1 1 6
A Here Large 2 1 7
B There Jumbo 3 2 8
C Here Small 4 2 9
A There Medium 5 2 10
B Here Large 1 2 11
C There Jumbo 2 2 12
A Here Small 3 2 13
B There Medium 4 2 14

And here is apivot table of that data...

Position Title A

Average of FTEs Position Grade
Location Pay Plan 1 2 Grand Total
Here Large 7 7
Small 1 13 7
Here Total 4 13 7
There Jumbo 4 4
Medium 10 10
There Total 4 10 7
Grand Total 4 11.5 7

Fields can be in Columns or Rows...
--
HTH...

Jim Thomlinson


"Jon in the U&L" wrote:

I have an Excel 2003 spreadsheet with Position Title, Location, Pay Plan,
Position Series, Position Grade, and FTEs. I want to organize this data by
Position Title, then Location, then Grade, and finally FTEs.

For example, I have multiple rows of Human Resources Specialist in different
locations, in different grades each with a FTE of 1. I'd like to consolidate
each row of Human Resources Specialist in the same location in the same grade
and have the row display the total number of FTEs for that title in that
grade at that location.

Thanks in advance for your help!

That is helpful, Jim. However, the result I am trying to get to will end
looking like this:

Pos Title Location Pay Plan Pos Series Grade FTEs
Accountant MI AA 1111 3 3
HR Spclst WA AA 0000 1 6

So on and so forth. When I run the Pivot Table I get a very ugly table that
stretches way out to the right to accomodate the series, grade, pay plan...

Maybe I need to keep plugging away at setting up the Pivot correctly.

Thanks, again.

"Jim Thomlinson" wrote:

Pivot tables allow you to move the fields around to get the best possible
representation of the data...

Here is a sample set based on your fields
Position Title Location Pay Plan Position Series Position Grade FTEs
A Here Small 1 1 1
B There Medium 2 1 2
C Here Large 3 1 3
A There Jumbo 4 1 4
B Here Small 5 1 5
C There Medium 1 1 6
A Here Large 2 1 7
B There Jumbo 3 2 8
C Here Small 4 2 9
A There Medium 5 2 10
B Here Large 1 2 11
C There Jumbo 2 2 12
A Here Small 3 2 13
B There Medium 4 2 14

And here is apivot table of that data...

Position Title A

Average of FTEs Position Grade
Location Pay Plan 1 2 Grand Total
Here Large 7 7
Small 1 13 7
Here Total 4 13 7
There Jumbo 4 4
Medium 10 10
There Total 4 10 7
Grand Total 4 11.5 7

Fields can be in Columns or Rows...
--
HTH...

Jim Thomlinson

Can you post a small snippet of data that I can play with. How did you want
to handle Accountants in both MI and WA. My experience with this is that
there is a lot of mix and match of data.
--
HTH...

Jim Thomlinson


"Jon in the U&L" wrote:

That is helpful, Jim. However, the result I am trying to get to will end
looking like this:

Pos Title Location Pay Plan Pos Series Grade FTEs
Accountant MI AA 1111 3 3
HR Spclst WA AA 0000 1 6

So on and so forth. When I run the Pivot Table I get a very ugly table that
stretches way out to the right to accomodate the series, grade, pay plan...

Maybe I need to keep plugging away at setting up the Pivot correctly.

Thanks, again.

"Jim Thomlinson" wrote:

Pivot tables allow you to move the fields around to get the best possible
representation of the data...

Here is a sample set based on your fields
Position Title Location Pay Plan Position Series Position Grade FTEs
A Here Small 1 1 1
B There Medium 2 1 2
C Here Large 3 1 3
A There Jumbo 4 1 4
B Here Small 5 1 5
C There Medium 1 1 6
A Here Large 2 1 7
B There Jumbo 3 2 8
C Here Small 4 2 9
A There Medium 5 2 10
B Here Large 1 2 11
C There Jumbo 2 2 12
A Here Small 3 2 13
B There Medium 4 2 14

And here is apivot table of that data...

Position Title A

Average of FTEs Position Grade
Location Pay Plan 1 2 Grand Total
Here Large 7 7
Small 1 13 7
Here Total 4 13 7
There Jumbo 4 4
Medium 10 10
There Total 4 10 7
Grand Total 4 11.5 7

Fields can be in Columns or Rows...
--
HTH...

Jim Thomlinson

DUTY LOCATION STATE POSITION TITLE PAY PLAN GRADE SERIES FTE


Washington DC Administrative Specialist GS 12 301 1
Portland OR Administrative Specialist GS 11 301 1
Seattle WA Administrative Specialist GS 12 301 1
Vancouver WA Administrative Specialist GS 13 301 1
Vancouver WA Administrative Specialist GS 13 301 1

See the 2 positions in Vancouver WA in the same Grade? Ultimately I want to
end up with a worksheet that consolidates those two rows and totals the FTE
count thereby eliminating duplicate titles in the same grade, series, and
location.

I.e.:
Vancouver WA Account Specialist GS 13 1101 2

Thanks for your patience and help, Tom, it is really appreciated.


"Jim Thomlinson" wrote:

Can you post a small snippet of data that I can play with. How did you want
to handle Accountants in both MI and WA. My experience with this is that
there is a lot of mix and match of data.
--
HTH...

Jim Thomlinson

I took your Source data and did the following...

Created a pivot Table from the data (I think you are good to here)
Placed the Duty Location in the Left Column (not top row)
Placed the State to the immediate right of the Location (not in the data
section)
.... same thing for all of the remaining fields
Place FTE's in the data section.

Pivot tables like to add in subtotals. You can remove the subtotals by right
clicking on the field and select Field Options - Aggregation - None. Do
that for each field. You will end up with this

Sum of FTE
DUTY LOCATION STATE POSITION TITLE PAY PLAN GRADE SERIES Total
Portland OR Administrative Specialist GS 11 301 1
Seattle WA Administrative Specialist GS 12 301 1
Vancouver WA Administrative Specialist GS 13 301 2
Washington DC Administrative Specialist GS 12 301 1
Grand Total 5
If you ignore the text wrap it look exactly like what you wanted...

--
HTH...

Jim Thomlinson


"Jon in the U&L" wrote:

DUTY LOCATION STATE POSITION TITLE PAY PLAN GRADE SERIES FTE


Washington DC Administrative Specialist GS 12 301 1
Portland OR Administrative Specialist GS 11 301 1
Seattle WA Administrative Specialist GS 12 301 1
Vancouver WA Administrative Specialist GS 13 301 1
Vancouver WA Administrative Specialist GS 13 301 1

See the 2 positions in Vancouver WA in the same Grade? Ultimately I want to
end up with a worksheet that consolidates those two rows and totals the FTE
count thereby eliminating duplicate titles in the same grade, series, and
location.

I.e.:
Vancouver WA Account Specialist GS 13 1101 2

Thanks for your patience and help, Tom, it is really appreciated.


"Jim Thomlinson" wrote:

Can you post a small snippet of data that I can play with. How did you want
to handle Accountants in both MI and WA. My experience with this is that
there is a lot of mix and match of data.
--
HTH...

Jim Thomlinson

This is working, Jim. Thanks so much.

Follow-up question, more a formatting question I guess: I need the duty
location, state, title, pay plan, series, and grade to appear on every line.
What's happening is that, for example, for Account Services Manager there
will be one entry and then two duty locations rather than repeating the
position title.

Any help on that one?

My goodness, thanks, again, Jim!

"Jim Thomlinson" wrote:

I took your Source data and did the following...

Created a pivot Table from the data (I think you are good to here)
Placed the Duty Location in the Left Column (not top row)
Placed the State to the immediate right of the Location (not in the data
section)
... same thing for all of the remaining fields
Place FTE's in the data section.

Pivot tables like to add in subtotals. You can remove the subtotals by right
clicking on the field and select Field Options - Aggregation - None. Do
that for each field. You will end up with this

Sum of FTE
DUTY LOCATION STATE POSITION TITLE PAY PLAN GRADE SERIES Total
Portland OR Administrative Specialist GS 11 301 1
Seattle WA Administrative Specialist GS 12 301 1
Vancouver WA Administrative Specialist GS 13 301 2
Washington DC Administrative Specialist GS 12 301 1
Grand Total 5
If you ignore the text wrap it look exactly like what you wanted...

--
HTH...

Jim Thomlinson


"Jon in the U&L" wrote:

DUTY LOCATION STATE POSITION TITLE PAY PLAN GRADE SERIES FTE


Washington DC Administrative Specialist GS 12 301 1
Portland OR Administrative Specialist GS 11 301 1
Seattle WA Administrative Specialist GS 12 301 1
Vancouver WA Administrative Specialist GS 13 301 1
Vancouver WA Administrative Specialist GS 13 301 1

See the 2 positions in Vancouver WA in the same Grade? Ultimately I want to
end up with a worksheet that consolidates those two rows and totals the FTE
count thereby eliminating duplicate titles in the same grade, series, and
location.

I.e.:
Vancouver WA Account Specialist GS 13 1101 2

Thanks for your patience and help, Tom, it is really appreciated.


"Jim Thomlinson" wrote:

Can you post a small snippet of data that I can play with. How did you want
to handle Accountants in both MI and WA. My experience with this is that
there is a lot of mix and match of data.
--
HTH...

Jim Thomlinson

From http://www.contextures.com/xlfaqPivot.html

How do I get the row field headings to repeat in a pivot table?
The row headings show once in a PivotTable, and there's no setting you can
change, to force them to repeat. To create a table with a heading on each
row, you could copy the pivot table, paste it as values in another location,
and fill in the blanks. In the beta version of Excel 2010, this feature is
finally available.

If you want to copy and paste the pivot as values only I can show you a
technique to easily fill in the blank. Let me know if that is of interest...
--
HTH...

Jim Thomlinson


"Jon in the U&L" wrote:

This is working, Jim. Thanks so much.

Follow-up question, more a formatting question I guess: I need the duty
location, state, title, pay plan, series, and grade to appear on every line.
What's happening is that, for example, for Account Services Manager there
will be one entry and then two duty locations rather than repeating the
position title.

Any help on that one?

My goodness, thanks, again, Jim!

"Jim Thomlinson" wrote:

I took your Source data and did the following...

Created a pivot Table from the data (I think you are good to here)
Placed the Duty Location in the Left Column (not top row)
Placed the State to the immediate right of the Location (not in the data
section)
... same thing for all of the remaining fields
Place FTE's in the data section.

Pivot tables like to add in subtotals. You can remove the subtotals by right
clicking on the field and select Field Options - Aggregation - None. Do
that for each field. You will end up with this

Sum of FTE
DUTY LOCATION STATE POSITION TITLE PAY PLAN GRADE SERIES Total
Portland OR Administrative Specialist GS 11 301 1
Seattle WA Administrative Specialist GS 12 301 1
Vancouver WA Administrative Specialist GS 13 301 2
Washington DC Administrative Specialist GS 12 301 1
Grand Total 5
If you ignore the text wrap it look exactly like what you wanted...

--
HTH...

Jim Thomlinson


"Jon in the U&L" wrote:

DUTY LOCATION STATE POSITION TITLE PAY PLAN GRADE SERIES FTE


Washington DC Administrative Specialist GS 12 301 1
Portland OR Administrative Specialist GS 11 301 1
Seattle WA Administrative Specialist GS 12 301 1
Vancouver WA Administrative Specialist GS 13 301 1
Vancouver WA Administrative Specialist GS 13 301 1

See the 2 positions in Vancouver WA in the same Grade? Ultimately I want to
end up with a worksheet that consolidates those two rows and totals the FTE
count thereby eliminating duplicate titles in the same grade, series, and
location.

I.e.:
Vancouver WA Account Specialist GS 13 1101 2

Thanks for your patience and help, Tom, it is really appreciated.


"Jim Thomlinson" wrote:

Can you post a small snippet of data that I can play with. How did you want
to handle Accountants in both MI and WA. My experience with this is that
there is a lot of mix and match of data.
--
HTH...

Jim Thomlinson

I am definitely interested, Jim. Man, I think I owe you some hourly pay
here...

"Jim Thomlinson" wrote:

From http://www.contextures.com/xlfaqPivot.html

How do I get the row field headings to repeat in a pivot table?
The row headings show once in a PivotTable, and there's no setting you can
change, to force them to repeat. To create a table with a heading on each
row, you could copy the pivot table, paste it as values in another location,
and fill in the blanks. In the beta version of Excel 2010, this feature is
finally available.

If you want to copy and paste the pivot as values only I can show you a
technique to easily fill in the blank. Let me know if that is of interest...
--
HTH...

Jim Thomlinson


"Jon in the U&L" wrote:

This is working, Jim. Thanks so much.

Follow-up question, more a formatting question I guess: I need the duty
location, state, title, pay plan, series, and grade to appear on every line.
What's happening is that, for example, for Account Services Manager there
will be one entry and then two duty locations rather than repeating the
position title.

Any help on that one?

My goodness, thanks, again, Jim!

"Jim Thomlinson" wrote:

I took your Source data and did the following...

Created a pivot Table from the data (I think you are good to here)
Placed the Duty Location in the Left Column (not top row)
Placed the State to the immediate right of the Location (not in the data
section)
... same thing for all of the remaining fields
Place FTE's in the data section.

Pivot tables like to add in subtotals. You can remove the subtotals by right
clicking on the field and select Field Options - Aggregation - None. Do
that for each field. You will end up with this

Sum of FTE
DUTY LOCATION STATE POSITION TITLE PAY PLAN GRADE SERIES Total
Portland OR Administrative Specialist GS 11 301 1
Seattle WA Administrative Specialist GS 12 301 1
Vancouver WA Administrative Specialist GS 13 301 2
Washington DC Administrative Specialist GS 12 301 1
Grand Total 5
If you ignore the text wrap it look exactly like what you wanted...

--
HTH...

Jim Thomlinson


"Jon in the U&L" wrote:

DUTY LOCATION STATE POSITION TITLE PAY PLAN GRADE SERIES FTE


Washington DC Administrative Specialist GS 12 301 1
Portland OR Administrative Specialist GS 11 301 1
Seattle WA Administrative Specialist GS 12 301 1
Vancouver WA Administrative Specialist GS 13 301 1
Vancouver WA Administrative Specialist GS 13 301 1

See the 2 positions in Vancouver WA in the same Grade? Ultimately I want to
end up with a worksheet that consolidates those two rows and totals the FTE
count thereby eliminating duplicate titles in the same grade, series, and
location.

I.e.:
Vancouver WA Account Specialist GS 13 1101 2

Thanks for your patience and help, Tom, it is really appreciated.


"Jim Thomlinson" wrote:

Can you post a small snippet of data that I can play with. How did you want
to handle Accountants in both MI and WA. My experience with this is that
there is a lot of mix and match of data.
--
HTH...

Jim Thomlinson

Sorry about taking so long to get back to you. Once you have copied your
pivot and pasted values to get a static copy do the following...

1. Highlight the area with the blanks that you want to fill.
2. Click the F5 key
3. Click Special... in the bottom Left corner of the Goto Dialog that just
opened
4. Choose Blanks fro the option buttons
5. Choose Ok
Now all of the balnk cells in the highlighted area should be selected
6. Type = <Up Arrow to create a formula making the cell equal to the row
above
7. Do Not just hit <Enter. You want to hit Ctrl + Enter to palce the
formula in all of the blank cells.
All of the blanks will now be filled with formulas making them equal to the
cell above.
Copy and paste special values the entire area to remove the formulas.
--
HTH...

Jim Thomlinson


"Jon in the U&L" wrote:

I am definitely interested, Jim. Man, I think I owe you some hourly pay
here...

"Jim Thomlinson" wrote:

From http://www.contextures.com/xlfaqPivot.html

How do I get the row field headings to repeat in a pivot table?
The row headings show once in a PivotTable, and there's no setting you can
change, to force them to repeat. To create a table with a heading on each
row, you could copy the pivot table, paste it as values in another location,
and fill in the blanks. In the beta version of Excel 2010, this feature is
finally available.

If you want to copy and paste the pivot as values only I can show you a
technique to easily fill in the blank. Let me know if that is of interest...
--
HTH...

Jim Thomlinson


"Jon in the U&L" wrote:

This is working, Jim. Thanks so much.

Follow-up question, more a formatting question I guess: I need the duty
location, state, title, pay plan, series, and grade to appear on every line.
What's happening is that, for example, for Account Services Manager there
will be one entry and then two duty locations rather than repeating the
position title.

Any help on that one?

My goodness, thanks, again, Jim!

"Jim Thomlinson" wrote:

I took your Source data and did the following...

Created a pivot Table from the data (I think you are good to here)
Placed the Duty Location in the Left Column (not top row)
Placed the State to the immediate right of the Location (not in the data
section)
... same thing for all of the remaining fields
Place FTE's in the data section.

Pivot tables like to add in subtotals. You can remove the subtotals by right
clicking on the field and select Field Options - Aggregation - None. Do
that for each field. You will end up with this

Sum of FTE
DUTY LOCATION STATE POSITION TITLE PAY PLAN GRADE SERIES Total
Portland OR Administrative Specialist GS 11 301 1
Seattle WA Administrative Specialist GS 12 301 1
Vancouver WA Administrative Specialist GS 13 301 2
Washington DC Administrative Specialist GS 12 301 1
Grand Total 5
If you ignore the text wrap it look exactly like what you wanted...

--
HTH...

Jim Thomlinson


"Jon in the U&L" wrote:

DUTY LOCATION STATE POSITION TITLE PAY PLAN GRADE SERIES FTE


Washington DC Administrative Specialist GS 12 301 1
Portland OR Administrative Specialist GS 11 301 1
Seattle WA Administrative Specialist GS 12 301 1
Vancouver WA Administrative Specialist GS 13 301 1
Vancouver WA Administrative Specialist GS 13 301 1

See the 2 positions in Vancouver WA in the same Grade? Ultimately I want to
end up with a worksheet that consolidates those two rows and totals the FTE
count thereby eliminating duplicate titles in the same grade, series, and
location.

I.e.:
Vancouver WA Account Specialist GS 13 1101 2

Thanks for your patience and help, Tom, it is really appreciated.


"Jim Thomlinson" wrote:

Can you post a small snippet of data that I can play with. How did you want
to handle Accountants in both MI and WA. My experience with this is that
there is a lot of mix and match of data.
--
HTH...

Jim Thomlinson

Sorry for resetting such an old thread.

I've added two additional fields of data to this data set: Status Code and
Reason Code. I have the data for these fields only for FY09, none for FY10.
When I ran my pivot table as Jim explained below I got position titles
appearing in FY09 and FY10 along with the same state, duty location, pay
plan, series, grade to concolidate and give me a FTE for both FY09 and FY10.
However, when I ran it with these two added data fields I get:

Account Services Manager OR Portland GS 1101 14 (blank) (blank) 1
WA Spokane GS 1101 14 (blank) (blank) 1
Account Services Manager
OR Portland GS 1101 14 C A 1
WA Spokane GS 1101 14 C A 0.6

I think I understand why these two position titles with the same state, duty
location, etc are now broken out...the new data fields which have data only
for FY09.

Does anyone know a way to force these lines together where everything else
but the status and reason code match?

TIA!

"Jon in the U&L" wrote:

I have an Excel 2003 spreadsheet with Position Title, Location, Pay Plan,
Position Series, Position Grade, and FTEs. I want to organize this data by
Position Title, then Location, then Grade, and finally FTEs.

For example, I have multiple rows of Human Resources Specialist in different
locations, in different grades each with a FTE of 1. I'd like to consolidate
each row of Human Resources Specialist in the same location in the same grade
and have the row display the total number of FTEs for that title in that
grade at that location.

Thanks in advance for your help!