Forum Discussion

bobo_kbg's avatar
bobo_kbg
Copper Contributor
Jan 19, 2022

Help for formula for extracting different words and sorting them among spreadsheet

Hello all,

 

I need a specific formula. I've search a lot on internet, found way more complicated requests but not this one.

I will be typing different words in different cells and columns. Something like this:
B2: Home
C4: Contacts
C3 Home
H5: Prices

There will be hundreds of words, most of them will  the same. Also some cells might be empty.

I need a formula that will tell me all the words I've used, how many times I used them and sort them by how many times I've used them.

It will be great If the formula can do this among a whole excel document - between different spreadsheets. But even for one spreadsheet will be OK.

Thanks in advance. 

BI & Data Analysis
excel
Formulas and Functions

9 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 20, 2022

    bobo_kbg 

    If name the source as range

    that could be

    =LET(
   n,     ROWS(range),
   m,     COLUMNS(range),
   k,     SEQUENCE(n*m),
   rN,    INT( (k-1)/m)+1,
   cN,    MOD(k-1,m)+1,
   all,   LET( t, INDEX( range, rN, cN), FILTER(t, t<>"")),
   list,  UNIQUE(all),
   count, MMULT(--(list=TRANSPOSE(all)), SEQUENCE(ROWS(all),,1,0) ),
   IF({1,0}, list, count)  )

     

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 19, 2022

    bobo_kbg 

    It depends on which Excel version/platform you are. And in any case it's better to define the range in which to search, otherwise you could wait recalculation forever.

    • bobo_kbg's avatar
      bobo_kbg
      Copper Contributor
      Jan 20, 2022

      SergeiBaklan

       

      I am using 

      Microsoft® Excel® for Microsoft 365 MSO (Version 2112 Build 1) 64-bit

       

      It is not a problem to define 15-20 words that I will use. After filling all the data I will know that I've used this, this and this word and I will be able to pre-define them in the final formula.

       

      Attached is a photo of what I want to achieve

      • NowshadAhmed's avatar
        NowshadAhmed
        Iron Contributor
        Jan 20, 2022
        If you are going to predefine the names that would be used, then here is what I would do for each defined name:
        COUNTIF($B$2:$F$12,B28)

        If you didn't define names, then I would use a dynamic list to display the unique names and then apply the above formulas to show the count of reoccurrence:
        UNIQUE($B$2:$F$12) # to generate a unique list
        COUNTIF($B$2:$F$12,B28) # to display the count

Resources