Extract duplicates and their adjacent values
Question: How do i only put duplicate cell values from a list into another new list and their adjacent values?
Answer: Here is the formula for the duplicates. (Column D)
=INDEX($A$2:$A$9, SMALL(IF(COUNTIF($A$2:$A$9, $A$2:$A$9)>1, ROW($A$2:$A$9)-MIN(ROW($A$2:$A$9))+1, ""), ROW(A1))) + Ctrl + Shift + Enter
Here is the formula for duplicates adjacent values (Column E):
=INDEX($B$2:$B$9, SMALL(IF(COUNTIF($A$2:$A$9, $A$2:$A$9)>1, ROW($A$2:$A$9)-MIN(ROW($A$2:$A$9))+1, ""), ROW(A1))) + Ctrl + Shift + Enter
Download excel sample file for this tutorial.
Extract duplicates and their adjacent values.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
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
SMALL() returns the k-th smallest row number in this data set.
COUNTIF(range;criteria)
Counts the number of cells within a range that meet the given condition
Related blog posts
- Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2
- Create a list of duplicates where adjacent cell value meets a condition in excel
- Extract negative values and adjacent cells in excel
- Highlight duplicates where an adjacent column is in a date interval using conditional formatting in excel
- Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
May 27th, 2010 at 3:10 pm
Hi, This doesnt seem to work when I am trying to use it. It only parses one cell not a full lise. I roughly have to look for duplicates in a list of 2916. The adjacent cells just have letters and numbers. I want to do the samething here you have. I want to take all my duplicates with the adjacent data and place them in a seperate column. (originally I just wanted to take one column of one spreadsheet and another column of the other and create a list in another excel sheet or not a column in the other but this seemed hard to fine). Any help would be appreciated.
May 30th, 2010 at 9:25 pm
chad king,
You are right! I have changed the array formula and uploaded a tutorial file.
Thanks for bringing this to my attention!