Formula to copy rows based on on three different criteria

[MagicMan]

Hi Folks,

I've got a formula that identifies two statuses "Overdue" & "Not Started":

Please Login or Register  to view this content.

This produces a count of all those rows that where the status is "Overdue" then follows on by counting those rows where the status is "Not Started"

I'd really like to add a third state of "In Progress" to this formula. Ideally, this would start the "In Progress" immediately after the "Not Started" count.

Can anyone help? (I can produce a Example sheet if necessary, I'll just need to "sanitise" it a bit.

Thanks

[AliGW]

Yes, please - use the one you used in the earlier thread, which shuld save you time. Make sure you give some examples of the new requirement manually mocked up.

[AliGW]

Just a thought - do you mean this?

=IF(G3="Overdue",COUNTIF(G$3:G3,G3),IF(G3="Not Started",COUNTIF(G$3:G3,G3)+COUNTIF(G:G,"Overdue"),"In Progress"))

[MagicMan]

I've uploaded the sample workbook. (Serves me right for hoping I could get away with it )


As it stands, Cell H4 in the "NonCompletion Findings" Tab would be 16 and then would be added to the "NonCompletion List" Tab after the "Not Started" list.

[AliGW]

The workbook shows examples of formulae that already return "In Progress".

There is far too much data there for a useful sample file - it needs to be smaller so that we can find our way around and test our solutions easily.

So, where are we looking EXACTLY? Cell reference, please. What is WRONG with the current output, and what do you want to see INSTEAD?

There are NO helpful annotations in the sample sheet. You should not assume that we have followed ANY of your previous threads ...

[MagicMan]

Okay, I've reduced the sample size and revamped the Sample Sheet and Added in the "Ideal" that I'd like to see in the Key Tabs. (The Tab I've called Training Report (Source Data) is literally just where the other sheets data is originated from).

What is wrong with the current output is that in the "NonCompletion List" Tab, It will only list the Overdue and Not Started Statuses. I'd like it to also list the "In Progress" status (after the "Not Started").

The goal is to be able to produce a report that can be used to identify who hasn't completed mandatory training and what their completion status is.

I do appreciate your help and hope I've made it a bit clearer.

Thanks

[AliGW]

I am most likely being extremely thick today, but I really don't understand your numbering system. I will step aside - someone who does can assist. Sorry!

[MagicMan]

No worries, thanks for looking at it.

[MagicMan]

Basically, it counts and numbers the 'Overdue' first, then it counts and numbers the 'Not Started'.

This bit works fine 🙂

I'm trying to get it to follow on so it then counts and numbers the 'In progress' ones as well.

[JohnTopley]

Like Ali, I am confused by your logic: the list in "NonCompletion List" Tab only works if the data is sorted by Criteria..

Why not 3 simple counts based on the 3 criteria which you can summate in any combination you desire.

[AliGW]

Thanks for stepping in, John, and for making me feel a lot less 'thick'.

[MagicMan]

Hi Folks,

As I did such a shocking job of explaining myself yesterday (Genuinely, on reflection, it really was THAT BAD!) , I thought I'd have another go and, hopefully, be a bit clearer in my request.

In the Sample Workbook attached, data is entered in to the Source Data tab. There are 5 states in the Category Column (H:H) which are: Very Low, Low, Moderate, High and Critical.

I'm looking for a formula that will copy rows on to a different sheet (Destination Sheet?) where the category is either Critical, High or Moderate. I'd like the output to be in that order

Here's a preview of the Source Sheet
Attachment 767831

and here's a preview of what I'm hoping to achieve:
Attachment 767832

While I appreciate I could achieve this using VBA, unfortunately, where I'm planning to store it doesn't allow for *.xlsm files, so I'm having to rely on formula.

Any help would be appreciated and thank you in advance

P.S. I've put a little "Briefing" tab in the workbook in the hope it provdes some degree of clarity.

[AliGW]

New information copied here from the duplicate thread (now closed). Please continue HERE, as per the forum rules. Thank you.

[JohnTopley]

Try

in I4 of "Source Data"0Helper column)

=COUNTIF($H$4:$H4,$H4)+LOOKUP(H4,{"Critical","High","Low","Moderate","Very Low"},{100,1000,1000000,10000,1000000})

Copy down

In A4 of "Desired Outcome")

=IF(SMALL('Source Data'!$I$4:$I$1000,ROWS($1:1))>100000,"",INDEX('Source Data'!A$4:A$1000,MATCH(SMALL('Source Data'!$I$4:$I$1000,ROWS($1:1)),'Source Data'!$I$4:$I$1000,0)))

See "Result"

[MagicMan]

John,

That is absolutely spot on! - Thank you so much. (Added to Rep)

Just for my education, can I ask what the significance of the curly brackets in the formula are?

Please Login or Register  to view this content.

Is it something to do with 'hard coding' an array as opposed to specifying it from another source?

Thanks

Terry

[AliGW]

LOOKUP requires a lookup array (in alphabetical order):

{"Critical","High","Low","Moderate","Very Low"}

and a results array:

{100,1000,1000000,10000,1000000}

The curly brackets show Excel where these arrays start and finish in the formula:

LOOKUP(H4,{"Critical","High","Low","Moderate","Very Low"},{100,1000,1000000,10000,1000000})

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

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon 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 all those who offered help.

[MagicMan]

Thanks for the explanation AliGW

Rep already given to John and will mark it as [SOLVED]

Every day's a school day (and thanks to other people's efforts, I'm going to look good at work )

[Ochimus]

MagicMan,

1. Match Mutliple Categories:

Source Data worksheet on attached uses a Helper Column in Col I with this in I2 copied down:

=IF(OR('Source Data'!$H2="Critical",'Source Data'!$H2="High",'Source Data'!$H2="Moderate"),1,0)

So anything with a "1" matches your requirement.

On OUTCOME sheet formula in A2 extracts all the matching records across Cols A - H

=FILTER('Source Data'!$A$2:$H$24,'Source Data'!$I$2:$I$24=1,"No matches").

Note it does not pull the Helper col I across, just the data in Cols A - H.

2. Match selected Location:

Created list of unique locations in Col U, and Data Validated J2 with it.

Select Location from the DropList, and this formula in L2 brings over all the matching records into Cols L - S automatically.

=FILTER('Source Data'!$A$2:$H$24,'Source Data'!$B$2:$B$24=J2,"No matches")

Ochimus