Format/SubTotals

[MrsMope]

Hi All,
I need to take this:

Curr Loc	Location	Name	Social Security Number	Class Description	Coverage Tier	Med Admin	Cobra/HIPPA	PPO Fee	UR/CM	Billed Amount	Coverage Month	Count
207	7005 IAD	Doe,John A	xxx-xx-xxxx	FULL/EPO/PHCS	e	13.7	1.75	6.75	2.75	24.95	08/2006	1
207	7005 IAD	Doe,John B	xxx-xx-xxxx	FULL/EPO/PHCS	s	13.7	1.75	6.75	2.75	24.95	08/2006	1
207	7005 IAD	Doe,John C	xxx-xx-xxxx	ENHANCED/EPO/PHCS	e	13.7	1.75	6.75	2.75	24.95	08/2006	1
207	7005 IAD	Doe,John D	xxx-xx-xxxx	FULL/EPO/PHCS	e	13.7	1.75	6.75	2.75	24.95	08/2006	1
207	7005 IAD	Doe,John E	xxx-xx-xxxx	ENHANCED/EPO/PHCS	e	13.7	1.75	6.75	2.75	24.95	08/2006	1
207	7005 IAD	Doe,John F	xxx-xx-xxxx	FULL/EPO/PHCS	e	13.7	1.75	6.75	2.75	24.95	08/2006	1
207	7005 IAD	Doe,John G	xxx-xx-xxxx	STANDARD/EPO/PHCS	e	13.7	1.75	6.75	2.75	24.95	08/2006	1
207	7005 IAD	Doe,John H	xxx-xx-xxxx	FULL/EPO/PHCS	e	13.7	1.75	6.75	2.75	24.95	08/2006	1
207	7005 IAD	Doe,John I	xxx-xx-xxxx	FULL/EPO/PHCS	e	13.7	1.75	6.75	2.75	24.95	08/2006	1
207	7005 IAD	Doe,John J	xxx-xx-xxxx	STANDARD/EPO/PHCS	f	13.7	1.75	6.75	2.75	24.95	7/1/2006	1
207	7005 IAD	Doe,John K	xxx-xx-xxxx	FULL/EPO/PHCS	e	13.7	1.75	6.75	2.75	24.95	7/1/2006	1

and create this:

New Code	EMPLOYEE	SOC SEC	Plan and networkdesignation	COV	MEDICAL	COBRA/	PPO	UR/CARE	TOTAL
	NAME	NUMBER		STAT	ADMIN	HIPAA	FEE	COORD	CHARGES
	=====================	============		======	===========	============	=============	==========	=============
									
018	2105 USA OPERATIONS								
									
	Doe,John A	xxx-xx-xxxx	FULL/EPO/UHC	e	13.7	1.75	6.75	2.75	24.95
	Doe,John B	xxx-xx-xxxx		s	13.7	1.75	6.75	2.75	24.95
	Doe,John C	xxx-xx-xxxx		e	13.7	1.75	6.75	2.75	24.95
	Doe,John D	xxx-xx-xxxx		e	13.7	1.75	6.75	2.75	24.95
	Doe,John E	xxx-xx-xxxx		e	13.7	1.75	6.75	2.75	24.95
	Doe,John F	xxx-xx-xxxx		e	13.7	1.75	6.75	2.75	24.95
	Doe,John G	xxx-xx-xxxx		e	13.7	1.75	6.75	2.75	24.95
	Doe,John H	xxx-xx-xxxx		e	13.7	1.75	6.75	2.75	24.95
	Doe,John I	xxx-xx-xxxx		e	13.7	1.75	6.75	2.75	24.95
					--------	--------	--------	--------	--------
	**  Location Subtotals				123.3	15.75	60.75	24.75	224.55
									
	Adjustments for 	Jul-06							
									
	Doe,John J	xxx-xx-xxxx		f	13.7	1.75	6.75	2.75	24.95
	Doe,John K	xxx-xx-xxxx		e	13.7	1.75	6.75	2.75	24.95
					--------	--------	--------	--------	--------
	**  Adjustment Subtotals				27.4	3.5	13.5	5.5	49.9
					--------	--------	--------	--------	--------
	** Location Totals				178.1	22.75	87.75	35.75	324.35
	Location Count:				11	11	11	11	11

I have tried pivot tables, but I have to many rows/columns to add. I have tried Subtotals, but I can't get the month subtotal and location subtotal on the same line. Any suggestions?

 

[SkipVought]

Hi,

Either a MIRACLE occurs...

or

you have to provide a more consistent example and more information.

For instance, where does
[tt]
018 2105 USA OPERATIONS
[/tt]
come from in your source data/logic?

Where does Jul 06 come from? Do you have to differentiate between Jul 06 and other months?

Where did ENHANCED/EPO/PHCS go?

Your target report format is rather complex for Excel's built-in reporting functionality, like SUBTOTAL and PIVOT TABLE. That's what you hire a programmer to do.

Skip,

_[red][/red]