List mutliple cells in separate worksheet

[chanpink]

Is there a way to display multiple cells in a separate worksheet that are a part of multiple worksheets? Please see below.

Worksheet 1 has list of email addresses in column A
Worksheet 2 has list of email addresses in column A

How to have Worksheet 3 display email addresses in column A that were on Worksheet 1 and Worksheet 2? Considering all duplicates are removed from each worksheet.

Worksheet 1 (column A)
red
blue
green
yellow

Worksheet 2 (column A)
black
red
blue
white
green
purple
pink

Need to have Worksheet 3 display as: (column A)
red
blue
green

 

[Rijnsent]

Hi chanpink,

what about: Copy the range from sheet 1 and paste it in (an empty) sheet 3. Next, copy the range from sheet 2 and paste it below the bit you just pasted. Use the "remove duplicates" option to get a list of unique values. Then you would only need to make a =COUNTIF(Sheet3!A2,Sheet1!A1:A10) formula to find the values in your list that are on sheet one. Do the same for sheet 2 and filter your list on both formulas being >0.

Hope that helps,

Koen