Counting bins

[karkas]

I would appreciate some help identifying and quantifying available bins that medications are stored in.

My spreadsheet is 22k rows, so I need an easy way to find drawers that are underutilized. I'm ultimately looking to add a column in each row that show #bins available in that drawer by %, so I can sort and find the ones that are more empty, unless someone can think of an easier way.

I have the following report, which displays drawer/bin use in the following way:
medication cabinet name (omni_stid)
cabinet section (cabinet)
zone (zone)
drawer number(drawer_num)

So in this example, you can see that the first 24 rows is a 24 bin drawer (draw_desc). The (omni_stid), (cabinet, zone), and (draw_num) all match, so that is one drawer, and the second drawer continues below with a "2" in the (draw_num) column. You can see if the respective bin in each drawer is utilized, by whether there is a medication name there in the (rx_name) column.

If a bin does not have an assigned medication, it will read "(Available)" in the (rx_name column). You can also see that final column (qty_onhand) shows the quantity currently loaded in that bin. I don't care how many are in the bin, only whether it has a medication assigned and quantity on hand.

In this example, there would be 0% available for the drawer Cabinet PB1W, cabinet 0, zone 1, drawer 1.

Thanks in advance for any help generating a formula to display bin availability!

omni_stid	cabinet	zone	draw_num				draw_desc			omni_bin	bin_number																		rx_name		qty_parlvl	qty_onhand
PB1W	0	1	1				24 Bin Locking			33656	1																		CARVEdilol 6.25mg tab		10	9
PB1W	0	1	1				24 Bin Locking			33657	2																		phenyTOIN ER 100mg cap		16	12
PB1W	0	1	1				24 Bin Locking			33658	3																		morphine IR 15mg tab		10	8
PB1W	0	1	1				24 Bin Locking			33659	4																		lactobacillus acidophilus tablet		15	13
PB1W	0	1	1				24 Bin Locking			33660	5																		metoprolol succinate XL 50mg tab		10	11
PB1W	0	1	1				24 Bin Locking			33661	6																		fludrocortisone 0.1mg tab		5	12
PB1W	0	1	1				24 Bin Locking			33662	7																		pregabalin 75mg cap		10	6
PB1W	0	1	1				24 Bin Locking			33663	8																		rivaroxaban 20mg tab		5	3
PB1W	0	1	1				24 Bin Locking			33664	9																		diazePAM 5mg tab		5	11
PB1W	0	1	1				24 Bin Locking			33665	10																		HYDROcodone-aceta 10mg-325mg tab		15	16
PB1W	0	1	1				24 Bin Locking			33666	11																		HYDROcodone-acetam 5mg-325mg tab		30	16
PB1W	0	1	1				24 Bin Locking			33667	12																		methocarbamol 500mg tab		12	10
PB1W	0	1	1				24 Bin Locking			33668	13																		acetaminophen-ox 325mg-7.5mg tab		10	20
PB1W	0	1	1				24 Bin Locking			33669	14																		HYDROmorphone 4mg Tab		10	10
PB1W	0	1	1				24 Bin Locking			33670	15																		linagliptin 5mg tab		5	3
PB1W	0	1	1				24 Bin Locking			33671	16																		losartan 50mg tab		5	8
PB1W	0	1	1				24 Bin Locking			33672	17																		NIFEdipine ER/XL 90mg tab		4	7
PB1W	0	1	1				24 Bin Locking			33673	18																		amoxicillin-clav 875mg-125mg tab		6	4
PB1W	0	1	1				24 Bin Locking			33674	19																		glipiZIDE 5mg tab		6	10
PB1W	0	1	1				24 Bin Locking			33675	20																		DULoxetine 30mg cap		10	13
PB1W	0	1	1				24 Bin Locking			33676	21																		NIFEdipine 10mg cap		10	13
PB1W	0	1	1				24 Bin Locking			33677	22																		atenolol 50mg tab		8	6
PB1W	0	1	1				24 Bin Locking			33678	23																		ezetimibe 10mg tab		8	6
PB1W	0	1	1				24 Bin Locking			33679	24																		folic acid 1mg tab		8	10
PB1W	0	1	2				24 Bin Locking			33616	1																		metoclopramide 10mg tab		10	9
PB1W	0	1	2				24 Bin Locking			33617	2																		metaxalone 800mg tab		9	8
PB1W	0	1	2				24 Bin Locking			33618	3																		cyancobalamin 1000mcg tab		10	11
PB1W	0	1	2				24 Bin Locking			33619	4																		diltiaZEM CD 180mg cap		5	6
PB1W	0	1	2				24 Bin Locking			33620	5																		traMADol HCL 50mg tab		20	19
PB1W	0	1	2				24 Bin Locking			33621	6																		zolpidem 5mg tab		8	7
PB1W	0	1	2				24 Bin Locking			33622	7																		amiodarone 200mg tab		16	15

 

[marshy3300]

In K2 =If(and(H4="",J4=""),"Available","")

 

[karkas]

Thanks for the help, but I'm having trouble getting that to work. What is it supposed to be doing?

I think the comumns are messed up and that might be causing my problem. Here they are as they appear in my spreadsheet:
A = omni_stid
B = cabinet
C = zone
D = draw_num
H = draw_desc
K = omni_bin
L = bin_number
AD = rx_name
AF = qty_parlvl
AG = qty_onhand

Thank you!

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

Any chance you could give us another (small) example or two (preferably with XL2BB) where the % available is not 0% and give explanation of the % calculation for that example too?

 

[karkas]

Thank you Peter_SSs

I'm sorry for any confusion.

I updated my account details. Excel 2013 running in Windows 10.

Forgive my mistake. I actually posted the info from XL2BB, but I just chose the table only option. I moved the simplified data to another sheet. Here I chose "generate output" and got column and row info.

In this case, PB2NS, cabinet 0, zone 1, drawer 1 has 14 of 24 unused bins (58.3%), so I would like to add a column K that displays the 58.3%.

pharmacy drawer configuration by drawer bin2.xlsx
	A	B	C	D	E	F	G	H	I	J
1	omni_stid	cabinet	zone	draw_num	draw_desc	omni_bin	bin_number	rx_name	qty_parlvl	qty_onhand
2	PB2NS	0	1	1	24 Bin Locking	33656	1	ascorbic acid 500mg tab	10	27
3	PB2NS	0	1	1	24 Bin Locking	33657	2	zinc sulfate 220mg cap	10	19
4	PB2NS	0	1	1	24 Bin Locking	33658	3	famotidine 10mg tab	20	8
5	PB2NS	0	1	1	24 Bin Locking	33659	4	LORazepam 2mg/1ml 1ml vial	5	5
6	PB2NS	0	1	1	24 Bin Locking	33660	5	lacosamide 50mg tab	6	5
7	PB2NS	0	1	1	24 Bin Locking	33661	6	insulin 1unit/.01mL 10mL injecti	1000	0
8	PB2NS	0	1	1	24 Bin Locking	33662	7	(Available)	0	0
9	PB2NS	0	1	1	24 Bin Locking	33663	8	diazePAM 5mg tab	5	7
10	PB2NS	0	1	1	24 Bin Locking	33664	9	(Available)	0	0
11	PB2NS	0	1	1	24 Bin Locking	33665	10	(Available)	0	0
12	PB2NS	0	1	1	24 Bin Locking	33666	11	HYDROcodone-acetam 5mg-325mg tab	20	21
13	PB2NS	0	1	1	24 Bin Locking	33667	12	(Available)	0	0
14	PB2NS	0	1	1	24 Bin Locking	33668	13	(Available)	0	0
15	PB2NS	0	1	1	24 Bin Locking	33669	14	(Available)	0	0
16	PB2NS	0	1	1	24 Bin Locking	33670	15	(Available)	0	0
17	PB2NS	0	1	1	24 Bin Locking	33671	16	(Available)	0	0
18	PB2NS	0	1	1	24 Bin Locking	33672	17	(Available)	0	0
19	PB2NS	0	1	1	24 Bin Locking	33673	18	(Available)	0	0
20	PB2NS	0	1	1	24 Bin Locking	33674	19	oxyCODONE IR 5mg tab	35	14
21	PB2NS	0	1	1	24 Bin Locking	33675	20	(Available)	0	0
22	PB2NS	0	1	1	24 Bin Locking	33676	21	(Available)	0	0
23	PB2NS	0	1	1	24 Bin Locking	33677	22	(Available)	0	0
24	PB2NS	0	1	1	24 Bin Locking	33678	23	acetaminophen-oxy 325mg-10mg tab	6	9
25	PB2NS	0	1	1	24 Bin Locking	33679	24	(Available)	0	0
26	PB2NS	0	1	2	24 Bin Locking	33616	1	(Available)	0	0
27	PB2NS	0	1	2	24 Bin Locking	33617	2	adenosine 3mg/1ml 2ml inj	3	3
28	PB2NS	0	1	2	24 Bin Locking	33618	3	zolpidem 5mg tab	8	3
Sheet2

 

[marshy3300]

Thanks for the help, but I'm having trouble getting that to work. What is it supposed to be doing?

I think the comumns are messed up and that might be causing my problem. Here they are as they appear in my spreadsheet:
A = omni_stid
B = cabinet
C = zone
D = draw_num
H = draw_desc
K = omni_bin
L = bin_number
AD = rx_name
AF = qty_parlvl
AG = qty_onhand

Thank you!

I would suggest a new tab that just shows your bins and the percentage then.
In K2 enter =concatenate(A2&"-",B2&"-",C2&"-",D2) and expand down the list (makes your bins as PB2NS-0-1-1 or PB2NS-0-1-2 etc)
On the new tab Copy all of K column as text and remove duplicates. Then next to that =SUM(COUNTIFS(Sheet2!$K$2:$K$28,Sheet3!A2,Sheet2!$H$2:$H$28,"(Available)",Sheet2!$J$2:$J$28,0)/COUNTIF(Sheet2!$K$2:$K$28,Sheet3!A2))
Expand that down and change the format to percentage.

Also it looks like when its available trhen quantity is 0 anyway so you may not need ",Sheet2!$J$2:$J$28,0"

 

[karkas]

Thank you Marshy.

I wasn't quite able to get that to work in a single cell as you intended, but I was able to get it to work with a few extra columns this way:

1) Copied the original data (sheet named pharmacy drawer configuration b) to sheet 2 (on sheet 2 I removed all data except omni_stid column 'A'; cabinet 'B', zone 'C', draw_num 'D')
2) Concatenated omni+cabinet+zone+drawer# into (sheet named 'pharmacy drawer configuration b', column AK) and sheet 2 (column E)
3) On sheet2 column E (newly concatenated column), I removed duplicates to get one row per drawer.
4) on Sheet2 column F titled "Total bins" =COUNTIFS('pharmacy drawer configuration b'!$AK$2:$AK$22061,"=" & E2) and dragged down
5) on sheet2 column G titled "available bins" = =COUNTIFS('pharmacy drawer configuration b'!$AK$2:$AK$22061,"=" & E2,'pharmacy drawer configuration b'!$AD$2:$AD$22061,"=(Available)") & dragged down
6) on Sheet2 column H titled "percent available" = =G2/F2 and dragged down

Thanks for your help everyone!