Excel Help Thread

Yeah he needs to get his algebra clearly defined before we can do it in Excel

 

Yeah the lightbulb came on when I was trying to figure out how to get around the basic problem that I couldn't weight a 0. It ended up being simpler that I thought, its just that whenever I got the formula in like that it spit the answer out formatted in dollars, which made no sense to my pea brain. I finally figured out it was giving me the right answer, just formatted as $ instead of %

Got to =SUMProduct(D4:D7*G4:G7)/SUM(D4:D7)

 

Anyone know how to reference data in workbooks that will all have a slightly different name to a master data repository sheet automatically? I think I have to use VBA, but I would prefer not to.

 

You could probably use the INDIRECT function but I hate that thing as it's crazy easy to make mistakes with it.

 

Hopefully someone can help me out here, b/c I can't for the life of me figure out how I can do this in excel...

What I am trying to do is identify all records in this file that contain a code of 99201-99215 and also include a code of lets say 96360 - 96417 for the same Date of Service (DOS). There are multiple dates and multiple times a person could show up too. If they don't meet that criteria, I don't care about them and wanna get rid of them in the list for my analysis.

This is a snippet of what it looks like:


So in the example above, I wouldn't care about John, Jane or Jenny Doe, i would only care about Jim Doe. I have about 80k lines to go through so doing this by hand is not gonna happen. Any ideas?

 

Simple pivot. Im assuming you cant send me the file because it will contain hella PHI

I can create a dummy file and show you how.

 

Turn on filters and maybe convert them to numbers instead of text so you can filter ranges.

 

For the love of all things holy god don't use filters.

 

Why? Filter those records and copy them to a new sheet. Better option than a pivot table.

 

Because filtered tables are the devil and should never be used. Certainly not with large data sets with multiple conditions. You don't have a view into whats filtered all at once, you have to click on each column to make sure you have the filtered info you want selected. In a pivot its much easier, more clear and less error prone.

 

I'm not filtering, let's see this pivot table solution.

 

Use Python.

 

Your option stops being useful if this is something you have to do more than once.

 

Put it in a SQL database and make your life 100000 easier.

 

i was assuming he was trying to identify all records so his data set only has the information he wants to analyze. For analysis, definitely a pivot table.

 

Just write a nested if statement for those ranges

 

Nice filters.

 

lol filters were on because I had to create a formula and then sort alphabetically to blind my patient protected info, not to splice data or for analysis as you suggested.

I was also just giving you shit. You could have filtered and survived.

 

All good. Just giving you shit as well. Always multiple ways to accomplish a task.

 

I'm not sure I explained it clearly enough, but what I need to identify are those patients that have a code of range x to y, if they have a code in the range of x to y, then i need to see if they had another code in a range of y to z on the same date of service. If they have both, let me know they have both on the same date of service.

i.e.
Jim Doe had code 99215 (this is one range) on 6/29/16, he also had code 96365 and 96366 (these codes call in another range). This is what I am trying to identify and pull out from a large dataset.
John Doe I wouldn't care about b/c he only had a 99214 on 6/13/16 and no other codes on that day.

 

Pivot your data. Rows will be the unique patient ID. Columns would be the CPT Codes (select only the codes in ranges you want to see). Pick any column to be a count of the data in that range. At least this would organize your data easier with one row per patient and a count of all their CPT Codes. You can quickly sum the row and find anything that had 1 vs multiple.

 

I feel like I'm way overthinking this but can't think of an easier way. One major assumption is that you're only trying to identify when a patient had both codes on the same day. You mention you don't care about John Doe because he has only 1 code. Would you care if John Doe had a code 96365 on a different day? If the answer is no, I have a solution.

Using the same set up you presented, Title cell E1 "Primary ID", F1 "Compound", and G1 "Find".
In E2 type the formula =IF(D2=99215,A2,"") and format this as a number.

This will identify the person and the date in which they had the primary Code Procedure.

In F2 type the formula =IF(E2="","",B2&E2) which will concatenate the Patient Name and Date of Service only for cells that meet the Primary Criteria.

In G2 type the formula =IF(D2=96365,FIND(F:F,B2&E2),""). This is where I identified Patient Name and Date of Service combinations that matched both criteria. The end result is a value of 1 in G4 since Jim Doe had CPT code 96365 and 99215 on the same day. The rest of the records should be blank.

 

*Sort A-Z and add a level.
*pivot

 

Just make 4 new columns to make it easier.

Column 1, If date = date then 1
Column 2, if value 1 between x and y then 1
Column 3, if value 2 between x and y then 1

You can then use an and function on all 3 or just sum the 3 columns in column 4 and do an if statement on the and. If column1:Column3 =3 then "Whatever you want for true"

Now you have all your variables

 

OK, so here's how I'd do it...

Spoiler

First of all, I wouldn't use patient name, please find another less ambiguous field like patient ID.
Here's a random page that I made:


Second, Pivot the data.


I then threw out months from the rows and changed the values from "sum" to "count"

Then I grouped the column headers:



And collapsed the groups



Then I switched both row values to "Tabular form" so that the appear next to eachother rather than one being a sub of the other and it looked like this:




So the top patient, number 127, had 3 treatments with codes between 99214 and 99216 on 4/22 but didn't have any with the codes 99217-99219. He also had one that wasn't in that range.

How's this look?

(That'll be $25, btw).

 

Put me on team pivot tables default format should be tabular.

 

I know, right? I edit 75% of my tables that have more than one value in the row field.
(Actually, I secretly like that it doesn't default to that because I get to look smart by knowing how to change it.)

 

That’s so true. Most of the time I’m showing excel illiterate people something mind blowing, that’s the trick.

 

I can remember the first time I found out about the tabular view and just having my mind blown at how awesome it was.

 

Pivot works looking for grouped ranges of values and not specific values.

I did an index match array which pulls the actual code for each dos and PT ID concatenation, but it’s a pita.

Pivot is definitely easier if the output suffices

 

That's perfect. Tiger Tiger Woods Y'all way worked, but it was definitely more complex.

Alright so one last thing (sorry for noob questions)... So I need to flip these two groups around, I need Office Visit Codes in front of Injections (see below). I also need a formula to identify for me when there is a value in the injections column, but no number in the office visit column. Example: so for DOS 2/25/16, there were 2 codes for injections, but not codes for office visits, I'd want to flag this as such. I am sure its some type of IF statement.

 

Nvm on the column thing. I got that.

 

bump

 

=IF(AND(ISNUMBER(C6),ISNUMBER(D6)),"","FLAG")

 

Thanks. It is flagging row 8 when there are numbers in both cells, how can i modify the formula to not flag when there are numbers in both column B and C?

 

It shouldn't be doing that. Check and make sure it's referencing C8 and D8

 

Ok, that's working, not sure what happened. I had to change the reference to B and C cells, i don't care about column D. However, I do not care if column C is blank if there is a value in column B. Is there a work around that not flagging in that occurrence?

i.e. I don't care about Row 11, 12, 13, etc...

 

it’s very tricky to make a formula that references a row or value in a pivot table.
Instead, Copy the pivot table, paste values only into a new tab or sheet, run formulas on the pasted table.

Spoiler

You don't work for a particular hospital on the upper west side, do you?
(I'm waiting for a call back on an interview.)

 

No.

 

have a date/time question:

facility where I work counts delivery days as 6am to 6am and we get deliveries 24/7. I have data with time-stamp of delivery but need to setup a formula to manipulate the days to mirror the 6am to 6am "delivery date"

there has to be an IF function involving the date serial number but I cannot figure it out

TIA

 

Subtract 6 hours from the date. Whatever your time stamp cell is - (6 / 24)

 

so much simpler than I was making it

 

I have a sheet that is used for time tracking. I have my workdays going from left to right and want to shade every other week gray. I figured out how to do this once for every 5 days, but is there a conditional formatting formula to do this for alternating blocks of 5?

Nm... Internet knows all

 

yes. Hth

 

Got another burner. Let's say I have two numbers that I've converted to a percentage that are in cells a1 (5%) and b1 (10%). I want to combine those to one cell that says 5% - 10%.

I've tried the concat and concatenate functions, but I can only get the raw decimal numbers and it won't let me format it to percentages without decimals. Any ideas?

 

I’m not in front of a computer so I’m guessing from my phone…

=concatenate(text(A1,0%),” - “,text(B1,0%))

 

this is correct

 

Concatenate operator is now pipe vs coma when you upgrade to Windows 11

 

i tried that and it didn't work