Duplicates for multiple columns

[Maddog31]

Hi,

I am working with parts lists for several variations of a product. I am trying to find a way of finding duplicates that appear in all three lists. So for my columns, I would have
Column 1: A, B, C, D, E
Column 2: A, C, D
Column 3: A, B, C

My output column should be A, C. Also, the same part numbers will not be on the same rows, so I need to make sure the formula can handle that. Any suggestions?

Thanks

[Cofad]

How do you want to search for duplicates? Enter a value in a cell and then search the columns? Or do you need to search for all of the duplicates of all the parts?

[Maddog31]

Being able to output parts that are common to all 3 lists and output them to a 4th would be ideal, but I can work with just highlighting all the duplicates on one of the parts list

[Tony Valko]

As long as there are no duplicates in any individual list. For example, this will not happen:

List1: A, A, B, C

Data Range

	A	B	C	D	E
1	List1	List2	List3	-----	Common
2	A	A	A		A
3	B	C	B		C
4	C	D	C
5	D
6	E

You want to compare the shortest list to the other lists. In this example List2 and List3 are the shortest lists so I chose to compare List3 to the other lists.

This array formula** entered in E2:

=IFERROR(INDEX(C$2:C$4,SMALL(IF(COUNTIF(A$2:A$6,C$2:C$4)+COUNTIF(B$2:B$4,C$2:C$4)=2,ROW(C$2:C$4)),ROWS(E$2:E2))-MIN(ROW(C$2:C$4))+1),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

[Maddog31]

Thanks, i'll give this a try and hopefully everything works out!

[Tony Valko]

Let us know how you make out.