Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: Return most common, 2nd most common, 3rd most common, etc. text string in an array

  1. #1
    New Member
    Join Date
    Oct 2012
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Return most common, 2nd most common, 3rd most common, etc. text string in an array

    I have a table of data [Table_CQI_Contacts_be.accdb] of which one column [Provider Name] has text strings. My goal is to write a formula that looks in this column of data and returns the following:

    Most Common Text String
    2nd Most Common Text String
    3rd Most Common Text String
    .
    .
    .
    20th Most Common Text String

    Sometimes the column contains a blank field some I've incorporated a conditional IF statement using the ISBLANK function. To try and get to the most common text string I've been trying to use the MODE or LARGE functions but to no avail. I think the MODE.MULT function in EXCEL 2010 is exactly what I"m looking for but I'm in EXCEL 2007.

    Here is the arrary formula that I"ve been trying to use thus far and it does work for finding the Most Common Text String, but I can't get it to work for the 2nd, 3rd, etc.

    Code:
    =IF(ISBLANK(Table_CQI_Contacts_be.accdb[Provider Name]),"",INDEX(Table_CQI_Contacts_be.accdb[Provider Name],MATCH(LARGE(COUNTIF(Table_CQI_Contacts_be.accdb[Provider Name],Table_CQI_Contacts_be.accdb[Provider Name]),large_seq_count),COUNTIF(Table_CQI_Contacts_be.accdb[Provider Name],Table_CQI_Contacts_be.accdb[Provider Name]),0)))
    The named range [large_seq_count] is an integer listing 1,2,3,4...20 for the formula to reference when fulfilling the (k) component of the LARGE formula.

    Again, I'm running EXCEL 2007 and any help is much appreciated.

  2. #2
    New Member
    Join Date
    Oct 2010
    Location
    Manchester, UK
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return most common, 2nd most common, 3rd most common, etc. text string in an array

    This may be a tad more basic than you want...

    I would have a 2nd column that had a COUNTIF which referenced the first column, probably with an OFFSET (looking back up the list to ensure that you weren't capturing the same phrase twice), then in a 3rd column use the LARGE function to sort the most commonly used phrases... with a VLOOKUP to the phrase next to the largest value (you will need to add a very small number to all of your COUNTIF values to prevent LARGE / VLOOKUP repeating the same phrase twice)
    JP

  3. #3
    New Member
    Join Date
    Oct 2012
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Re: Return most common, 2nd most common, 3rd most common, etc. text string in an array

    Quote Originally Posted by poolio View Post
    This may be a tad more basic than you want...

    I would have a 2nd column that had a COUNTIF which referenced the first column, probably with an OFFSET (looking back up the list to ensure that you weren't capturing the same phrase twice), then in a 3rd column use the LARGE function to sort the most commonly used phrases... with a VLOOKUP to the phrase next to the largest value (you will need to add a very small number to all of your COUNTIF values to prevent LARGE / VLOOKUP repeating the same phrase twice)
    JP
    JP, I like where you are going, but I'm not following for some reason. Can you generate a small sample worksheet using the following names in one column?

    Data Set:
    Harmon
    Smith
    Smith
    Harmon
    Williams
    Williams
    Smith
    Williams
    Williams

    Thanks!!

  4. #4
    New Member
    Join Date
    Oct 2010
    Location
    Manchester, UK
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return most common, 2nd most common, 3rd most common, etc. text string in an array

    BD,

    I've produced a sheet, but can't attach (am I missing something?!)

    Anyway here it is with the equations expanded... these equations could be combined, but I've left them apart for ease of understanding...

    CountIF Offset Check + Small No VLOOKUP Large Name
    1 Harmon =IF(D2>1,0,COUNTIF(B:B,B2)) =COUNTIF(OFFSET($B$2,0,0,A2,1),B2) =C2+A2/1000 =B2 =LARGE($E$2:$E$10,A2) =VLOOKUP(G2,$E$2:$F$10,2,0)
    2 Smith =IF(D3>1,0,COUNTIF(B:B,B3)) =COUNTIF(OFFSET($B$2,0,0,A3,1),B3) =C3+A3/1000 =B3 =LARGE($E$2:$E$10,A3) =VLOOKUP(G3,$E$2:$F$10,2,0)
    3 Smith =IF(D4>1,0,COUNTIF(B:B,B4)) =COUNTIF(OFFSET($B$2,0,0,A4,1),B4) =C4+A4/1000 =B4 =LARGE($E$2:$E$10,A4) =VLOOKUP(G4,$E$2:$F$10,2,0)
    4 Harmon =IF(D5>1,0,COUNTIF(B:B,B5)) =COUNTIF(OFFSET($B$2,0,0,A5,1),B5) =C5+A5/1000 =B5 =LARGE($E$2:$E$10,A5) =VLOOKUP(G5,$E$2:$F$10,2,0)
    5 Williams =IF(D6>1,0,COUNTIF(B:B,B6)) =COUNTIF(OFFSET($B$2,0,0,A6,1),B6) =C6+A6/1000 =B6 =LARGE($E$2:$E$10,A6) =VLOOKUP(G6,$E$2:$F$10,2,0)
    6 Williams =IF(D7>1,0,COUNTIF(B:B,B7)) =COUNTIF(OFFSET($B$2,0,0,A7,1),B7) =C7+A7/1000 =B7 =LARGE($E$2:$E$10,A7) =VLOOKUP(G7,$E$2:$F$10,2,0)
    7 Smith =IF(D8>1,0,COUNTIF(B:B,B8)) =COUNTIF(OFFSET($B$2,0,0,A8,1),B8) =C8+A8/1000 =B8 =LARGE($E$2:$E$10,A8) =VLOOKUP(G8,$E$2:$F$10,2,0)
    8 Williams =IF(D9>1,0,COUNTIF(B:B,B9)) =COUNTIF(OFFSET($B$2,0,0,A9,1),B9) =C9+A9/1000 =B9 =LARGE($E$2:$E$10,A9) =VLOOKUP(G9,$E$2:$F$10,2,0)
    9 Williams =IF(D10>1,0,COUNTIF(B:B,B10)) =COUNTIF(OFFSET($B$2,0,0,A10,1),B10) =C10+A10/1000 =B10 =LARGE($E$2:$E$10,A10) =VLOOKUP(G10,$E$2:$F$10,2,0)

    And how it would look... I would round up the results and add an IF<1,"" in the Large Name column to get rid of the repeats...

    CountIF Offset Check + Small No VLOOKUP Large Name
    1 Harmon 2 1 2.001 Harmon 4.005 Williams
    2 Smith 3 1 3.002 Smith 3.002 Smith
    3 Smith 0 2 0.003 Smith 2.001 Harmon
    4 Harmon 0 2 0.004 Harmon 0.009 Williams
    5 Williams 4 1 4.005 Williams 0.008 Williams
    6 Williams 0 2 0.006 Williams 0.007 Smith
    7 Smith 0 3 0.007 Smith 0.006 Williams
    8 Williams 0 3 0.008 Williams 0.004 Harmon
    9 Williams 0 4 0.009 Williams 0.003 Smith

    Hope this helps...
    JP

  5. #5
    New Member
    Join Date
    Oct 2012
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return most common, 2nd most common, 3rd most common, etc. text string in an array

    JP, Thanks a bunch! I was able to do some combining of the formulas you've got here and I added on some date criteria that I wanted to incorporate as well so I modified a couple of your CountIf f(x)s to CountIfs. Overall, this strategy worked perfectly and gave me what I was looking for. Thanks a bunch for your assistance!

  6. #6
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,623
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Return most common, 2nd most common, 3rd most common, etc. text string in an array

    Quote Originally Posted by bdkyzer View Post
    JP, I like where you are going, but I'm not following for some reason. Can you generate a small sample worksheet using the following names in one column?

    Data Set:
    Harmon
    Smith
    Smith
    Harmon
    Williams
    Williams
    Smith
    Williams
    Williams

    Thanks!!
    Try this...

    Sheet1

     ABC
    2Harmon_Williams
    3Smith_Smith
    4Smith_Harmon
    5Harmon__
    6Williams__
    7Williams__
    8Smith__
    9Williams__
    10Williams__

    Enter this array formula** in C2:

    =IFERROR(INDEX(A2:A10,MODE(MATCH(A2:A10,A2:A10,0)+{0,0})),"")

    Enter this array formula** in C3 and copy down until you get blanks:

    =IFERROR(INDEX(A$2:A$10,MODE(IF(COUNTIF(C$2:C2,A$2:A$10)=0,MATCH(A$2:A$10,A$2:A$10,0)+{0,0}))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    .
    Biff
    Microsoft MVP - Excel

    Don't be afraid to use volatile functions or array formulas
    Tell us what version of Excel you're using
    KISS - Keep It Simple Stupid

  7. #7
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,302
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Return most common, 2nd most common, 3rd most common, etc. text string in an array

    Or you can use a Pivot Table - very easy and efficient

    Names ---> Row Labels
    Names ---> Values area
    Sort

    Names Counting
    Williams 4
    Smith 3
    Harmon 2

    M.

  8. #8
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    8,897
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Return most common, 2nd most common, 3rd most common, etc. text string in an array

    As the data is in a database table, maybe just run a query against it. Set up via menu ALT-D-D-N and follow the wizard.

    This database type approach might be preferred over formulas?

    For counts of all data, SQL could be
    Code:
    SELECT [Provider Name], COUNT(*) AS [MyCount]
    FROM Table_CQI_Contacts_be
    GROUP BY [Provider Name]
    If you only want the TOP n, where n is an integer, use instead
    Code:
    SELECT TOP n [Provider Name], COUNT(*) AS [MyCount]
    FROM Table_CQI_Contacts_be
    GROUP BY [Provider Name]
    Or maybe only those with a count more than 10
    Code:
    SELECT [Provider Name], COUNT(*) AS [MyCount]
    FROM Table_CQI_Contacts_be
    GROUP BY [Provider Name]
    HAVING COUNT(*) > 10

    hth

  9. #9
    New Member
    Join Date
    Oct 2010
    Location
    Manchester, UK
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return most common, 2nd most common, 3rd most common, etc. text string in an array

    Quote Originally Posted by bdkyzer View Post
    JP, Thanks a bunch! I was able to do some combining of the formulas you've got here and I added on some date criteria that I wanted to incorporate as well so I modified a couple of your CountIf f(x)s to CountIfs. Overall, this strategy worked perfectly and gave me what I was looking for. Thanks a bunch for your assistance!
    Yahay!!

    After a couple of years of getting assistance from Mr Excel, so glad that for the first time I've been able to help someone else...
    Just made my day, so glad I could help!

    JP

  10. #10
    Board Regular Marcol's Avatar
    Join Date
    Mar 2010
    Location
    Fife, Scotland
    Posts
    644
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return most common, 2nd most common, 3rd most common, etc. text string in an array

    Yet another way, introducing tie-breakers, therefore easily listing results when there are duplicate counts.

    Sheet1

     ABCDE
    1Data Set:Helper NameOccurrences
    2Harmon  Williams4
    3Smith  Smith3
    4Smith  Harmon2
    5Harmon2.005   
    6Williams    
    7Williams    
    8Smith3.008   
    9Williams    
    10Williams4.010   
    11     
    12     
    13     
    14     
    15     

    Spreadsheet Formulas
    CellFormula
    B2=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=COUNTIF($A$2:$A$100,A2),COUNTIF($A$2:$A$100,A2)+(ROW()/1000),""))
    D2=IF(ROWS($1:1)>COUNT(B:B),"",INDEX(A:A,MATCH(LARGE(B:B,ROWS($1:1)),B:B,0)))
    E2=IF(D2="","",COUNTIF($A$2:$A$100,D2))


    Excel tables to the web >> Excel Jeanie HTML 4

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •