0

On the spreadsheet there are two subsets of data that are separated by color based on where I pulled the information from. Within these subsets are duplicate entries.

I do not want to simply remove duplicates as it is key to know the source of the data. As well as what I am trying to view are those without duplicate entries (unique values ONLY).

I am trying to build an advanced filter that will select all fields so the list range will be column/row A:1 to [whatever the last column/row] is for the xls.

I only want to use the first name and last name as the criteria to use to determine a unique value.

I can copy the data to separate location within the xls or filter in place.

The key here is after the filtering has taken place I only want unique values left.

In this instance the unique value would be those records that did not have a duplicate entry. Not only one instance of the duplicate record and those single entries. I only want the single entries to be left at the end of the filtering.

What I am finding is that it is not bringing over unique values only.

As an end result I should only have those fields that were unique (meaning no duplicate was found). Not just removing the duplicate.

  • What do “color” and “the source of the data” have to do with the rest of your question? And, if Excellll’s answer isn’t what you want, please try to explain your problem better. Showing example data (before and after) is often helpful. – Scott Feb 4 '14 at 22:30
  • The color and the source are one in the same. I need to know where the information came from, as one color (green) signifies that the data is already present in our database. The other (blue) is from an external resource. If those that remain as unique values are green then I will not have a need to import the data. If the those that remain as a unique value are blue then these are net new data points that will be brought into our database. The names must remain confidential so an example is hard to post. – DGrissom Feb 5 '14 at 14:20
1

Add a field to your data that checks for uniqueness of First and Last Name combinations. In my sample data, First Names are in column A, Last Names in B. Fill the following formula down the column in your new field.

=SUMPRODUCT((A2=$A$2:$A$10)*(B2=$B$2:$B$10))

Unique entries will return the value 1. Simply filter your table for records with the value 1 in the new field.

Sample:

Insert new field:
enter image description here

Apply filter:*
enter image description here
*I just noticed the changing numbers in OtherField2. I accidentally left the RANDBETWEEN formula that I used to generate the dummy data. No funny business. Everything works as it should.

  • That worked perfectly! Thank you, greatly appreciated. – DGrissom Feb 5 '14 at 14:40
  • No worries, it didn't affect the filtering so all is good. – DGrissom Feb 5 '14 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.