Creating Complex Report

I have a sheet with multiple columns that have the same drop down. I want a report that shows any row that contains either of 2 values from that drop down "< 2 week supply" or "No Supply" . I got that figured out, but now I want to only show that set of lines filtered by another column for "Division" .

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The benefit would be that one requires much less typing which in turn is less likely to break due to typos, and would be much easier to update if anything needs to be tweaked in the future.


    I did some testing for your other question...

    Using a COUNTIFS and directly referencing data that includes "<" such as "< 2 Week Supply" doesn't work. Odd, but...

    FIND and CONTAINS can both register the "<" just fine.

    So instead of

    =COUNTIFS({Range}, "< 2 Week Supply")

    try this

    =COUNTIFS({Range}, CONTAINS("< 2 Week Supply", @cell))


    That should get you your counts.

«1

Answers

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    Hi

    Within the Report Builder use the What button and select the column that contains the first 2 options that you require; use the criteria definer to place ticks against "< 2 week supply" and "No Supply" you will see that these are summarised with an or between them. Then Click on the What button again and this time select Division and use the criteria definer in there to set the division required. You should see the word AND in a grey bar between your two column criterias in the Report Builder. If you click RUN this should do as you require.

    Hope this makes sense and helps...

    Kind regards

    Debbie

  • Randy Van Winkle
    Randy Van Winkle ✭✭✭

    I can only seem to get all OR or all And. If I change the last item for Division to And, all the ones above change from OR to And

  • Randy Van Winkle
    Randy Van Winkle ✭✭✭

    There are 5 columns that I need to select "< 2 week supply" and "No Supply" prior to selecting Division

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    Hi

    Yes - I can understand that as it has happened to me lots, but before I answered to that effect I ran a quick test on a dummy sheet and was able to get a mix!

    If you can see in this screen shot it is doing what you wanted (to a degree!) Machine Center is a dropdown list column and Column5 is just text.

    If this isn't available to you, then I would suggest you set up a checkbox helper column with an IF function in it which will tick the box if the row meets the criteria needed for your report. Then your report just find all the records with a tick in the box.

    Will this approach work for you?

    Kind regards

    Debbie

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    @Randy Van Winkle

    Are you able to capture a screen shot with the columns and detail the criteria at all? Maybe we could help you with the Checkbox solution? You can nest many statements in IF function. Happy to help if I can...

    Debbie

  • Randy Van Winkle
    Randy Van Winkle ✭✭✭

    The problem arises when you add multiple columns of the Or function then try to add And for the "division". All the Or's change to And

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    Ahh Yes I see. There is no way around that I'm afraid. I think you really will need that helper column. Are you happy to write this IF function required or would you like me to have a go at it for you?

  • Randy Van Winkle
    Randy Van Winkle ✭✭✭

    I don't think a check box would resolve the issue. I have about 10 divisions to create reports for, all of the information is dynamic and I have about 50+ collaborators.

  • Randy Van Winkle
    Randy Van Winkle ✭✭✭

    So the IF function would combine the "< 2 week supply" and "No Supply"  information into a single column? I think that would work. What more info can I provide to help build that?

  • Randy Van Winkle
    Randy Van Winkle ✭✭✭

    Here are the column names. Check any row that shows any one of these at either value  "< 2 week supply" or "No Supply"



  • Randy Van Winkle
    Randy Van Winkle ✭✭✭

    All right, let me give it a shot! Thanks for your help. Will reply with result in the am.

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    Phew! I was holding on in there for you, but to be honest it is twenty past midnight here and my eyes are struggling to stay open!! I'll look for an update tomorrow :)

    Good luck :)

  • Randy Van Winkle
    Randy Van Winkle ✭✭✭

    Thanks for your help! I really appreciate it.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    This IF can actually be simplified GREATLY. Basically you want to check a box if any cell within the range (that row) contain either "< 2 Week Supply" or "No Supply".


    =IF(OR(CONTAINS("< 2 Week Supply", [Safety Glasses]@row:[Toilet Paper Supply]@row), CONTAINS("No Supply", [Safety Glasses]@row:[Toilet Paper Supply]@row)), 1)


    Just use a CONTAINS function to look across the row for "< 2 Week Supply" and another for "No Supply", drop them into an OR and have an output of 1 to check the box if either are true.