Very large sheet I need to manipulate.

gilbert

Board Regular
Joined
Jun 13, 2002
Messages
141
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
ABCDEFGHIJKLMNOPQRSTUV
1TAXPIDLANDUSECODtxt_tax_bill_yeartxt_nme_taxpayernum_streetcde_street_dirtxt_nme_streetcde_street_typetxt_unittxt_addr_1txt_addr_2txt_nme_citytxt_cde_statecde_zip_1cde_zip_2nme_contact_personamt_amount_duetxt_mortgage_yeartxt_mortgage_co_numtxt_parcel
200101349R1202007PIERREJOSEPHB13711HAGERSFERRYRD13703HAGERSFERRYRDHUNTERSVILLENC28078577.88200700101349
Select1
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

gilbert

Board Regular
Joined
Jun 13, 2002
Messages
141
I got colo's to work a little better here are more records
10Yr_Unpds.xls
ABCDEFGHIJKLMNOPQRSTUV
1TAXPIDLANDUSECODtxt_tax_bill_yeartxt_nme_taxpayernum_streetcde_street_dirtxt_nme_streetcde_street_typetxt_unittxt_addr_1txt_addr_2txt_nme_citytxt_cde_statecde_zip_1cde_zip_2nme_contact_personamt_amount_duetxt_mortgage_yeartxt_mortgage_co_numtxt_parcel
200101349R1202007PIERREJOSEPHB13711HAGERSFERRYRD13703HAGERSFERRYRDHUNTERSVILLENC28078577.88200700101349
300101350R1202007PIERREJOSEPHB13719HAGERSFERRYRD13703HAGERSFERRYRDHUNTERSVILLENC28078574.27200700101350
400101453R1222007COXRUBYE15901BAYSHOREDRPOBOX780OAKWOODVA246317086.58200700101453
500101453R1222005COXRUBYE15901BAYSHOREDRPOBOX780OAKWOODVA246317415.46200500101453
Select1
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,912
Members
414,110
Latest member
docops

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