LinkBack URL
About LinkBacks
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
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.
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
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
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.
There are no records with any of these criteria.
1- Owner (Column E) is either RF, FTD, CID, SBS or IBBS
MarvinP
I am trying to do it without Pivot as I need to link it with Powerpoint and share it dinamically
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
This can be done with PowerQuery:
Have a look and see if it's what you want.
Please Login or Register to view this content.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Post Deleted
Last edited by pvkvimalan; 12-26-2019 at 04:20 AM.
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
Dear AliGW,
Thank you for your response. Sorry its a mistake.I shall remove my post and make a new post.
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!!
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
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
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
Fine words, but. Please amend your sheet to SHOW me what you mean....
CARACALLA
Thanks, That what I wanted
Last edited by Mohamed Elgammal; 12-30-2019 at 11:20 AM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks