Solved Hide Both Sets of Duplicates

March 27, 2017 at 13:54:11
Specs: Windows 7
I'm trying to filter out all cells that do not match another cell with the same data. I compiled two lists of similar data but, I only need the cells which include data that is not on both lists. Now that I have compiled two lists, there are duplicates of most cells. I need to have visibility to the cells that do not have a match/duplicate.

message edited by KatieKOMP


See More: Hide Both Sets of Duplicates

Report •

#1
March 27, 2017 at 14:09:13
Sound like you need to Filter for duplicates, see if this is what you need:

https://support.office.com/en-us/ar...

MIKE

http://www.skeptic.com/


Report •

#2
March 27, 2017 at 16:31:49
✔ Best Answer
Let's say you are starting with this set of data:

        A         B             C               D
1   List 1	List 2	   In 1, Not 2     In 2, Not 1
2      A	  A
3      S	  B
4      C	  F		
5      D	  D		
6      E	  C		

Put this in array formula in C2 and use Ctrl-Shift-Enter (not just Enter).
Then drag it down as far as you need.

=IFERROR(INDEX($A$2:$A$6,MATCH(0,IFERROR(MATCH($A$2:$A$6,$B$2:$B$6,0),COUNTIF($C$1:$C1,$A$2:$A$6)),0)),"")

Put this in array formula in D2 and use Ctrl-Shift-Enter (not just Enter).
Then drag it down as far as you need.

=IFERROR(INDEX($B$2:$B$6,MATCH(0,IFERROR(MATCH($B$2:$B$6,$A$2:$A$6,0),COUNTIF($D$1:$D1,$B$2:$B$6)),0)),"")

You should end up with this:


        A         B             C               D
1   List 1	List 2	   In 1, Not 2     In 2, Not 1
2      A	  A	        S	        B  	       
3      S	  B	        E	        F	        
4      C	  F		
5      D	  D		
6      E	  C		

Credit is due here:

https://answers.microsoft.com/en-us...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
March 28, 2017 at 06:58:51
More thoughts...

You said:

I'm trying to filter out all cells that do not match another cell with the same data.

and

I need to have visibility to the cells that do not have a match/duplicate.

It's not clear to me what you want your output to be.

1 - The Array formula in my previous response creates 2 new lists, each of which contains the values from one list that do not appear in the other.

2 - If both lists are on the same sheet, but in different columns, it would be hard to hide the duplicates because the entire Row would be hidden. If one list was underneath the other or on a different sheet, then the rows with dups could be hidden fairly easily. This would leave the values that are not in both lists visible.

3 - The dups could set to a font color that makes then invisible, but you'd have blank gaps in your lists.

4 - If simply highlighting the non-dups is acceptable, then Conditional Formatting should work. Using the layout in my previous response, select A2:A6 and apply this CF rule, then pick a fill or font color of your choice:

=COUNTIF($B$2:$B$6,A2)=0

Next, select B2:B6 and apply this CF rule and pick a fill or font color of your choice:

=COUNTIF($A$2:$A$6,B2)=0

Any values that appear in only one of the lists will be highlighted.

If you could tell us exactly what you want your output to be, we may have other suggestions.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question