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

dbwiz

Active Member
Joined
Nov 20, 2007
Messages
275
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?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Use Excel's built-in Find function (activate the sheet, press ctrl + f and type in the word you want to find).
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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, :)
 
Upvote 0
That is EXACTLY what I needed shawnhet! Thanks so much and you just made my day!
 
Upvote 0
You're very welcome and Thanks for the feedback!

Cheers, :)

BTW, I didn't mention above that the search term will be case sensitive.
 
Last edited:
Upvote 0
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?
 
Upvote 0
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, :)
 
Upvote 0

Forum statistics

Threads
1,211,963
Messages
6,105,114
Members
447,947
Latest member
OX_2005

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top