Forum Discussion

Vinnie_Smith's avatar
Vinnie_Smith
Copper Contributor
Apr 14, 2024

4 integera (with no separators) in each cell of a column range. Counting each individual interger

Hello-

 

Help! I have a column that I have manually entered (or copied in) 4 digit numbers. All cell entries are exactly four digits, 0-9, with duplicates allowed (ex. 4424). I need a formula that will examine a range of these cells and tell me how many of each integer there are, reported in separate cells. I would like it to work for and display all zeros, even leading zeros.

Column Example

0278

3094

8823

Etc.

For the column example above, in ten separate cells, I want to see that there were 2 zeros, no ones, 2 twos, 2 threes, 1 four, no fives, no six, 1 seven, 3 eights, and 1 nine. I want all zeros that are entered displayed all the time, even leading zeros.

 

Thank you in advance for any hints or help!  -Vinnie-

excel
Formulas and Functions

1 Reply

Sort By
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Apr 14, 2024

    Vinnie_Smith 

    With 365 you can do this a few different ways.

    This method 'explodes'  each 4 digit number by converting each to a 1x4 array.  The result of using explode on the entire column is a matrix of numbers 4 columns wide.  TOCOL converts this matrix to a vector and then REDUCE cleans up.

     

     

    =LET(
    explode, LAMBDA(integer, MID(integer, {1, 2, 3, 4}, 1)),
    number_vector, TOCOL(explode(numbers)),
    unique_numbers, SORT(UNIQUE(number_vector)),
    CountNumbers, LAMBDA(acc, i,
        LET(
            total, SUM(N(i = number_vector)),
            VSTACK(acc, HSTACK(i, total))
        )
    ),
    REDUCE({"Totals", "Count"}, unique_numbers, CountNumbers)
)

     

     

     

    This task becomes a bit easier with access to the Insider function GROUPBY:

     

     

    =LET(
    explode, LAMBDA(integer, MID(integer, {1, 2, 3, 4}, 1)),
    number_vector, TOCOL(explode(numbers)),
    GROUPBY(number_vector, number_vector, COUNTA)
)

     

     

     

     

     

Resources