Extract a list of duplicates from two columns combined

=IFERROR(LOOKUP(2, 1/((COUNTIF($F$2:F2, $B$3:$B$21)=0)*(COUNTIF($B$3:$B$21, $B$3:$B$21)>1)), $B$3:$B$21), LOOKUP(2, 1/((COUNTIF($F$2:F2, $D$3:$D$8)=0)*((COUNTIF($D$3:$D$8, $D$3:$D$8)>1)+(COUNTIF($B$3:$B$21, $D$3:$D$8)>0))), $D$3:$D$8))

Explaining formula in cell F3

This formula consists of two similar parts, one returns values from List1 and the other returns values from List2.

IFERROR(formula1, formula2)

Step 1 - Prevent duplicate values

The COUNTIF function counts values based on a condition, in this case, I am counting values in cells above. This makes sure that duplicates are ignored.

COUNTIF($F$2:F2,$B$3:$B$21)=0

becomes

COUNTIF("Duplicates",$B$3:$B$21)=0

becomes

COUNTIF("Duplicates",{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando ";"Gonzalez, Fernando ";"Wawrinka, Stanislas ";"Gonzalez, Fernando ";"Blake, James ";"Nalbandian, David ";"Robredo, Tommy ";"Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "})=0

{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}=0

and returns

{TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE}

Step 2 - Count values in List1

We want to know where the duplicates are if there are any.

COUNTIF($B$3:$B$21,$B$3:$B$21)>1

becomes

COUNTIF({"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando ";"Gonzalez, Fernando ";"Wawrinka, Stanislas ";"Gonzalez, Fernando ";"Blake, James ";"Nalbandian, David ";"Robredo, Tommy ";"Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "},{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando ";"Gonzalez, Fernando ";"Wawrinka, Stanislas ";"Gonzalez, Fernando ";"Blake, James ";"Nalbandian, David ";"Robredo, Tommy ";"Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "})>1

becomes

{2;1;1;2;1;1;2;2;1;2;2;2;1;1;1;2;1;1;1}>1

{TRUE;FALSE; FALSE;TRUE; FALSE;FALSE; TRUE;TRUE; FALSE;TRUE; TRUE;TRUE; FALSE;FALSE; FALSE;TRUE; FALSE;FALSE; FALSE}

Step 3 - Multiply arrays

(COUNTIF($F$2:F2,$B$3:$B$21)=0)*(COUNTIF($B$3:$B$21,$B$3:$B$21)>1)

becomes

{TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE} * {TRUE;FALSE; FALSE;TRUE; FALSE;FALSE; TRUE;TRUE; FALSE;TRUE; TRUE;TRUE; FALSE;FALSE; FALSE;TRUE; FALSE;FALSE; FALSE}

and returns

{1;0;0;1;0;0;1;1;0;1;1;1;0;0;0;1;0;0;0}




Advanced formulas Concatenate Based on a condition Unique distinct values Drop-down list Join without TEXTJOIN Lookup & return values Compare Criteria, two lists Sums, return unique Extract missing values Common values 3 lists Two price lists Records in two tables Common records Update recent values(2) Missing values two cols Update recent values Values shared by 2 rngs Vals not shared 2 rngs Values shared by 3 rngs Extract not shared vals Shared values 2 cols Missing values 2 cols Combine merge Merge tables Merge rows - condition Combine ranges [UDF] Merge matching rows Merge 2 cols w. blanks Consolidate sheets Combine sheets Merge two columns Merge 3 columns Count values Pattern in cell value Overlapping time Containing any list value Unique distinct/condition Count colored cells Equal values per row Count overlapping days Sort count freq. condi. Count overlap. days(2) Count overlap. days(1) Contiguous values Count students Count filtered table Groups of each value Weekday in date range Weekday date range(2) Cells between value Entries date & time rng Unique distinct - criteria Unique dist. rec. criteria Digits - no duplicates Based on criteria Count groups in calendar Uniq. dist. based dates Count based on date Numbers across sheets Cells between values Most freq/date range Unique distinct months Uniq. dist. 2 cols Unique dist. values Dates Schedule -> date rngs Overlapping time Count overlapping days Date from week & day Count overlap. days(2) Count overlap. days(1) Missing dates Overlap. date ranges Overlapping ranges Find overlap. ranges Min/max date overlap. Date ranges overlap Consecutive dates Count overlap days Prevent overlapping Find date range based on a date Create date ranges Weekday in date range Calc dates in project Weeks from ranges Weekday date range(2) Entries date & time rng Overlap. date ranges Overlap rngs - condition Count groups in calendar Dates from overlap rngs Dates from date rngs Date rngs from dates Dates not in rngs Overlapping ranges Based on date criteria Date, week, month Dupes - date, wk, month Year - month from dates Match date in date rng Latest date - condition Most freq/date range Find min/max date rng Blank between quarters Dupe vals sorted date Create date range Lookup the nearest date Based on a date range Machine utilization Date based on wkday Find numbers in sum Numbers closest to sum No net close to zero Find numbers in sum Numbers in sum Overlapping Overlapping time Count overlapping days Count overlap. days(2) Count overlap. days(1) Missing dates Overlap. date ranges Overlapping ranges Find overlap. ranges Min/max date overlap. Date ranges overlap Count overlap days Prevent overlapping Overlap. date ranges Overlap rngs - condition Overlapping ranges Unique values Case sensitive Unique strings [UDF] Sort digits in rng Based on date criteria From range Sort unique A-Z Min unique number Sort based on date Sum if unique Two columns One column Unique distinct values One column Two columns Three columns Remove blanks Uniq. dist. - condition Adjacent contains string Adjacent has text Contains string Based on criteria Sorted frequency Sorted by sum Sorted alphabetically Sorted A->Z Sorted & no blanks - rng Sort length From a cell range Sorted A-Z from range Contains string in rng Begins with criterion Based on a date range Based on dates Year - month from dates Based on date criteria Extract filtered table Extract & sum condition Two conditions Sum uniq. dist. rec. Assign randomly unique Sums, return unique Sorted, no blanks - rng Duplicate values Dupes A-Z condition Dupes w. exceptions Filter duplicate values using critera Common values 3 lists Label duplicate records Dupes - date, wk, month Dupes from rng contain Filter dupes begins w. Min unique number Dupe vals sorted date Sort dupes A-Z Dupes from range Dupes from 3 cols Dupes from 1 col Dupes from 2 cols Create a list of duplicates where adjacent cell value meets a condition Sort values Sort a column A-Z Sort from 2 cols A-Z Sort rng A-Z Sort column - frequency Frequency - criteria Sort rng - frequency Sort count freq. condi. Sort two columns Sort records Sort table Sort adj. txt horiz. (1) Sort monthly Sort table custom Lookup/Sort custom Sort random order Sort based on length Reverse list Sort dupes A-Z Sort unique A-Z Sort unique A-Z blanks Sort cell delimiter [VBA] Sort Excel table [VBA] Sort based on proximity Sort dates - criteria Vlookup function VLOOKUP function Discount percentages Multiple conditions Lookup values in rng Lookup value in rng Three columns Two datasets Vlookup and return multiple values 5 easy ways Multiple conditions Sorted from A to Z Sorted A-Z - condition Multiple conditions Vlookup range Vlookup rng - records Vlook col - return rng 1 result per search Horizontal vlookup Vlookup - Drop-down Multiple sheets Search and return multiple values Nested Search Multiple conditions Interactive formula Search for string Search records Wildcard vlookup Multiple strings Criteria - AND logic Strings in any 2 cols Strings in 2 cols each Strings in column Contains string - column Filter records Extract using criteria Extract using criteria(2) Extract using criteria(3) Recs betw. this & that Records betw. 2 dates Date range & condition Search text string Filter neg. values Containing digits Records Sort based on freq. Not shared records Sort table custom Records [AND logic] Records in two tables Unique records Common records Common records Duplicate columns Unique distinct records Extract unique distinct records from two data sets Sort based on freq. Filter unique distinct records using criteria Count uniq. dist. [2010] Unique dist. rec. criteria Records - condition(2) Records - condition(1) Filter unique distinct records Count unique distinct records Count unique distinct records Duplicate records Extract duplicate records Count records Weekly Blog EMAIL Get a FREE PDF book! Email Formula categories Count Dates Extract Index and Match Logic Lookups String manipulation Sum Time Vlookup Welcome! I am Oscar and here to help you out. Feel free to comment and ask Excel questions. Make sure you subscribe to my newsletter so you don't miss new blog articles.	Extract a list of duplicates from two columns combined Excel > Duplicate values > Extract a list of duplicates from two columns combined Share on Facebook. Share on Twitter. Share on LinkedIn Pin It! Author: Oscar Cronquist Article last updated on November 20, 2018 The following regular formula extracts duplicates from column B (List1) and column D (List2) combined, the result is shown in column F. Formula in cell F3: =IFERROR(LOOKUP(2, 1/((COUNTIF($F$2:F2, $B$3:$B$21)=0)(COUNTIF($B$3:$B$21, $B$3:$B$21)>1)), $B$3:$B$21), LOOKUP(2, 1/((COUNTIF($F$2:F2, $D$3:$D$8)=0)((COUNTIF($D$3:$D$8, $D$3:$D$8)>1)+(COUNTIF($B$3:$B$21, $D$3:$D$8)>0))), $D$3:$D$8)) Explaining formula in cell F3 This formula consists of two similar parts, one returns values from List1 and the other returns values from List2. IFERROR(formula1, formula2) Step 1 - Prevent duplicate values The COUNTIF function counts values based on a condition, in this case, I am counting values in cells above. This makes sure that duplicates are ignored. COUNTIF($F$2:F2,$B$3:$B$21)=0 becomes COUNTIF("Duplicates",$B$3:$B$21)=0 becomes COUNTIF("Duplicates",{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando ";"Gonzalez, Fernando ";"Wawrinka, Stanislas ";"Gonzalez, Fernando ";"Blake, James ";"Nalbandian, David ";"Robredo, Tommy ";"Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "})=0 {0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}=0 and returns {TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE} Step 2 - Count values in List1 We want to know where the duplicates are if there are any. COUNTIF($B$3:$B$21,$B$3:$B$21)>1 becomes COUNTIF({"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando ";"Gonzalez, Fernando ";"Wawrinka, Stanislas ";"Gonzalez, Fernando ";"Blake, James ";"Nalbandian, David ";"Robredo, Tommy ";"Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "},{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando ";"Gonzalez, Fernando ";"Wawrinka, Stanislas ";"Gonzalez, Fernando ";"Blake, James ";"Nalbandian, David ";"Robredo, Tommy ";"Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "})>1 becomes {2;1;1;2;1;1;2;2;1;2;2;2;1;1;1;2;1;1;1}>1 {TRUE;FALSE; FALSE;TRUE; FALSE;FALSE; TRUE;TRUE; FALSE;TRUE; TRUE;TRUE; FALSE;FALSE; FALSE;TRUE; FALSE;FALSE; FALSE} Step 3 - Multiply arrays (COUNTIF($F$2:F2,$B$3:$B$21)=0)(COUNTIF($B$3:$B$21,$B$3:$B$21)>1) becomes {TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE} {TRUE;FALSE; FALSE;TRUE; FALSE;FALSE; TRUE;TRUE; FALSE;TRUE; TRUE;TRUE; FALSE;FALSE; FALSE;TRUE; FALSE;FALSE; FALSE} and returns {1;0;0;1;0;0;1;1;0;1;1;1;0;0;0;1;0;0;0}

get-digital-help.com