Sort an excel sheet and remove duplicates?

soumendas001

New Member
Joined
Apr 16, 2020
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
Please find the attached excel sheet. There are many many duplicates. For example few properties are listed 10-15 times with billing information for each year listing. For those that have duplicates listed you will see them listed together. I need to sum those bills, and remove the duplicates so that resulted with One Address and Total due. I have tried by data consolidate. But unable to do so that there are billing year is differ. Please look into the matter and solve this.
 

Attachments

  • Testing Data.jpg
    Testing Data.jpg
    185.3 KB · Views: 5

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
After remove duplicates, I need the result as attached image file.
 

Attachments

  • Result.jpg
    Result.jpg
    77.5 KB · Views: 8
Upvote 0
Use a pivot table, put year into the "Filters" or into the "Rows" section depending on how you want to filter your information
Change report layout to Tabular (Click anywhere on the pivot table, then in the ribbon go to PivotTable Tools -> Design -> Report Layout)
Add Pin, Year, Quarter, Name, etc to Rows section etc
Add tax due, penalty due, interest due etc, to Values (you may need to change it to sum from Count)
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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