Dec 16 2022 11:44 PM
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
Dec 17 2022 01:37 AM
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))
)
Dec 17 2022 02:01 AM
Dec 17 2022 03:11 AM
Not sure I really understand
Please post a picture reflecting what you expect to add later & what the result should look like
Dec 17 2022 03:27 AM
@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
Dec 17 2022 07:03 AM - edited Dec 17 2022 07:10 AM
@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)))))))
Dec 17 2022 07:12 AM
This works no problem here. No change in the formula:
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)
Dec 17 2022 07:19 AM
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
Dec 17 2022 07:26 AM
Dec 17 2022 09:07 AM
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)
Dec 17 2022 09:56 AM
SolutionWith 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))
)
Dec 17 2022 09:56 AM
SolutionWith 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))
)