Most frequent text (not number) within a very large matrix not a single row or column

**leehowardblair** · 10-07-2019, 01:53 PM

I have a bunch of names in rows 36:98, although there are also a lot of blanks and "X" as well which I would like to skip.

I want to find the top 3 occurring names within that range and how many time each occurs?

I have searched and searched and cannot figure out a solution. I would like to do this without VBA.

Thanks in advance for any help!

Lee

**63falcondude** · 10-07-2019, 02:28 PM

Are they all in one column or multiple?

I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

**leehowardblair** · 10-07-2019, 02:34 PM

I basically want the most frequent names from $B$39:$IQ$98 ignoring blanks and "X" values
I could make all of the "X" values blank if it would help.

**leehowardblair** · 10-08-2019, 10:56 AM

I have since gotten rid of all the columns of x’s so now it’s just the blue columns that exist so I no longer need to ignore the value “X”

**JeteMc** · 10-10-2019, 08:18 PM

This proposal is a two step approach.
1a. Make a list of distinct names using*:

Formula:

Please Login or Register  to view this content.

*Denotes an array entered formula which needs to be activated by holding the Ctrl and Shift keys while pressing the Enter key. The formula needs to be activated before it is copied.
1b. Count the occurrences of the names using: =IF(A2="","",SUMPRODUCT(--(INPUT1920!B$39:F$98=A2)))
2. Make a pivot table that displays the top 3 occurring names.
Note that in the attached file more than 3 names are shown because 18 names occur twice (18 ties for second most occurrence)
Let us know if you have any questions.

**menem** · 10-11-2019, 02:03 AM

If you allow to use many of helper cells >_<.

This file may be fit your requirement.

Note : Answer from your sample is : AVS - MACKINNON

Concept of calculation.

Combind all text together with TEXTJOIN (auto skip blank cell)
Remove X, from combind text.

** I use INDIRECT function in this file , but you may override it by change this formula.
B3

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

Then repeat the following process
Find 1st word before ,
C10

Please Login or Register  to view this content.

Subsitute found text from combind text
B11

Please Login or Register  to view this content.

The rest is just count and find 1st max count text.

Regards.

Most frequent text (not number) within a very large matrix not a single row or column

LinkBack

Thread Tools

Rate This Thread

Display

Most frequent text (not number) within a very large matrix not a single row or column

Re: Most frequent text (not number) within a very large matrix not a single row or column

Re: Most frequent text (not number) within a very large matrix not a single row or column

Re: Most frequent text (not number) within a very large matrix not a single row or column

Re: Most frequent text (not number) within a very large matrix not a single row or column

Re: Most frequent text (not number) within a very large matrix not a single row or column

Thread Information

Users Browsing this Thread

Similar Threads

Find most frequent word on matrix

[SOLVED] How to count the most frequent number in a Column

[SOLVED] Return top 5 most frequent text strings from a column

Check most frequent number in 1 column that has 4 digits number

Find the most frequent and second most frequent text string whilst ignoring numbers

Matrix to single column

Changing a matrix into one large column

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions