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: 4

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

soumendas001

New Member
Joined
Apr 16, 2020
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
After remove duplicates, I need the result as attached image file.
 

Attachments

  • Result.jpg
    Result.jpg
    77.5 KB · Views: 6

Dave87

Board Regular
Joined
Apr 22, 2020
Messages
107
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,386
Messages
5,636,015
Members
416,892
Latest member
Bensch

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