+ Reply to Thread
Results 1 to 13 of 13

Output unique list from table with certain criteria - formula

  1. #1
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,529

    Output unique list from table with certain criteria - formula

    Hi Guys,

    in attachment please find example workbook.
    I have table source and using Search function to lookup for all SystemNames for 2 servers - "Cloud Server" and "B777" (cell A2 in sheet "With one Value").
    You can see there is a function.

    This is ok but i have to add one more condition field here - "Topo2" - because i want to get only SystemNames for these 2 servers and "Topo2".
    When i am adding into Cell "A2" "Topo2" i have error so search function within my whole function is not working properly.

    How to solve this?

    Please help,
    Best,
    Jacek
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,861

    Re: Output unique list from table with certain criteria - formula

    Please try at C3 and press Ctrl+Shift+Enter
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,529

    Re: Output unique list from table with certain criteria - formula

    Hi Bo_Ry,

    wow your formula is enormous!

    i pasted your code into Sheet("With one Value") and i have not expected result:

    Screenshot_28.png

    Best,
    Jacek

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,861

    Re: Output unique list from table with certain criteria - formula

    Sorry not C3 you need to put that on A3 or
    for C3 You need to change COUNTIF(A$2:A2 to COUNTIF(C$2:C2

  5. #5
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,529

    Re: Output unique list from table with certain criteria - formula

    hmm thank you!

    i hope that others will gigure out also something because your formula is very very big...

    Best,
    Jacek

  6. #6
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,529

    Re: Output unique list from table with certain criteria - formula

    Hi,

    no shorter way to do this?

    Best,
    Jacek

  7. #7
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,529

    Re: Output unique list from table with certain criteria - formula

    Hi,

    please help,
    Jacek

  8. #8
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,529

    Re: Output unique list from table with certain criteria - formula

    Hmm, this is not working at all.

    I changed lookup value to:

    "Cloud ServerB555" and i should get only Windows 7 and Windows 11 and Windows XP but i have additionally Linux which is characteristic for B777, not for B555...

    Please help,
    Best,
    Jacek
    Attached Files Attached Files

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,010

    Re: Output unique list from table with certain criteria - formula

    Using your original attachment:

    =IFERROR(INDEX(TableSource!D:D,AGGREGATE(15,6,ROW(t_OperatingSystems[SystemName])/((ISNUMBER(SEARCH(t_OperatingSystems[Server],A$2)))*(t_OperatingSystems[TopologyName]=A$1)*(COUNTIF(A$2:A2,t_OperatingSystems[SystemName])=0)),1)),"")
    Attached Files Attached Files
    Glenn



  10. #10
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,529

    Re: Output unique list from table with certain criteria - formula

    hi Glenn,

    thank you very much you are master!!!

    one question:

    Please Login or Register  to view this content.
    what does this "/" sign mean here?

    It will take 6 here but why?

    "*" means AND in formula.

    Best,
    Jacek

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,010

    Re: Output unique list from table with certain criteria - formula

    The foremula is defined to return row numbers... IF they meet the criteria. The / signifies the start of the conditions.

    the 6 in aggregate means ignore errors. And yes, the * is and... (Criterion 1*Criterion2*Criterion3)

  12. #12
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,529

    Re: Output unique list from table with certain criteria - formula

    thank you Glenn,

    it is possible to instead of "TableSource!D:D" input structure table reference here?
    It is imporant for my model...

    Best,
    Jacek

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,010

    Re: Output unique list from table with certain criteria - formula

    At the expense of brevity... yes.
    Attached Files Attached Files

+ 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. Replies: 6
    Last Post: 09-09-2018, 07:43 AM
  2. [SOLVED] Formula to create a unique supplier list based on criteria
    By The_Snook in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-31-2017, 12:02 PM
  3. [SOLVED] Extract unique list from table that complies to multiple criteria
    By Henk Stander in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-17-2016, 05:41 AM
  4. [SOLVED] Formula to get unique and alphabetically sorted list after matching a criteria
    By Saranya A in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-20-2016, 06:50 AM
  5. Formula to list unique names that match criteria.
    By jxpresto in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-09-2015, 05:44 PM
  6. [SOLVED] Formula (array?) to list unique entries that match criteria?
    By Rerock in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 11-11-2014, 04:23 PM
  7. Unique list Array Formula dependent of two criteria
    By ron2k_1 in forum Excel General
    Replies: 3
    Last Post: 09-01-2011, 10:50 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