Help! Is there a way to search multiple columns for a specific word?

[dbwiz]

I have a huge database that has about 90 columns containing ICD9 code descriptions. I need something along the lines of a text filter for "Contains" a specific word, such as Malignant. I tried to do this in a pivot table but that doesn't work - way too many columns. Any thoughts?

 

[JoeMo]

Use Excel's built-in Find function (activate the sheet, press ctrl + f and type in the word you want to find).

 

[dbwiz]

Use Excel's built-in Find function (activate the sheet, press ctrl + f and type in the word you want to find).

Sorry, that isn't what I was looking for, because I need to not only find them, I need to be able to show a grand total of claim amounts tied to those, as well as count the number of those instances. I was looking for something more along the lines of some VB.

 

[JoeMo]

If you post a sample of your database and some more specifics about what you want to accomplish it will improve your chances of getting a solution.

 

[shawnhet]

Hi dbwiz,

You could try something like the following for each line - I used a range of a2:at2 and my search term in au2 to test it out.

=IF(SUM(LEN(A2:AT2))-SUM(LEN(SUBSTITUTE(A2:AT2,$AU$2,"")))>0,1,0) - Array entered (Ctrl+Shift+Enter) in AV2 and copied down for all your rows.

This should let you know if a row contains your search term in it and then I can't see a reason why you can't refer to that column in your pivot table.

Cheers,

 

[dbwiz]

That is EXACTLY what I needed shawnhet! Thanks so much and you just made my day!

 

[shawnhet]

You're very welcome and Thanks for the feedback!

Cheers,

BTW, I didn't mention above that the search term will be case sensitive.

 

[dbwiz]

Thank you for mentioning that. Would you be able to search for multiple "keywords" or numbers, then? In case I needed to search for Malignant and malignant, or maybe any numbers ranging from 215 to 240?

 

[shawnhet]

There is probably a better way of doing this but this will let you check if one of three keywords is present (located in AU2,AU3 and AU4)

=IF(SUM(LEN(A2:AT2))-SUM(LEN(SUBSTITUTE(A2:AT2,$AU$2,"")))+SUM(LEN(A2:AT2))-SUM(LEN(SUBSTITUTE(A2:AT2,$AU$3,"")))+SUM(LEN(A2:AT2))-SUM(LEN(SUBSTITUTE(A2:AT2,$AU$4,"")))>0,1,0)

you can add more keywords by adding +SUM(LEN(A2:AT2))-SUM(LEN(SUBSTITUTE(A2:AT2,$AU$2,""))) before the > sign and changing the AU2 to another cell

I am sure that this can be done more simply but I will have to think about it a bit.

Cheers,