Question: How do I create a list of duplicates where adjacent cell value meets a condition? Only duplicates occurring the second time or more is to be listed.

Formula displaying item numbers (H2:H16):

=IF(ROW()-1>SUM(IF(\$F\$1=\$B\$2:\$B\$16, IF(MATCH(\$B\$2:\$B\$16&\$C\$2:\$C\$16, \$B\$2:\$B\$16&\$C\$2:\$C\$16, 0)<>ROW(\$B\$2:\$B\$16)-1, 1, ""), "")), "", SMALL(IF(\$F\$1=\$B\$2:\$B\$16, IF(MATCH(\$B\$2:\$B\$16&\$C\$2:\$C\$16, \$B\$2:\$B\$16&\$C\$2:\$C\$16, 0)<>ROW()-1, ROW()-1, ""), ""), ROW()-1)) + Ctrl + Shift + Enter

Formula displaying categories (I2:I16):

=IF(ROW()-1>SUM(IF(\$F\$1=\$B\$2:\$B\$16, IF(MATCH(\$B\$2:\$B\$16&\$C\$2:\$C\$16, \$B\$2:\$B\$16&\$C\$2:\$C\$16, 0)<>ROW(\$B\$2:\$B\$16)-1, 1, ""), "")), "", INDEX(\$B\$2:\$B\$16, \$H\$2:\$H\$16)) + Ctrl + Shift + Enter

Formula displaying values (J2:J16):

=IF(ROW()-1>SUM(IF(\$F\$1=\$B\$2:\$B\$16;IF(MATCH(\$B\$2:\$B\$16&\$C\$2:\$C\$16;\$B\$2:\$B\$16&\$C\$2:\$C\$16;0)<>ROW(\$B\$2:\$B\$16)-1;1;"");""));"";INDEX(\$C\$2:\$C\$16;\$H\$2:\$H\$16)) + Ctrl + Shift + Enter

(Excel 97-2003 Workbook *.xls)

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers

ROW(reference) returns the rownumber of a reference

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

SUM(number1,[number2],)
Adds all the numbers in a range of cells

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

### Related posts:

Create a list of distinct values from a list where an adjacent cell value meets a criteria in excel

Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel

Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2

Create unique distinct list from column where an adjacent column meets criteria

Create and sort distinct list by adjacent cell value size