Prev: Consolidating and totaling rows into single row
Next: Locked for Editing
From: Jon in the U&L on 11 Mar 2010 18:57
I previously asked about consolidating rows of data that I was looking at and
got the following response that worked great:

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

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 above 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!
 | 
Pages: 1
Prev: Consolidating and totaling rows into single row
Next: Locked for Editing