Excel expert? I'm somewhat proficient but can't figure this one out.

There are 5 clinics. We want to standardize their med list so they all have the same meds (within reason). I've got a list of their inventory and want to note which clinic does not have a med that others do. More explanations below the table. I've thought of Index/Match, If/Then statements and possibly some type of Pivot table (really don't like pivot tables), but I just can't come up with a way. Any ideas would be appreciated.

Made up sample of the data, which is several thousand lines long.

 

One shot deal or a recurring analysis?

 

Do you have a list of all the meds that all clinics are supposed to have? You said you are standardizing, but you also said Clinic 4 doesn't stock something the others do. Is that because they are out of stock, or because they shouldn't carry that stock?

 

Create a master list of ID (rows), then each clinic is column. Could use "unique" for that if you don't have it.
Then you should be able to create an index match at each intersection to bump against your data table.
I'm not at a computer, but I think that would move you along, at least for the design.

 

How many conditions do you need to know? Meaning, do you just need to know combinations (Id, Clinic) that aren't present, or do you need to return "Manufacturer Out", etc?

 

This would be super easy and the correct application for MS Access, or I could write something in Python pretty quick I think. But, the problem is sharing and having other users understand those is shenanigans - so we can shoehorn excel and pivot tables.

Make a table



Make a pivot table, click this box




Rt Click here, and "Add measure"



Make this:
Note the function is CONCATENATEX, with the X.



make this the pivot table:



Turn off grand totals and play around with what you want for formatting

Here you go



Edit - I made the clinic numbers Clinic1 Clinic2 etc from my first screenshot being 1 2 etc respectively, to see it better and easier and never went back to change that screenshot. Woops.

 

Thanks for replies jn meetings, will replywhen i'm out.

 

The only data that really matters is the Facility and Med ID. The Med ID is unique to each medication. If it's in stock or not doesn't factor in at this point. All I need is what clinic doesn't have a Med ID that the others do (if the clinic doesn't show up for a Med ID, then they don't have it on their formulary, but should).

I've tried the index/match @ace_10 mentioned but I'm not seeing how to get the result. The issue with Index/Match is it uses the first match it finds, so using the Med ID will always return the first clinic. Same would happen if I used the clinic name. Unless I missed something he mentioned.

So, in the table below, Med ID 24665 is on the formulary at clinics 1-3, but not clinic 4 or 5. That's what I need to know, for all Med IDs.

 

@GarlicFarts - Yeah, this will need to be sharted. I mean shared. I have a little knowledge around pivot tables but generally shy away from them. I think I may need to dive in and try pivot tables... I do have Access on my work laptop, however we don't use it and I never got around to learning it. I heard Power BI might be something worth learning as well.

 

I won't lie and I won't sugar coat it. If you're shying away from pivot tables, access and power BI are just jazzed up pivot tables (really, pivot tables are a toned down version of a database).

 

I need to get over it and jump in. For me it is far from intuitive, but I imagine at some point a light will go on and I'll "get it".

 

The biggest thing is knowing how to think like a database. Once you understand that, it's all pretty easy. The data structure of your tables is sorry important.

https://youtu.be/wR0jg0eQsZA?si=y50OfG3GxMecX4YH

 

Thanks. I'm going down the rabbit hole. Reading about ERD now...

 

Would V look up be an option, sorry im looking via a phone amd cant see the data too well..
Use the clinics as the common look.up and import over the med ids?

 

EDIT: I was on mobile last night and now that I re-read @GarlicFarts post they pretty much laid out what I am about to write anyhow. My mistake.

As someone said above, "unique" the medicine listing and run that along your Y-axis. Then across the top on the X-axis, clinics 1 through 5. In the field, just do some index/matching combined with if/iserror stuff to return either blank cells or populated cells. Conditionally format the blank cells to stand out and you can have what you need VISUALLY. It's kind of a table and kind of visual so it's in a weird space. If you get more medicines, it'll populate the unique field already (that is, if you are formatting the medicine listing in a table and referencing as such, which you should be because it makes your life easier )

I'll say that Power BI is not bad but I wouldn't call it simply glorified PTs. It's more a visual tool than anything else in my opinion. Still need to be able to procure data from sources, which in many cases are databases (at least in the manner I used PBI).

@GarlicFarts nailed it; if you start thinking in terms of databases, things fall into place. Learn SQL too, btw, if you don't already (not so much for your task but just in general whilst learning databases). Oh and Python is currently in beta preview in Excel so might want to learn that too. Makes scripting a bit more "easy" than VBA.

Thanks to all the users that chimed in here, always nice to hear different approaches

 

Another option is to add a unique identifier column for Med/Clinic combination, then one can use VLOOKUP to accomplish what you want by linking the horizontal and vertical lookup criteria such as the formula shown