Filter a List Using Different Criteria

Please Help Me to Solve the Issue


excel issue.JPG

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet.

A sample sheet would help here, and possibly will enable a quicker and more accurate solution for you.


The forum does allow for spreadsheets to be uploaded direct to the forum -

Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Here are the instructions, found at the top of the page again

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

thanks for the PM , we only provide help and support via the forums and NOT offline

make the sheet smaller sample as mentioned
OR you could put onto a share and post a link - NOT idea - has the spreadsheet got sensitive data in , as this is a public forum open to anyone , even non members searching google

what you are showing in the image - should not be very BIG

Administrative Note:

Welcome to the forum.

Unfortunately, it has come to our attention you have violated Guideline #3 of our Forum Rules:

Don't private message, visitor message or email Excel (or Access, Word, etc.) questions to moderators or other members.

All questions and answers will benefit other posters like yourself when discussed in public threads. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.

Breaking this guideline is considered harassment by most of our contributors and thus cannot be tolerated. Repeat offense could lead to a permanent ban, so do take this caution to heart.

Post your question in a public thread and our many contributors will come to your assistance, especially if the title is accurate (see Guideline #1) and you include a sample desensitized workbook that makes it easy for others to try and help.

Make sure in your sample workbook to show your expected results.

Originally Posted by anasmobikom

My Dear Plz help me out I am unable to upload excel sheet Here....

There are instructions in post #2 and also in a yellow banner at the top of every page.

We allow .xlsx attachments up to 1MB. If your file is larger than that (based on the screenshot it's a small file) you can zip it. We accept zip files up to 9.77MB.

Also, please note that every post in this entire forum is about a problem in an Excel sheet. You will attract better help if your title tells us what you want to do.



Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to consider amending your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Guideline #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

Please update your thread title, as requested, to reflect the actual issue that you need help with.

You were saked to update the title of THIS thread with something SPECIFIC to the poroblem that you need help with, nit open a new thread with an equally uninfoirmative title.

Our guidelines have been explained to you above regarding thread titles. Members whould be able to see at a glance what the nature of the problem is.

Please Login or Register  to view this content.

Why not just have the summary table as there just duplication of data?

The SUMIFS formula requires the Department Name and if the result of this (SUMIFS) is 0, then the Department Name and SUMIFS formula need to be "deleted": not possible with formula.

An option is to do all calculations in a "temporary" worksheet (hidden if required) and then extract the non-zero reults to the actual worksheet.

The attached is an example: Sheet "TEAMWORK" is used to provide the input to "Day Total" BUT I have yet to generate the SUMMARY table (Columns B:F)

As per my last post

BUT I have yet to generate the SUMMARY table (Columns B:F)

See attached

And we are still waiting for you to update the thread title (see post #8). You have so far ignored this request.

Forum Rule #8:

Do not ignore questions and requests by Moderators and Administrators - these must be addressed and any amendments made before you proceed with your thread.

I have amended the thread title for you. Please take a look - this will be our expectation of you going forward.

In C6:

=SORT(UNIQUE(FILTER(Test!H2:I1694,(Test!C2:C1694=C2)*((Test!N2:N1694+Test!P2:P1694)>0))))

In E6 copied down:

=LET(s,SUMIFS(Test!$N$2:$N$1694,Test!$C$2:$C$1694,$C$2,Test!$H$2:$H$1694,C6,Test!$I$2:$I$1694,D6),
IF(s=0,"",s))

In F6 copied down:

=LET(s,SUMIFS(Test!$P$2:$P$1694,Test!$C$2:$C$1694,$C$2,Test!$H$2:$H$1694,C6,Test!$I$2:$I$1694,D6),
IF(s=0,"",s))

In H6:

=SORT(FILTER(B6:E22,((D6:D22="Diesel")+(D6:D22="Diesel to Petrol"))*(E6:E22<>0)),2)

In M6:

=SORT(FILTER(B6:E22,(D6:D22="Diesel")*(E6:E22<>0)),2)

In R6:

=SORT(FILTER(B6:E22,(D6:D22="Diesel to Petrol")*(E6:E22<>0)),2)

In W6:

=SORT(FILTER(B6:E22,(D6:D22="Petrol")*(E6:E22<>0)),2)

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

I have seen your sent excel sheet.
I need Type "D" to Sort it out coz end of the day we need
Diesel
Diesel to Petrol
Filter
Grease
M. Oil
Petrol
Service
STP

and department "C" also sorted alphabetically.
Please Check "N61" fro Reference

Thanks & Regards,
Zubair Akram

The entries in N61:O77 can be produced using C38:D54 as an intermediate step and employing two formulas.
For type: =SORT(D38:D54)
For department name: =INDEX(C$38:C$54,AGGREGATE(15,6,(ROW(C$38:C54)-ROW(C$37))/(D$38:D$54=D61),COUNTIFS(D$61:D61,D61)))
Note that the first formula will spill while the second will need to be copied down.
Let us know if you have any questions.