Your are viewing a read-only archive of the old DiS boards. Please hit the Community button above to engage with the DiS !
know literally everything about Microsoft Excel? And are they willing to help me?
You'd better have good biscuits though.
i know a tiny bit. what's the issue?
"i know a tiny bit"
probably the worst reply ever
about 8500 addresses. I want to filter out duplicates of the first line of the address so that I only have one address for each household, rather than one address for each resident.
So, I highlighted the column marked 'Address Line One', went to Advanced Filter and selected Unique Records Only.
It has counted 3480 unique records but won't filter out the duplicates for some reason.
Probably the easiest way to check for duplicates after that filtering is to then sort the spreadsheet by postcode - you can then do a quick visual check for duplicates that way.
have one person for each household on the list. the postcodes are duplicated multiple times unfortunately.
I've thought about doing it manually but, eventually, it's going to have to be done for about 20-25,000 people.
if you have x4 Jones at the same residence, you want it to cancel out three of them (randomly), just leaving one name at each home address?
Under action select 'copy to another location'
Click a box somewhere in the Excel sheet for it to duplicate the list
Check the 'unique records only' box.
It may duplicate the heading, but otherwise seems to work.
sort by one of the address fields, then use a formula to compare the fields which would give either true or false as a result, then filter on these results.
That usually solves most Excel based problems.
after you click on advanced filter it should have:
- "filter the list in place" bit checked
- list range is the column you're filtering on (manually select this again even if it was highlighted before you clicked advanced filter - excel is a bit of a bastard about this)
- criteria range blank (i think)
- unique records only box ticked
sorry if that's no help:P