Help me squash this file

gilbert

Board Regular
Joined
Jun 13, 2002
Messages
141
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
ABCDEFGHIJKLMNOPQRST
9013101312R1002007INTELLIGENTPRINTMANAGEMENT3912LANGHORNEAV3912LANGHORNEAVENUECHARLOTTENC282055618168.74200713101312
9117102249R1002007REBOLJODIINTERIORS900MONTFORDDR6526PENSFORDLANECHARLOTTENC2827033.37200717102249
9217321516R1002007MOOSEVENDINGINC7001SHERBOURNEDR7001SHERBOURNEROADCHARLOTTENC28210776.63200717321516
9317323301A5002007RESERVEAPARTMENTSTHE1508KELLENWY1100STRYONSTREETSUITE205CHARLOTTENC2820342911653.44200717323301
9417323301A5002007RESERVEAPARTMENTSTHE1508KELLENWY1100STRYONSTREETSUITE205CHARLOTTENC2820342911653.44200717323301
9517323301A5002007RESERVEAPARTMENTSTHE1508KELLENWY1100STRYONSTREETSUITE205CHARLOTTENC2820342911653.44200717323301
9617323301A5002007RESERVEAPARTMENTSTHE1508KELLENWY1100STRYONSTREETSUITE205CHARLOTTENC2820342911653.44200717323301
9717323301A5002007RESERVEAPARTMENTSTHE1508KELLENWY1100STRYONSTREETSUITE205CHARLOTTENC2820342911653.44200717323301
9817323301A5002007RESERVEAPARTMENTSTHE1508KELLENWY1100STRYONSTREETSUITE205CHARLOTTENC2820342911653.44200717323301
9917323301A5002007RESERVEAPARTMENTSTHE1508KELLENWY1100STRYONSTREETSUITE205CHARLOTTENC2820342911653.44200717323301
Select1
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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
ABCDEFGHI
3Data
4TAXPIDbcfgklCount of YEARSum of TOTAL
513101312R100INTELLIGENT PRINT MANAGEMENTLANGHORNEAVCHARLOTTENC1168.74
617102249R100REBOL JODI INTERIORSMONTFORDDRCHARLOTTENC133.37
717321516R100MOOSE VENDING INCSHERBOURNEDRCHARLOTTENC1776.63
817323301A500RESERVE APARTMENTS THEKELLENWYCHARLOTTENC711574.08
9Grand Total1012552.82
Sheet2
 
Last edited:

gilbert

Board Regular
Joined
Jun 13, 2002
Messages
141
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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
ABCDEFGHIJKLMNOPQ
1TAXPIDColumn BYearCompanyHouse No.Column FStreetStreet TypeColumn IAddressColumn KCityStateAmount NAmount OColumn PO/S
213101312R1002007INTELLIGENT PRINT MANAGEMENT3912LANGHORNEAV3912 LANGHORNE AVENUECHARLOTTENC282055618169
317102249R1002007REBOL JODI INTERIORS900MONTFORDDR6526 PENSFORD LANECHARLOTTENC2827033.4
417321516R1002007MOOSE VENDING INC7001SHERBOURNEDR7001 SHERBOURNE ROADCHARLOTTENC28210777
517323301A5002007RESERVE APARTMENTS THE1508KELLENWY1100 S TRYON STREET SUITE 205CHARLOTTENC2820342911653
617323301A5002007RESERVE APARTMENTS THE1508KELLENWY1100 S TRYON STREET SUITE 205CHARLOTTENC2820342911653
717323301A5002007RESERVE APARTMENTS THE1508KELLENWY1100 S TRYON STREET SUITE 205CHARLOTTENC2820342911653
817323301A5002007RESERVE APARTMENTS THE1508KELLENWY1100 S TRYON STREET SUITE 205CHARLOTTENC2820342911653
917323301A5002007RESERVE APARTMENTS THE1508KELLENWY1100 S TRYON STREET SUITE 205CHARLOTTENC2820342911653
1017323301A5002007RESERVE APARTMENTS THE1508KELLENWY1100 S TRYON STREET SUITE 205CHARLOTTENC2820342911653
1117323301A5002007RESERVE APARTMENTS THE1508KELLENWY1100 S TRYON STREET SUITE 205CHARLOTTENC2820342911653
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
ABCDEFG
1
2
3Data
4TAXPIDCompanyAddressCityStateCount of YearSum of O/S
513101312INTELLIGENT PRINT MANAGEMENT3912 LANGHORNE AVENUECHARLOTTENC1168.74
617102249REBOL JODI INTERIORS6526 PENSFORD LANECHARLOTTENC133.37
717321516MOOSE VENDING INC7001 SHERBOURNE ROADCHARLOTTENC1776.63
817323301RESERVE APARTMENTS THE1100 S TRYON STREET SUITE 205CHARLOTTENC711574.08
9Grand Total1012552.82
Sheet2


HTH
 

Watch MrExcel Video

Forum statistics

Threads
1,122,190
Messages
5,594,761
Members
413,931
Latest member
acrato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top