Excel Array assistance

[shelby55]

Hello

I'm using Excel 2003.

I have a workbook with a worksheet entitled "rawdata". Each line of data is one hospital visit.

There are 25 columns for diagnoses (O to AM) and 25 columns for diagnosis type (AN to BL).

How do I search through the diagnosis array to find a certain diagnosis and then determine if it is a certain diagnosis type?

For instance if diagcd3 = "I20" and dxtype3 in ("1","W","X","Y") then count 1.

Thanks.

 

[SkipVought]

Hi,

First, what happens WHEN there become more than 25 diagnoses in a visit? A much better structure would be ti have one diagnosis and type per row. Then there could be many more than 25 or just one per patient, per visit.

Why not query...

if diagcd3 = "I20" and dxtype3 in ("1","W","X","Y") then count 1.

[tt]
Select Count(*)
From [Your Sheet Name$]
Where diagcd3 = 'I20'
and dxtype3 in ('1','W','X','Y')
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

The database structure for the rawdata worksheet has that many diagnosis columns because there could be up to 25 diagnosis codes with 25 possible diagnosis types.

I was thinking I would need an array to look at all 25 to first find the applicable code and ensure that the code is the correct type.

In your example, would I do that for all 25 occurrences of diagnosis code/type and then sum them?

Note that there are 17 comorbid conditions and each condition has more than 1 code to check against. One hospital visit could have more than one comorbid condition so I'll need to sum all conditions as well for a final tally.

 

[SkipVought]

But WHY 25? Why is 25 the magic number? Why could there not be 26 diagnoses or more? 25 seems to be an abitrary limit, based on some unstated reason. It results in an NON NORMALIZED data base.

I would build another table for listing each target diagnosis and related types, and then join the two to tally the count. However, it will be VERY difficult with your current structure, which is NOT conducive to analysis.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

The database is a standard one used in hospitals for coding. Only up to 25 diagnoses are accepted within the database so there are only 25 placeholders for these diagnoses. So the columns designated for these 25 diagnoses are as I stated.

Some patient visits may have only 1 diagnosis, some may have 3 all the way up to a maximum of 25. It depends on how sick the patient is!!

So wouldn't this be a case of using arrays?

 

[SkipVought]

So what is a typical question? It seems that the initial question that I answered was not really typical.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Skip, you lost me. I'm not sure what you're asking me or what you need to know to help me.

 

[SkipVought]

Was my solution sufficient to solve your question?

If not, what is an example of the criteria that you need to use.

Please also provide a sample 'array', sample criteria and the result you expect using both.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

1: Are the daignosis and diagnosis type always paired up so they will always be both in the 3rd column or both int he 5th column of their respective areas?
2:How many different combinations do you have? i.e. for "I20" are you always going to be checking for any of ("1","W","X","Y")? Is that the same for other disgnoses? (same rule I ean - of course it may be a different array?)

How are you expecting to get results? Do you need to query all rows for a specific pattern or all patterns for a specific row?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[shelby55]

Hi Geoff

Great question, thanks. Yes, they will always be paired up - diagnosis type #2 will always refer to diagnosis #2.

As mentioned, there are 17 comorbid scenarios based on codes. What I want to do is loop through all diagnoses and once I find I20 then I need to know what diagnosis type it is. But I don't want to stop looping once found because I need to know if it exists again as another diagnosis type.

 

[SkipVought]

How do I search through the diagnosis array to find a certain diagnosis and then determine if it is a certain diagnosis type?

For instance if diagcd3 = "I20" and dxtype3 in ("1","W","X","Y") then count 1.

I do not understand how my query does not answer this question, unless I misunderstand your structure...
[tt]
diagcd1 diagcd2 diagcd2 .... diagcd25 dxtype1 dxtype2 dxtype3... dxtype25
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I set up a small test
[tt]
diagcd1 diagcd2 diagcd3 dxtype1 dxtype2 dxtype3
I20 W
C55 1
W12 T
W03 U
I20 V
S34 X
I20 Y
[/tt]
using your criteria and the SQL I posted, my query returns 2

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

So if it existed again as another diagnosis type what would you do with it? Add 1 to the count or a new count?

I guess the question is aimed at what is the end result you want from all this?

For your structure (which tbh I agree wioth Skip is not the best for this kind of analysis) you will be able to implement a loop such as:

Function DiagCt(rw,strDiag,strDiagType)
Dim arrDiag as variant
Dim arrDiagType as variant
Dim i as integer, ctr as integer

arrDiag = range("O" & rw & ":AM" & rw)
arrDiagType = range("AN" & rw & ":BL" & rw)

ctr = 0

For i = lbound(arrDiag) to ubound(arrDiag)

   if arrDiag(i) = strDiag AND instr(strDiagType,arrDiagType(i)) > 0 Then
      ctr = ctr + 1
   end if

Next i

DiagCt = ctr
End Function

You can call this code in a worksheet function

=DiagCt(2,"I20","1,W,X,Y")

will check for the combination you specified in your post in row 2 of your worksheet

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[lionelhill]

if you want a spreadsheet-only solution, just set up a further 25 columns containing O1 & "$%$" & AN1, etc. (i.e. concatenating diagnosis and diagnosis type) and then count instances of a particular combination in the usual way. Note that you may need to concatenate including a divider-string that cannot be found anywhere else (e.g. "$%$") if you ever think you'll have a situation where two different combinations concatenate the same ("S2"&"3" = "S"&"23")