+ Reply to Thread
Results 1 to 5 of 5

common cell question

  1. #1
    Registered User
    Join Date
    05-02-2019
    Location
    seattle
    MS-Off Ver
    2000
    Posts
    2

    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

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365 Insiders
    Posts
    11,302

    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: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365 Insiders
    Posts
    11,302

    Re: common cell question

    Here's a workbook with that in it.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-02-2019
    Location
    seattle
    MS-Off Ver
    2000
    Posts
    2

    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?

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365 Insiders
    Posts
    11,302

    Re: common cell question

    For the common entries in A1 of Sheet3
    Formula: copy to clipboard
    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?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1