SOLVED

Count and show multiple numbers who appear in an array

Copper Contributor

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 SandroScreenshot 2022-12-17 at 08.40.35.png

10 Replies

Hi @sandrosg 

 

One way with 365:

Sans titre.png

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

 

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

@sandrosg 

 

Not sure I really understand

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

@L z. 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 

@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)))))))

 

Harun24HR_1-1671289808047.png

 

 

@sandrosg 

 

This works no problem here. No change in the formula:

Sans titre.png

 

Make sure there's enough empty cells below AZ94 & BA94 to allow the formula to spill, otherwise you get a #SPILL error, as in your picture (How to correct a #SPILL! error)

@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

@L z. Oh! Yes. I do not look that so carefully. Thanks for catching that.

@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)

 

best response confirmed by sandrosg (Copper Contributor)
Solution

With SCAN

 

Sans titre.png

=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))
)
1 best response

Accepted Solutions
best response confirmed by sandrosg (Copper Contributor)
Solution

With SCAN

 

Sans titre.png

=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))
)

View solution in original post