+ Reply to Thread
Results 1 to 7 of 7

Filter common criteria from 2 columns & filter large of filtered values from 3rd column

  1. #1
    Registered User
    Join Date
    04-02-2014
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    79

    Filter common criteria from 2 columns & filter large of filtered values from 3rd column

    Hi,

    My title may not explain my need in detail. please refer attachment.

    For ex: Filter "A" from header 2, we have 2 "IND", 1 "AUS" and 1 "NJU" in Header 3, then find out large of similar criteria of Header 3 in Header 4.

    we have 12 & 23 against IND, so pull 23 against IND and leave the other, we have only one AUS and one NJU so pull them as it is.

    Hope this is clear.

    Thanks
    Shree
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,488

    Re: Filter common criteria from 2 columns & filter large of filtered values from 3rd colum

    Hi,

    Have you considered either an Advanced Data Filter and specify a criteria range which holds your selection criteria. Or personally, my preferred method would be a Pivot Table.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Filter common criteria from 2 columns & filter large of filtered values from 3rd colum

    This applies to cell D26 of your attached worksheet. Paste and drag down, without filters applied:
    Please Login or Register  to view this content.
    CTRL+SHIFT+ENTER to verify.

  4. #4
    Registered User
    Join Date
    04-02-2014
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: Filter common criteria from 2 columns & filter large of filtered values from 3rd colum

    thanks mcmahobt, it works well.. you saved me a lot of time.
    Could you pls explain this formula, how it works.

    thanks
    Shreeja

  5. #5
    Registered User
    Join Date
    04-02-2014
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: Filter common criteria from 2 columns & filter large of filtered values from 3rd colum

    Thanks Richard for the reply & suggestion.

  6. #6
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Filter common criteria from 2 columns & filter large of filtered values from 3rd colum

    Quote Originally Posted by shreeja178 View Post
    thanks mcmahobt, it works well.. you saved me a lot of time.
    Could you pls explain this formula, how it works.

    thanks
    Shreeja
    It first checks if cell B26 is equal to any cell within B3:B22, as well as if C26 is equal to any cell within C3:C22. If it is, is takes the corresponding cell location from D3:D22, but only if it is the MAX value out of the solutions provided.

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Filter common criteria from 2 columns & filter large of filtered values from 3rd colum

    Use this formula for correct results

    =MAX(IF(B26&C26=$B$3:$B$22&$C$3:$C$22,$D$3:$D$22))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    Please see compared results from two formula

    Row\Col
    D
    E
    F
    25
    HEADER 4 mcmahobt AlKey
    26
    23
    23
    23
    27
    32
    45
    32
    28
    32
    87
    32
    29
    45
    78
    45
    30
    98
    23
    98
    31
    98
    65
    98
    32
    56
    98
    56
    33
    78
    45
    78
    34
    45
    0
    45
    35
    65
    48
    65
    36
    10
    56
    10
    37
    48
    56
    48
    38
    23
    98
    23
    39
    65
    78
    65
    40
    45
    78
    45
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

+ 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. Filter data, create a new workbook from filtered criteria
    By reef in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-18-2013, 08:41 PM
  2. Replies: 4
    Last Post: 11-19-2012, 09:16 AM
  3. Replies: 3
    Last Post: 03-15-2012, 10:01 PM
  4. Filter large database using Advanced Filter
    By thegrimmster in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-10-2011, 11:06 AM
  5. Filter for unique values on an already auto-filtered list?
    By dylanemcgregor in forum Excel General
    Replies: 1
    Last Post: 01-11-2010, 07:25 PM

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