Search for instances of X in a sheet

T

ThaPCdoc

Where X is unknown.
I have 47,881 rows.

I have used *=COUNTIF(A1:A47881,"*"&"search phrase"&"*")*
with success.

However, this is suggesting I know what "search phrase" is.

The cell could contain any number of medical diagnoses, so I want to
run a search the most common matches.

Example, I want to see how many times HYPERTENSION appears in ANY of
the cells, or ASTHMA, or even DIABETES.

Since the number of searchable cells is so large, using
*=SUMPRODUCT((A1:A47881)/COUNTIF(A1:A47881,A1:A47881&"")* takes way too
long to even see if it works.

What I would love, if possible, is to be able to see the top 100 most
commonly used words within the sheet. There is only one column that is
being searched so there's nothing else to compare it to except another A
column.

Thanks in advance...
an over-zealous excell user
 
C

CLR

Have you considered usint the feature Data > Filter >
AutoFilter?..........seems like it's made to order for what you
want...........

Vaya con Dios,
Chuck, CABGx3
 
D

DOR

Your first formula implies that the search phrase could be wrapped by other
text in the cells to be searched. But your second formula seems to imply
that the search phrase exists alone in each cell since you seem to be trying
to count how many times the contents of each cell exists in the entire column
- your formula didn't seem to work on a short array, so I wonder if you left
something out of it.

Without knowing which situation exists, it is hard to suggest an aproach.
 
Top