I have a named list of 35 ICD code values (the second column in this named list is the category of result just to simplify the 35 down to 15). I have a dataset with 500,000 rows, each a person, and multiple columns of data. If a person only had one diagnosis they will only have one column of data with maybe one of my 35, but also possibly something different (other type of ICD code). One person in this database has 258 different diagnosis codes, so everyone has potentially 258 columns to look through, but if they have 5, only the first 5 will have data and other 253 will be empty. Is there a way I can do a search of the 258 columns for any one of my 35 codes? Then I can return either the code or the category (2nd column of my named list). I'm just trying to find out how many of these 500,000 people had heart disease regardless of whether or not they had other any diagnosis. I can just return the first instance of my list and don't need all the times they subsequently may have had something else from my list.
list
10 . 1
11 . 1
12 . 1
21 . 2
22 . 2
etc
data
pt. . dx1 . dx2 . dx3
1 . 35 . 11 . 54
2. 61 . 32
3. 21 . 10 . 93
4. 52
5. 72 . 43 . 92
6. 51 . 37 . 12
a very rough idea. Pt 1 has an 11 so that's a hit, I can get either 11 or 1. Pt 2 has none, P3 has 2, but I only need the first one and can get either 21 or 2. Pt 4 and 5 don't have any and Pt 6 has one so I can get either 12 or 1.
Just not sure how to expand an HLOOKUP to not just check a single cell but anything from a list of values.
thanks, Maureen
Mac Excel version 16.71 from MS365
list
10 . 1
11 . 1
12 . 1
21 . 2
22 . 2
etc
data
pt. . dx1 . dx2 . dx3
1 . 35 . 11 . 54
2. 61 . 32
3. 21 . 10 . 93
4. 52
5. 72 . 43 . 92
6. 51 . 37 . 12
a very rough idea. Pt 1 has an 11 so that's a hit, I can get either 11 or 1. Pt 2 has none, P3 has 2, but I only need the first one and can get either 21 or 2. Pt 4 and 5 don't have any and Pt 6 has one so I can get either 12 or 1.
Just not sure how to expand an HLOOKUP to not just check a single cell but anything from a list of values.
thanks, Maureen
Mac Excel version 16.71 from MS365