+ 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
    3

    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
    Office 365 v 2403
    Posts
    13,396

    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
    Office 365 v 2403
    Posts
    13,396

    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
    3

    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
    Office 365 v 2403
    Posts
    13,396

    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)

Similar Threads

  1. Trying to find the most common wrong question.
    By taylorsm in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-11-2019, 11:52 AM
  2. Copy cell between sheets using a common cell
    By Filipe Marques in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-27-2018, 06:14 PM
  3. Return most common, 2nd common...within the data range
    By tantcu in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-07-2015, 06:06 PM
  4. Replies: 27
    Last Post: 02-22-2015, 04:29 AM
  5. Replies: 1
    Last Post: 09-26-2014, 04:47 PM
  6. Need help with sorting a column by most common to least common
    By mikenmike0001 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-06-2011, 04:28 PM
  7. Finding the most common (and 2nd, 3rd most common) text
    By sprite_green in forum Excel General
    Replies: 2
    Last Post: 11-23-2006, 11:56 AM

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