Thread: Excel Help--How to fill column if cell value matches a specific value

Hello, i have a very large data of patient data and procedures/complications/injuries associated with each patient

The injuries and what not are coded in ICD-9 format, so they are four digit numbers, e.g. 88.67, I have a glossory of these codes which lets me know what each procedures is, e.g. 88.67=X-ray

Question 1:

If I wanted to create a column called "Xray" how do I fill it with all values for the corresponding code? Is there a way for me to program the sheet to fill the column labeled "X-ray" with all values=88.67 for example?


Question 2:
I am creating a database from the above information where i will have a column for patient ID number, Procedure done:
A1=Patient ID
B1=Xray

Is there a way for me to automatically fill this sheet from my raw datasheet which is in the form:

A1=Patient ID
A2=ICD-9 Code

so as to show which pt had which procedures performed in column format:

e.g.
Pt #124 had ICD-9 Code # 88.67 performed would automatically fill the cell value associated with Xray for pt 124?

Question 3:
If a patient had multiple X rays done of different types, say they had 2 Head X rays and 3 chest X rays, is there a way to fill my sheet in the above mentioned way that it shows the total number of X rays done, basically adding the two types together when filling?

Sorry if this sounds confusing, I am somewhat new to excel

]Thank you

A sample workbook would make this much easier - if you could upload a sample workbook with no sensitive/private data showing how the data currently is and how you would like it to appear in the worksheet I think most of what you want could be done with formulas rather than having to resort to VBA to achieve what you want. A sample workbook would make things much easier - as I said make sure you show specifically the expected output as you want it to appear and include some raw data (non sensitive) as well.
Some IFS, sumproduct, concatenation I think could do most of what you want however without a workbook it is very difficult to help you.

Hi, thanks for the reply, I might post this again at the general forum, but here's an uploaded workbook with how the data should look,

Sheet 1 is the raw data
Sheet 2 is how I want it to look afterwards

No this is certainly going to be easier with VBA so don't post in the general forum - I will write some code for you - it should be straight forward. Will get back soon

For CT total and Reduction Totals I have used formulas - the rest is in a macro - A question - if a patient has multiple procedures does that need to be recorded multiple times or just once - so will they have multiple 11.18 or multiple 11.11 - Have a look at the workbook and see if it is what you are after. It may need to be changed to your needs.

Option Explicit
Sub splitData()
On Error GoTo errHandler
Application.ScreenUpdating = False
Dim lRow As Long, k As Long, t As Long, splStr, desc As String
lRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
For k = 2 To lRow
Worksheets("Sheet2").Range("B" & k) = Worksheets("Sheet1").Range("B" & k)
Worksheets("Sheet2").Range("A" & k) = Worksheets("Sheet1").Range("A" & k)
splStr = Split(Worksheets("Sheet1").Range("E" & k), ",")
For t = LBound(splStr) To UBound(splStr)
desc = Trim(splStr(t))
Select Case desc
Case Is = "11.11"
Worksheets("Sheet2").Range("C" & k) = "Patient" & Worksheets("Sheet1").Range("A" & k)
Case Is = "11.13"
Worksheets("Sheet2").Range("E" & k) = "Patient" & Worksheets("Sheet1").Range("A" & k)
Case Is = "11.14"
Worksheets("Sheet2").Range("F" & k) = "Patient" & Worksheets("Sheet1").Range("A" & k)
Case Is = "11.15"
Worksheets("Sheet2").Range("G" & k) = "Patient" & Worksheets("Sheet1").Range("A" & k)
Case Is = "11.16"
Worksheets("Sheet2").Range("I" & k) = "Patient" & Worksheets("Sheet1").Range("A" & k)
Case Is = "11.17"
Worksheets("Sheet2").Range("J" & k) = "Patient" & Worksheets("Sheet1").Range("A" & k)
Case Is = "11.18"
Worksheets("Sheet2").Range("D" & k) = "Patient" & Worksheets("Sheet1").Range("A" & k)
End Select
Next
Next
Application.ScreenUpdating = True
Exit Sub
errHandler:
MsgBox "Error transcribing data - error " & Err.Number & " - " & _
Err.Description
End Sub