# common cell question

1. ## common cell question

Hello: would appreciate if someone can tell me how to do this.

1) i have an excel sheet with a column of elements such as: a b c d e f

2) I have a second excel sheet with a column of elements such as: a b c d e f g h

3) I would like to generate a 3rd excel sheet which should list the common elements from both the first and second excel sheets: example: should yield a column such as: g h

Thank You  Register To Reply

2. ## Re: common cell question

Hi. Welcome to the forum.

3) I would like to generate a 3rd excel sheet which should list the common elements from both the first and second excel sheets: example: should yield a column such as: g h
Wouldn't that be elements that are not common to both lists?

If that is the case try this in A1 of Sheet3 fill down until you get blanks.
Formula:  `Please Login or Register  to view this content.`  Register To Reply

3. ## Re: common cell question

Here's a workbook with that in it.  Register To Reply

4. ## Re: common cell question

sorry! i meant output should be common entries: a b c d e f

can you explain how you got the previous "not common result" and how do do the common entries?  Register To Reply

5. ## Re: common cell question

For the common entries in A1 of Sheet3
Formula:  `Please Login or Register  to view this content.`

can you explain how you got the previous "not common result" and how do do the common entries?
I'll do my best.

In the formula bar select the MATCH portion of the formula and hit the F9 function key.

=IFERROR(INDEX(Sheet2!\$A\$1:\$A\$8,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$8)-MIN(ROW(\$A\$1:\$A\$8))+1)/ISNA(MATCH(Sheet2!\$A\$1:\$A\$8,Sheet1!\$A\$1:\$A\$6,0)),ROWS(A\$1:A1))),"")

You will see an array of {1;2;3;4;5;6;#N/A;#N/A}

The position of the #N/As are the same as the non-matching g h.

Now select

=IFERROR(INDEX(Sheet2!\$A\$1:\$A\$8,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$8)-MIN(ROW(\$A\$1:\$A\$8))+1)/ISNA(MATCH(Sheet2!\$A\$1:\$A\$8,Sheet1!\$A\$1:\$A\$6,0)),ROWS(A\$1:A1))),"")

and hit the F9 key again. You will see this array.

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE}

When math operations are applied to TRUE/FALSE it coerces them into their underlying values 1/0. That happens when you divide the row numbers by those TRUE/FALSE.

Now select this portion of the formula and hit the F9.

(ROW(\$A\$1:\$A\$8)-MIN(ROW(\$A\$1:\$A\$8))+1)/ISNA(MATCH(Sheet2!\$A\$1:\$A\$8,Sheet1!\$A\$1:\$A\$6,0))

You will see this array

{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;7;8}

AGGREGATE has options for ignoring errors. That is what the 6 value is about. AGGREGATE is like the "Swiss Army Knife" of functions. It has 19 to choose from each designated by their function number. SMALL is one of them --- 15.

Small needs what is called a k_value to return the 1st, 2nd, 3rd ... etc smallest number from that array. ROWS(A\$1:A1) takes care of that. The remaining row numbers in the above array are then passed to INDEX(Sheet2!\$A\$1:\$A\$8, where g and h are. The IFERROR traps any remaining errors and returns "" where they occur.

Using the F9 function key in this manner can be very helpful for analyzing, trouble shooting and self instruction. Though useful examining steps out of context like this can be misleading until you get the hang of it.

Fortunately there is another tool that does the same as above. It is called 'Evaluate formula' (Fx for short). It is in the Formulas ribbon Auditing group. By clicking Evaluate repeatedly Excel shows step by step how the formula is calculated and does each step in context.

I recommend both these devices whenever I can.

The same steps explain the newest formula (common elements). Try these methods to see how you do.

Did this help?  Register To Reply