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 number...
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))
)
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

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

        Lorenzo When I put in new unknown values the function stops working. Pls see the screenshot. The red numbers are new so for 529 I need to get a 2 and for 856 a 1. Short and sweet:" how to handle new unknown values" ?Thx again Sandro 

Resources