Create Unique List, Based off Multiple Criteria, Via Array?

[DavidGM]

Hi,

I've been lurking for a long time and have usually found a solution, but this is my first time posting. I'm basically trying to turn a data-table that lists where there are staffing gaps on a hospital's ward, in a list of where those gaps are.

So the data table looks something like that (going up to 30 wards on the X axis; identified through the number of staff they should have minus the number of staff they do), call this 'Sheet 1':


[TABLE="width: 500"]
<tbody>[TR]
[TD]Role
[/TD]
[TD]Shift
[/TD]
[TD]Ward 1
[/TD]
[TD]Ward 2
[/TD]
[TD]Ward 3
[/TD]
[/TR]
[TR]
[TD]RN Gap
[/TD]
[TD]Late
[/TD]
[TD]-1
[/TD]
[TD][/TD]
[TD]-1
[/TD]
[/TR]
[TR]
[TD]AP Gap
[/TD]
[TD]Late
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HCA Gap
[/TD]
[TD]Late
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RN Gap
[/TD]
[TD]Night
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-1
[/TD]
[/TR]
[TR]
[TD]Twilight RN Gap
[/TD]
[TD]Twilight
[/TD]
[TD]-1
[/TD]
[TD][/TD]
[TD]-1
[/TD]
[/TR]
[TR]
[TD]AP Gap
[/TD]
[TD]Night
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HCA Gap
[/TD]
[TD]Night
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-1
[/TD]
[/TR]
[TR]
[TD]Twilight HCA Gap
[/TD]
[TD]Twilight
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RN Gap
[/TD]
[TD]Early
[/TD]
[TD]-1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AP Gap
[/TD]
[TD]Early
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HCA Gap
[/TD]
[TD]Early
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


And I want to convert this into a table like this, call this 'Sheet 2':


[TABLE="width: 500"]
<tbody>[TR]
[TD]Shift
[/TD]
[TD]Role
[/TD]
[TD]Ward
[/TD]
[TD]Agency
[/TD]
[TD]Name
[/TD]
[/TR]
[TR]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[/TR]
</tbody>[/TABLE]



Which I would basically want to populate to say this (below), where it would create a list in columns A-C, and then a nurse would then manually input the agency and name of whomever they've assigned to fill that gap and this would then show that gap in a separate dashboard. 'Sheet 2' populated, i.e. what I want it to look like:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Shift
[/TD]
[TD]Role
[/TD]
[TD]Ward
[/TD]
[TD]Agency
[/TD]
[TD]Name
[/TD]
[/TR]
[TR]
[TD]Late
[/TD]
[TD]RN
[/TD]
[TD]Ward 1
[/TD]
[TD][manual]
[/TD]
[TD][manual]
[/TD]
[/TR]
[TR]
[TD]Twilight
[/TD]
[TD]RN
[/TD]
[TD]Ward 1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Early
[/TD]
[TD]RN
[/TD]
[TD]Ward 1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Late
[/TD]
[TD]RN
[/TD]
[TD]Ward 2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc.
[/TD]
[TD]etc.
[/TD]
[TD]etc.
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Ultimately this creates a log of where there are gaps, and the nurses can input names and agencies against this, creating a log of where there are gaps and who's filling them. I then use an IF statement to use this to populate a separate dashboard (but I can handle this part, as it's comparatively simple).

I can't figure out how/if to convert the data from 'Sheet 1' into the table in 'Sheet 2', if anyone can help, please?

 

[Eric W]

Welcome to the board!

A few questions:

The Sheet2 list isn't a sample created from Sheet1, right? Because there are no Ward 2 gaps?

The Role column on Sheet1 looks a bit disorganized. I assume we can just remove the "Gap" from the description, but some of them have the shift included too?

Is it possible to have a -2 or a -3? If so, do you want 1 line item, and the nurse can input multiple agencies/names to the right, or do you want multiple lines?

How many rows do you expect Sheet1 to have?

And finally, do you want formulas, or are you open to a macro? I can probably figure out formulas, but they'd be pretty complicated. This kind of thing is usually better suited to a macro. You can set it up to run from a button or hot key.

 

[DavidGM]

Hi Eric, thank you!

In response to your questions:

Yes, sorry, Sheet 2 was an example based off sheet 1. So the real example would look as below, based off the example sheet 1 above:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Role
[/TD]
[TD]Shift
[/TD]
[TD]Ward
[/TD]
[TD]Agency
[/TD]
[TD]Name
[/TD]
[/TR]
[TR]
[TD]RN
[/TD]
[TD]Late
[/TD]
[TD]Ward 1
[/TD]
[TD][manual]
[/TD]
[TD][manual]
[/TD]
[/TR]
[TR]
[TD]RN
[/TD]
[TD]Twilight
[/TD]
[TD]Ward 1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RN
[/TD]
[TD]Early
[/TD]
[TD]Ward 1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RN
[/TD]
[TD]Late
[/TD]
[TD]Ward 3
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RN
[/TD]
[TD]Night
[/TD]
[TD]Ward 3
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RN
[/TD]
[TD]Twilight
[/TD]
[TD]Ward 3
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HCA
[/TD]
[TD]Night
[/TD]
[TD]Ward 3
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Happy to re-organise sheet 1 as desired, if it helps you. Yes, "gap" can be removed from the description. Ultimately it doesn't need to be included in the description, so long as we are able to identify "twilight" staff. Basically twilight staff staff the night-shift, but only half of the night shift.

Yes, -3 would probably be the realistic maximum, but there's a risk in exceptional circumstances it could hit -4. If -4 is substantially more work from the perspective of crafting a macro, then happy to work to -3; but otherwise if it could accommodate for potentially 4 gaps that would be ideal.
It would need to be 1 line per gap, however. Apologies, I don't think I made that clear in the original post (as was aiming for formula)! So if Ward 1 noted -3 on the late RN shift then 'sheet 2' would need to say (on top of any ward 2 and 3 etc. gaps):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Role
[/TD]
[TD]Shift
[/TD]
[TD]Ward
[/TD]
[TD]Agency
[/TD]
[TD]Name
[/TD]
[/TR]
[TR]
[TD]RN
[/TD]
[TD]Late
[/TD]
[TD]Ward 1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RN
[/TD]
[TD]Late
[/TD]
[TD]Ward 1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RN
[/TD]
[TD]Late
[/TD]
[TD]Ward 1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The rationale for this is that some of the gaps would 'un-plugged' and remain as gaps. The purpose of sheet 2 would be to A) provide a handover sheet that notes the gaps and what we have done to mitigate them (i.e. adding names for the staff we have put in those gaps), and B) to run a countifs using the role, shift and ward for any gaps that have name to them (therefore giving a 'true' picture of staffing after we've 'plugged' the gaps; i.e. adding the countif result to sheet 1 to remove some of the minuses).


Sheet 1 should only ever have the 11 rows noted: RN's for each shift (3), HCA's for each shift (3), AP's for each shift (3), and the 2 twilight roles (2). Sheet 1 would have 33 columns, however, as we have 33 wards. Sheet 2 however could have up to 100 rows (up to 100 gaps!).

Macro or formula would be fine. The worksheet utilising it is already macro-enabled, so it won't complicate things from that perspective.

Any help you could provide would be great! At the moment the nurses are having to manually enter the gaps and names etc., so it could save them time doing this and creating the handover sheet, and therefore be free of potential human error.

 

[Eric W]

Pretty tough for formulas, pretty easy for VBA. Since you already have macros in your workbook, I'll assume you have some idea how to use the editor. If not, let me know. Put the following code in a general module:

Sub MakeTable()
Dim MyResults(1 To 200, 1 To 5), MyData As Variant, i As Long, j As Long, c As Long, r As Long
Dim InData As Range, OutSh As Worksheet

    Set InData = Sheets("Sheet1").Range("A1:AI12")
    Set OutSh = Sheets("Sheet2")
    
    OutSh.Cells.ClearContents
    OutSh.Range("A1:E1").Value = Array("Role", "Shift", "Ward", "Agency", "Name")
    MyData = InData.Value
    r = 0
    
    For c = 3 To UBound(MyData, 2)
        For i = 2 To UBound(MyData)
            For j = 1 To Abs(MyData(i, c))
                r = r + 1
                MyResults(r, 1) = MyData(i, 1)
                MyResults(r, 2) = MyData(i, 2)
                MyResults(r, 3) = MyData(1, c)
            Next j
        Next i
    Next c
    
    OutSh.Range("A2").Resize(UBound(MyResults), 5) = MyResults
    
End Sub

The parts in red you may need to adjust, based on your workbook. It assumes you already have a Sheet2 created. The 200 is the maximum number of output rows, and there should be nothing else on Sheet2. We can tweak it if those assumptions don't work.

Go back to Excel and test this on a COPY of your workbook. Press Alt-F8 to get the macro selector, or you can add this to a button if you want.

Let me know how this works.

 

[DavidGM]

Hi Eric,

Thank you very much - it seems to have worked (almost) perfectly! The only issue is that it also picks up (and lists) plus-scores, rather than just the minuses. Is it possible for this to be rectified, so it therefore only shows gaps, please? Apologies, I should have outlined initially that sheet 1 might also list positive figures as well as minuses - but I only require it to create a table based off the minuses (i.e. the gaps). Happy to explain further if required.

Otherwise, seems perfect. I'll have to see how it goes in use and once nurses get their hands on it though!

Thanks again.

 

[Eric W]

I wondered if there would be positive values. Change this line:

For j = 1 To Abs(MyData(i, c))

to

For j = 1 To -MyData(i, c)

And see if that helps.

 

[DavidGM]

Works perfectly, thanks again Eric - it's a huge help.