Search for instances of X in a sheet
 

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

----- EDIT -----
I should probably clarify that the cells in column A could have up to a whole paragraph of unformatted text.

EXAMPLE OF CELL Ax:

HYPERTENSION #401.9
LOW BACK PAIN #724.2
PREGNANCY #V22.2
PPD-NOS
CONT MEDICATION

[END EXAMPLE]
Of course in Excel the format would look more like
...etc.

Doc

Search for instances of X in a sheet
 

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



"ThaPCdoc" wrote:


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


--
ThaPCdoc

Search for instances of X in a sheet
 

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.

"ThaPCdoc" wrote:


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


--
ThaPCdoc