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.

Answer: See picture below.

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

Download excel example file.
create-a-list-of-adjacent-duplicates.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

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:

1. Create a list of distinct values from a list where an adjacent cell value meets a criteria in excel
2. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2
3. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel
4. Create and sort distinct list by adjacent cell value size
5. Create unique distinct list from column where an adjacent column meets criteria
6. Unique list to be created from a column where an adjacent column has text cell values
7. Highlight duplicates where an adjacent column is in a date interval using conditional formatting in excel
8. Sum values in a range where adjacent cell value equals a criterion in excel
9. How to extract a list of duplicates of a column in excel
10. Filter unique distinct list sorted based on sum of adjacent values using array formula in excel