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

#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...MIKEhttp://www.skeptic.com/

Report •

#2
March 27, 2017 at 16:31:49
 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...