Sorting but keeping groups of rows together

Hello,

I am thinking of a strategy where I can sort a worksheet but keep groups of rows together. In other words how do you sort groups of rows?

Think of, for example an address book, where you have the Name, the street # and street name, City and Country, and Postal Code all in contiguous rows.

You want to find the duplicates in that address book, but if you sort the worksheet in the regular way, then all the addresses and cities and countries and names will mixed together and you could find the duplicate names, or duplicate cities and simply removing all these duplicates will not clean up the address book, because you still need to look at the whole entry to make the determination that it is, or is not, a duplicate.

For example, if you have two entries for two people who work at the same company, they will have a different name, but their addresses will be the same, so they are not truly a duplicate.

I tried making a helper column where I placed numbers, so that each entry had the same number, as to keep the group of rows together, but that failed miserably when you have 10,000+ rows.

I tried using VLOOKUP from one list to the other, and that also failed because I had to eliminate the lookup entry which means I must do the data cleaning first!

Any ideas are welcome!

Thanks in advance

Phillip

Hi, PhillipSh. Welcome to the Forum.

Could you attach your workbook (without sensitive data) ?
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
(Don’t use the paper clip icon to attach your file, it doesn’t work.)

Thanks jeffreybrown and Akuini
Here is a sample workbook with a single worksheet. I have highlighted the duplicates using colored fonts, so if you apply a filter then you will see that there are duplicate entries and ONLY the black font cells need to stay. All others represent a duplicate entry.

A duplicate is defined as one that has all the fields identical.

Imagine if you had a sales team made out of 1000s of sales people. If they go to conferences and exhibitions and collect business cards from contacts, then potentially (and in real life) you may have 2 or more sales people gathering the contact information for the same single person unknowns to each other that one sales person had already received that person's business card. If they all enter the information, then you will have duplicates.

This is a simplified version of the problem, and I am sure a simple meeting of the minds would eliminate this problem all together, and with better systems to check for duplicate at data entry time will prevent this problem, but when you don't yet have these systems in place you need to create them and put them in place and hope for the best. This is my unfortunately, or fortunately, job.

Thanks again,

Phillip

A duplicate is defined as one that has all the fields identical.

So if only some fields are identical then it doesn't count as duplicate, right?
But your example doesn't show any group that has some fields that are identical, it's all different or all identical.
So let's say we change B44 value to "X" then that group (B42:B49) doesn't count as duplicate, doest it?

Just an idea:
Transform your data into 8 columns, so you will have the header from Name to Time Zone.
Then you can easily sort the data by column Name or any column.
Delete all duplicate (maybe using Remove Duplicates)
Transform it back into 2 column data ... or not, I mean it's better if your data use 8 columns lay out, doesn't it?

Another thing, do you have something like this in your data?
270 Park Avenue New York
270 Park Avenue, New York
270 Park Avenue NY

I mean those 3 addresses are different but should be counted as identical, right? But it will be hard to write formula or code to make them counted as identical. So manual editing will be needed.