+ Reply to Thread
Results 1 to 17 of 17

Extract unique sub-list from repeated list based on criteria

  1. #1
    Registered User
    Join Date
    12-24-2019
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    2013
    Posts
    6

    Extract unique sub-list from repeated list based on criteria

    i have the below case
    How to have the sub list of project name avoiding repeating the project name (Proj1, Proj2 & Proj3) based on the remaining budget amount. If the remaining budget is zero (as Proj4), don't show that project at all
    Picture1.jpg
    Last edited by Mohamed Elgammal; 12-24-2019 at 06:41 AM.

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

    Re: Extract unique sub-list from repeated list based on criteria

    Hello Mohamed Elgammal. Welcome to the forum.

    For starters your data is inconsistent. Note the spellings of Porj, Proj. The inconsistency in case might cause issues ... but it seems unlikely. Basically you need to clean up the data first. Be sure to check for leading and trailing spaces. Also check the 'Item' column data for same.

    Better yet in addition upload an actual Excel workbook sample .... the one you sourced that screenshot from would be good. To do that just follow the instructions in the 'gold' banner at the top of this page.
    Dave

  3. #3
    Registered User
    Join Date
    12-24-2019
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    2013
    Posts
    6

    Re: Extract unique sub-list from repeated list based on criteria

    FlameRetired
    You are absolutely right, I tried to give simple example but I had typo mistake. The actual case is to much complicated but I simplified it as much as I could and included three worksheets (attached)
    - one for the data
    - one for requirement
    - one for the output I need
    Thanks
    Attached Files Attached Files

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,409

    Re: Extract unique sub-list from repeated list based on criteria

    Hi Mohamed,

    I think this is a normal Pivot Table problem where you filter by values not equal to zero. See the attached and answer is on the second sheet, below your request.

    Pivot Table Filter value of zero.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

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

    Re: Extract unique sub-list from repeated list based on criteria

    There are no records with any of these criteria.
    1- Owner (Column E) is either RF, FTD, CID, SBS or IBBS

  6. #6
    Registered User
    Join Date
    12-24-2019
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    2013
    Posts
    6

    Re: Extract unique sub-list from repeated list based on criteria

    MarvinP
    I am trying to do it without Pivot as I need to link it with Powerpoint and share it dinamically

  7. #7
    Registered User
    Join Date
    12-24-2019
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    2013
    Posts
    6

    Re: Extract unique sub-list from repeated list based on criteria

    FlameRetired
    Column E contains department named which I need (RF, FTD, CID, SBS, IBBS) and others which I don't need (Frozen, Non Design, Saving)
    I need to have a unique list of the "Short Project Name" for only Department I need and in the same time, those projects don't have their total "ND GAP1 Payable" as Zero
    I need to have a formula for that not Pivot

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,095

    Re: Extract unique sub-list from repeated list based on criteria

    This can be done with PowerQuery:

    Please Login or Register  to view this content.
    Have a look and see if it's what you want.
    Attached Files Attached Files
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  9. #9
    Registered User
    Join Date
    11-27-2019
    Location
    Abu Dhabi
    MS-Off Ver
    2016
    Posts
    6

    Re: Extract unique sub-list from repeated list based on criteria

    Post Deleted
    Last edited by pvkvimalan; 12-26-2019 at 04:20 AM.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,095

    Re: Extract unique sub-list from repeated list based on criteria

    Administrative Note:

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

  11. #11
    Registered User
    Join Date
    11-27-2019
    Location
    Abu Dhabi
    MS-Off Ver
    2016
    Posts
    6

    Re: Extract unique sub-list from repeated list based on criteria

    Dear AliGW,

    Thank you for your response. Sorry its a mistake.I shall remove my post and make a new post.

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Extract unique sub-list from repeated list based on criteria

    I am a bit confused about what you want in column A. In column B you have unique project names with a non-zero GAP1. Fine. However take a look at the first row of my solution - Disaster Recovery 2015 - Container. In owner there are three possible owners (see filtered list on data): FTD, RF and CID. Do you want one returned, or all 3, separately?

    Rather than a sample of nearly 2000 rows, 10-20 rows with some clearly set out expected answers would be MUCH more useful. Take a look at the file, I will explain formulaew once you make it clear what your expectations actually are!!
    Attached Files Attached Files
    Glenn



  13. #13
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    945

    Re: Extract unique sub-list from repeated list based on criteria

    Output

    A2=IFERROR(INDEX(Data!A$2:A$1793,SMALL(IF(FREQUENCY(IF(ISNUMBER(MATCH(Data!$A$2:$A$1793,$F$2:$F$6,0)),IF(ND_GAP1_Payable<>0,MATCH(Data!$A$2:$A$1793&Data!$B$2:$B$1793,Data!$A$2:$A$1793&Data!$B$2:$B$1793,0))),ROW(Data!A$2:A$1793)-ROW(Data!A$2)+1),ROW(Data!A$2:A$1793)-ROW(Data!A$2)+1),ROWS(Data!$A$2:Data!$A2))),"")

    Control+shift+enter

    copy across and down

    C2=IF($A2<>"",SUMIFS(ND_GAP1_Payable,Owner,Output!$A2,Project_Short_Name,Output!$B2),"")

    copy down
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    12-24-2019
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    2013
    Posts
    6

    Re: Extract unique sub-list from repeated list based on criteria

    Dear Glenn
    I tried to make it simple but I was requested by other members to provide the real case .. this is why I provide the real case .. sorry for big data.
    See .. each project (column A) has many POs (column C) and each PO has many Element code (column D) where the sum I need to use is in column H.
    I need to have a list of the projects under each owner showing the sum of column H for each project (the main issue to avoid any project which has its sum as ZERO)
    Sorry if it is a complicated request, but otherwise I could do it myself

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Extract unique sub-list from repeated list based on criteria

    Fine words, but. Please amend your sheet to SHOW me what you mean....

  16. #16
    Registered User
    Join Date
    12-24-2019
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    2013
    Posts
    6

    Re: Extract unique sub-list from repeated list based on criteria

    CARACALLA
    Thanks, That what I wanted
    Last edited by Mohamed Elgammal; 12-30-2019 at 11:20 AM.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,095

    Re: Extract unique sub-list from repeated list based on criteria

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. [SOLVED] Extract the Unique List based on SAP Codes and Header from two tabs
    By Neilesh Kumar in forum Excel General
    Replies: 6
    Last Post: 09-07-2018, 09:29 AM
  2. Extract Unique List Based on multiple criteria
    By Herbiec09 in forum Excel General
    Replies: 7
    Last Post: 01-14-2017, 07:23 AM
  3. [SOLVED] extract unique list from a range while other column is criteria
    By marinelkata in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-26-2016, 03:44 AM
  4. [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
  5. Need a unique list from repeated list
    By evanwm in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-11-2013, 07:42 PM
  6. [SOLVED] Extract unique values out of list based on an extra criteria
    By sven1975 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-11-2012, 05:02 AM
  7. [SOLVED] how to extract unique numbers once from a list of repeated numbers?
    By stuart.kwok@gmail.com in forum Excel General
    Replies: 2
    Last Post: 05-02-2006, 11:20 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