Forum Discussion

sandrosg's avatar
sandrosg
Copper Contributor
Dec 17, 2022
Solved

Count and show multiple numbers who appear in an array

Hi there, I'm new to this community and tried searching the net and other support pages for a rather simple task. I got an array of numbers such as seen in the screenshot. I need to show those numbers which appear twice or even more and how many times.  I tried vlookup counta and so on...all in vain. So need a function who can scan the array ranging from AN:95 to AX107 and returns the "number" and "count" of those cells. Many thanks Sandro

excel
  • Lorenzo's avatar
    Lorenzo
    Dec 17, 2022

    With SCAN

     

    =LET(
  range,  AN95:AX107,
  tocol,  TOCOL(CHOOSECOLS(range, SEQUENCE(,(COLUMNS(range)+1)/4,3,4)),1),
  uniq,   SORT(UNIQUE(tocol)),
  freq,   SCAN(0,uniq, LAMBDA(seed,number, COUNT(FILTER(tocol,tocol=number)))),
  VSTACK({"Number","Count"}, HSTACK(uniq,freq))
)

10 Replies

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Dec 17, 2022

    sandrosg 

    Here's my rendition using REDUCE to deliver the solution.

     

    Defined name 'Numbers'

    =Sheet1!$AN$95:$AX$107

     'NVector - the above is converted to a vector, 0s pulled, and sorted.

    =SORT(TOCOL(numbers, 1))

    Lamba 'NStack.  Parameters setup to be used with REDUCE.  'k' pulls all instances of a given number.  'n' obtains a unique value from 'k'.  VSTACK - stack the accumulated value of 'a' with an HSTACK which includes the number and its count.

    =LAMBDA(a,v,LET(k, FILTER(NVector, NVector = v), n, UNIQUE(k), VSTACK(a, HSTACK(n, COUNT(k)))))

    Sheet level formula:

    =REDUCE({"Number","Count"},UNIQUE(NVector),NStack)

     

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Dec 17, 2022

      With SCAN

       

      =LET(
  range,  AN95:AX107,
  tocol,  TOCOL(CHOOSECOLS(range, SEQUENCE(,(COLUMNS(range)+1)/4,3,4)),1),
  uniq,   SORT(UNIQUE(tocol)),
  freq,   SCAN(0,uniq, LAMBDA(seed,number, COUNT(FILTER(tocol,tocol=number)))),
  VSTACK({"Number","Count"}, HSTACK(uniq,freq))
)
  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Dec 17, 2022

    sandrosg TOCOL() itself can be used as more dynamic using option 1 means ignore blanks.

     

     

    =LET(x,TOCOL(AO95:AX100000,1),y,SORT(UNIQUE(x)),
VSTACK({"Number","Count"},HSTACK(y,BYROW(y,LAMBDA(p,SUM(--(x=p)))))))

     

     

     

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Dec 17, 2022

      Harun24HR 

      I didn't take the TOCOL(array,1) route as in the initial picture there's a number of non-empty cells - Those in purple with 00:00

       

      Hope this makes sense

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 17, 2022

    Hi sandrosg 

     

    One way with 365:

    in AZ95:

    =LET(
  range,  AN95:AX107,
  tocol,  SORT(TOCOL(CHOOSECOLS(range, SEQUENCE(,3,3,4)),1)),
  freq,   DROP(FREQUENCY(tocol,tocol),-1),
  VSTACK({"Number","Count"}, FILTER(HSTACK(tocol,freq), freq))
)

     

    • sandrosg's avatar
      sandrosg
      Copper Contributor
      Dec 17, 2022
      Dear L z,
      thx for the quick response...I tried your example and it works like a charm. However when new values will be put in into the array left hand side how can they be shown in the array on the right hand side? Do I need to expand the right hand array (which is my result area) and provide some empty cells within the array ? Thx again for your help. Sandro
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Dec 17, 2022

        sandrosg 

         

        Not sure I really understand

        Please post a picture reflecting what you expect to add later & what the result should look like

Resources