Help me squash this file

[gilbert]

I got colo's to work now here is a good sample

I have a 35k record sheet with house tax bills for my city. I have excel 07 and can't get the colo's html maker to work well so I have to try and type it. Sorry

Col a has the property's parcel id number it is referred to as pid. Each pid can appear up to 10 times on the sheet because we downloaded all unpaid tax bills for the last 10 years. each record no matter how many times it appears has the same info accross the sheet cols all the way to col v with info like situs address mailing address amount owed for that year etc.

I need a formula that will take each pid record if there is more than 1 and total all the taxes owed that appears in col s and give that result but only pull in the rest of the info for that record 1 time. making it one record but with the taxes totaled.

Since they show the amount owed on a per year basis if there is more than 1 year it makes multiple records. I do not need the years to appear. Just the number of years ie how many records there were that were combined and the total taxes owed.

10Yr_Unpds.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
90	13101312	R100	2007	INTELLIGENTPRINTMANAGEMENT	3912		LANGHORNE	AV		3912LANGHORNEAVENUE		CHARLOTTE	NC	28205	5618		168.74	2007		13101312
91	17102249	R100	2007	REBOLJODIINTERIORS	900		MONTFORD	DR		6526PENSFORDLANE		CHARLOTTE	NC	28270			33.37	2007		17102249
92	17321516	R100	2007	MOOSEVENDINGINC	7001		SHERBOURNE	DR		7001SHERBOURNEROAD		CHARLOTTE	NC	28210			776.63	2007		17321516
93	17323301	A500	2007	RESERVEAPARTMENTSTHE	1508		KELLEN	WY		1100STRYONSTREETSUITE205		CHARLOTTE	NC	28203	4291		1653.44	2007		17323301
94	17323301	A500	2007	RESERVEAPARTMENTSTHE	1508		KELLEN	WY		1100STRYONSTREETSUITE205		CHARLOTTE	NC	28203	4291		1653.44	2007		17323301
95	17323301	A500	2007	RESERVEAPARTMENTSTHE	1508		KELLEN	WY		1100STRYONSTREETSUITE205		CHARLOTTE	NC	28203	4291		1653.44	2007		17323301
96	17323301	A500	2007	RESERVEAPARTMENTSTHE	1508		KELLEN	WY		1100STRYONSTREETSUITE205		CHARLOTTE	NC	28203	4291		1653.44	2007		17323301
97	17323301	A500	2007	RESERVEAPARTMENTSTHE	1508		KELLEN	WY		1100STRYONSTREETSUITE205		CHARLOTTE	NC	28203	4291		1653.44	2007		17323301
98	17323301	A500	2007	RESERVEAPARTMENTSTHE	1508		KELLEN	WY		1100STRYONSTREETSUITE205		CHARLOTTE	NC	28203	4291		1653.44	2007		17323301
99	17323301	A500	2007	RESERVEAPARTMENTSTHE	1508		KELLEN	WY		1100STRYONSTREETSUITE205		CHARLOTTE	NC	28203	4291		1653.44	2007		17323301
Select1

 

[DonkeyOte]

Gilbert, you really need to use a Pivot Table for this with TAXPID as Row Field, Count of C (Year?) in DATA Field along with Sum of Total Column (you say S but looks blank).. assuming the other fields are common across years for same TAXPID then you can add any other fields as additional ROW fields on your PT.

HTH

Excel Workbook
	A	B	C	D	E	F	G	H	I
3								Data
4	TAXPID	b	c	f	g	k	l	Count of YEAR	Sum of TOTAL
5	13101312	R100	INTELLIGENT PRINT MANAGEMENT	LANGHORNE	AV	CHARLOTTE	NC	1	168.74
6	17102249	R100	REBOL JODI INTERIORS	MONTFORD	DR	CHARLOTTE	NC	1	33.37
7	17321516	R100	MOOSE VENDING INC	SHERBOURNE	DR	CHARLOTTE	NC	1	776.63
8	17323301	A500	RESERVE APARTMENTS THE	KELLEN	WY	CHARLOTTE	NC	7	11574.08
9	Grand Total							10	12552.82
Sheet2

 

[gilbert]

Hey thank you for replying. I am an idiot at excel. What you did is EXACTLY what I need but I don't understand how you did it. Could you give me more details on the method?

Thanks

Galen

 

[DonkeyOte]

Sorry for not replying sooner.

There are lots of guides on PT out there so I will give you a brief walkthrough based on your specific setup but would ask you research on line thereafter as I'm not a PT expert by any means and there are lots of articles by people who are out there not to mention some excellent demos on YouTube etc...

Firstly to create a PT each column in your data table must have a header.

I've used your sample as the basis for my PT and guessed at the headers given data within.

Some columns are showing with little width -- these are empty but they must still have something in the header row else the PT will not work.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	TAXPID	Column B	Year	Company	House No.	Column F	Street	Street Type	Column I	Address	Column K	City	State	Amount N	Amount O	Column P	O/S
2	13101312	R100	2007	INTELLIGENT PRINT MANAGEMENT	3912		LANGHORNE	AV		3912 LANGHORNE AVENUE		CHARLOTTE	NC	28205	5618		169
3	17102249	R100	2007	REBOL JODI INTERIORS	900		MONTFORD	DR		6526 PENSFORD LANE		CHARLOTTE	NC	28270			33.4
4	17321516	R100	2007	MOOSE VENDING INC	7001		SHERBOURNE	DR		7001 SHERBOURNE ROAD		CHARLOTTE	NC	28210			777
5	17323301	A500	2007	RESERVE APARTMENTS THE	1508		KELLEN	WY		1100 S TRYON STREET SUITE 205		CHARLOTTE	NC	28203	4291		1653
6	17323301	A500	2007	RESERVE APARTMENTS THE	1508		KELLEN	WY		1100 S TRYON STREET SUITE 205		CHARLOTTE	NC	28203	4291		1653
7	17323301	A500	2007	RESERVE APARTMENTS THE	1508		KELLEN	WY		1100 S TRYON STREET SUITE 205		CHARLOTTE	NC	28203	4291		1653
8	17323301	A500	2007	RESERVE APARTMENTS THE	1508		KELLEN	WY		1100 S TRYON STREET SUITE 205		CHARLOTTE	NC	28203	4291		1653
9	17323301	A500	2007	RESERVE APARTMENTS THE	1508		KELLEN	WY		1100 S TRYON STREET SUITE 205		CHARLOTTE	NC	28203	4291		1653
10	17323301	A500	2007	RESERVE APARTMENTS THE	1508		KELLEN	WY		1100 S TRYON STREET SUITE 205		CHARLOTTE	NC	28203	4291		1653
11	17323301	A500	2007	RESERVE APARTMENTS THE	1508		KELLEN	WY		1100 S TRYON STREET SUITE 205		CHARLOTTE	NC	28203	4291		1653
Sheet1

Once the headers are set up highlight your entire range of data, in this sample that's A1:Q11.

Once highlighted select Data -> Pivot Table and Pivot Chart Report...

Step 1: click Next (you want the defaults)
Step 2: click Next (your range is already selected)

Step 3: Here people do things differently... my approach:

Click Layout

Drag TAXPID from the right hand side into ROW field box.
Repeat for anything else you want to appear to the left of the count of year, sum of values -- eg Company, Address, City, State -- putting each one you add below the previous entry

Now Drag Year into DATA box, repeat for O/S.

Now by default you will probably find both YEAR & O/S are set to SUM (eg Sum of Year, Sum of O/S).
Obviously you don't want to SUM year you want to COUNT year so...
double click on "Sum of Year"
From the list provided select Count click OK.
You should now see in DATA box you have Count of Year, Sum of O/S

Click OK.

Back in Step3 dialog box now Click Finish.

You should now have your PT on a new sheet in your file.

Keep reading...

If you want to display Count of Year and Sum of O/S side by side as opposed to above and below, if you do simply select the grey cell with "Data" in it and drag it into the cell presently saying "Total"

Next... you have lots of subtotals showing... if you dont' want to show subtotals double click on the header of each field (eg Company) and in resulting box set Subtotals to "None" and click OK.
(You can also right click on a subtotal line in the PT itself and select Hide to do the same thing).

End result assuming you've been able to follow the above should look something like:

Excel Workbook
	A	B	C	D	E	F	G
1
2
3						Data
4	TAXPID	Company	Address	City	State	Count of Year	Sum of O/S
5	13101312	INTELLIGENT PRINT MANAGEMENT	3912 LANGHORNE AVENUE	CHARLOTTE	NC	1	168.74
6	17102249	REBOL JODI INTERIORS	6526 PENSFORD LANE	CHARLOTTE	NC	1	33.37
7	17321516	MOOSE VENDING INC	7001 SHERBOURNE ROAD	CHARLOTTE	NC	1	776.63
8	17323301	RESERVE APARTMENTS THE	1100 S TRYON STREET SUITE 205	CHARLOTTE	NC	7	11574.08
9	Grand Total					10	12552.82
Sheet2

HTH

 

[gilbert]

ok thank you i will work on it