• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count elements in array

V

vinb

New Member
I have an array that has certain ERROR Codes for various cases (Case #1, #2 etc), row-wise in a spreadsheet.

I simply need a count of all various ERROR Codes. Suppose Error Code "ABC" is seen 3 times overs various cases, then it should simply show the count of ABC as 3.
If some new error code gets added to the raw data, then that should be added to the output list and the respective count should be displayed.


Case#1ABCJKLPQR
Case#2DEF
Case#3XYZPQR
Case#4ABC
To
Output
Error codeCount
ABC
2​
DEF
1​
JKL
1​
PQR
2​
 

Attachments

  • ArrayValuesCount.xlsx
    10.1 KB · Views: 8
Maybe with Power Query

Load data to it. (delete any added steps that promote headers and or detect column data types)
Select first column, unpivot other columns.
select third column and group by, choose row count.
load to sheet.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Value"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"
77229
 
You got dynamic arrays (O365 I think)?
If you give the range B3:D6 in your sheet the defined name a then this in cell C10:
Code: 
=SORT(UNIQUE(FILTER(INDEX(a,MOD(SEQUENCE(ROWS(Table1)*(COLUMNS(a)))-1,ROWS(a))+1,MOD(SEQUENCE(ROWS(a)*(COLUMNS(a)))-1,COLUMNS(a))+1),INDEX(a,MOD(SEQUENCE(ROWS(a)*(COLUMNS(a)))-1,ROWS(a))+1,MOD(SEQUENCE(ROWS(a)*(COLUMNS(a)))-1,COLUMNS(a))+1)<>0)))
then in the cell to the right of it:
Code: 
=COUNTIF(a,C10#)

Using the table refs makes for longer formulae. In cell G10:
Code: 
=SORT(UNIQUE(FILTER(INDEX(Table1[[Column2]:[Column4]],MOD(SEQUENCE(ROWS(Table1)*(COLUMNS(Table1)-1))-1,ROWS(Table1[[Column2]:[Column4]]))+1,MOD(SEQUENCE(ROWS(Table1)*(COLUMNS(Table1)-1))-1,COLUMNS(Table1)-1)+1),INDEX(Table1[[Column2]:[Column4]],MOD(SEQUENCE(ROWS(Table1)*(COLUMNS(Table1)-1))-1,ROWS(Table1[[Column2]:[Column4]]))+1,MOD(SEQUENCE(ROWS(Table1)*(COLUMNS(Table1)-1))-1,COLUMNS(Table1)-1)+1)<>0)))

and:

=COUNTIF(Table1[[Column2]:[Column4]],G10#)
There has to be a better way of getting a 1d array from a 2d range…
 

Attachments

  • Chandoo47278ArrayValuesCount.xlsx
    11.3 KB · Views: 4
assuming the error codes are of equal length
[G3]=
Code: 
=LET(Data,Table1,
Cnt,SUM(COUNTIF(Data,{">0","?*"}))-ROWS(Data),
String,"00"&SUBSTITUTE(TEXTJOIN("",TRUE,IF(LEFT(Data,4)="case","",Data)),"0",""),
Arr,UNIQUE(MID(String,(SEQUENCE(Cnt)*3),3)),
Arr)
[H3]=
Code: 
=COUNTIF(Table1;G3#)
 

Attachments

  • ArrayValuesCount.xlsx
    11 KB · Views: 4
LET! Of course!
Mine [note: almost: I found a major fault with my original in msg#3 - see definition of d] using LET (and without a named range):
Code: 
=LET(aa,Table1[[Column2]:[Column4]],
ra,ROWS(aa),
ca,COLUMNS(aa),
b,SEQUENCE(ra*ca)-1,
c,MOD(b,ra)+1,
d,INT(b/ra)+1,
e,INDEX(aa,c,d),
SORT(UNIQUE(FILTER(e,e<>0))))
(perhaps I didn't need to define c & d since I only used them once)
Although I'm now thoroughly confused as to whether my version of Excel being Current Channel (Preview) is what they used to call an Insider version, so maybe the OP hasn't got LET available to him.
 
Last edited:
I'm curious about what versions of Excel have LET available now; if you go to File, Account and then next to the About tile I see:
77241

What do you see in your Excel where I've highlighted by a red box?
 
Since we seem to have covered existing solutions well, this is looking to the future. I have used a Lambda helper function REDUCE to run through the codes to append them to a list, irrespective of whether they have occurred previously. SORT UNIQUE completes the formula.
Code: 
= SORT(UNIQUE(
      REDUCE(,array,
         LAMBDA(acc, code, IF(ISTEXT(code), VSTACKλ(acc, code), acc) )
      )
  ) )
The catch is that I also have to define a function VSTACKλ to append an individual item to the list
Code: 
= LAMBDA(list, item,
    LET(
      n, 1+ROWS(list),
      k, SEQUENCE(n),
      IF(k<n, list, item))
  )
 

Attachments

  • ArrayValuesCount.xlsx
    11.6 KB · Views: 5
I'm on Semi-Annual. Let is around for about 6 months. At least, that is when my IT released it to be installed on the laptops together with the dynamic array functions. However those were available for over a year on the web apps. #Confused
LAMBDA ( ) and LAMBDA ( )-helpers are not (yet) available.

But just found out I can switch to insider :DD. Must admit LAMBDA ( ) scares the hell out of me :eek:...
 
@GraH - Guido
Go on, make the switch! I have yet to work through to a consistent development strategy, but I am tending to move to the view that the Lambda function provides a foundation for any solution; they are not simply 'the icing on the cake'. After all, If you view the spreadsheet as a form of programming, rather than simply as a strategy that allows end-users to manipulate numbers, it is functional programming.

For example, I recently wrote a formula that would run through a 10-pin bowling score card:
  1. Identifying strikes and spares ("X" and "/");
  2. substitute 10 and 10-x for the marks above;
  3. accumulate the ball count;
  4. accumulate the pin count;
  5. score the frame (counting the following two or three throws);
  6. accumulate each frame score to obtain a set of game scores;
  7. average the game scores
  8. calculate a bowling handicap on the basis of the game series.
All with one array formula in a single cell. Whether I should have done that is questionable, some helper cells might have been of interest in their own right; what was remarkable is that it was possible. It read like a program, not a spreadsheet.
 
Guido
... but the Spreadsheets you write are already programming! Now, however ...
  1. LET allows nested formulas to be tidied up and made more readable by introducing variable names for the inner terms of a nested formula.
  2. LAMBDA is practically the same thing but, instead of defining all the variables within the function, some are represented by dummy variables and the actual values are passed to the formula as parameters.
  3. Defined Names allow formulas to be represented by name (often the formula simply references a range but any formula can be referred to in this manner). This allows the developer/user to name Lambdas and so create user-defined functions without VBA or TypeScript.
The resulting formulas read very differently from a traditional spreadsheet but, for me, that is not the main difference. The main difference may lie in the development process itself. Rather than starting by entering an arithmetic expression in cell A1 and replicating it, the process might involve some more overt planning steps. For example: 'Given the customer name and transaction date, I would like to have a function that returns the transaction amount'. The resulting function will hide the detail, so you would not see the CHOOSE that took you to a different Table or the details of the XLOOKUP with two dimension variables. In traditional development, the same thing could be achieved by using helper cells to break up the calculation, but that creates its own problems. The resulting sea of numbers contains much that is of little interest yet each used cell will provide the opportunity to introduce errors.

Currently, only about 10% or spreadsheets are correct. If more structured working could halve the error rate, then the chances of a given spreadsheet being correct would be closer to 50%-50%. That may not sound great, but it would be a huge improvement.
Peter
@GraH - Guido
@p45cal
 
@vletm
True, we probably have digressed from the original subject of the thread. In our defence I would point out that #4, #5, #10 offers a complete solutions to the OP question using modern Excel.
Where would you consider the appropriate place to be for the ongoing discussion; perhaps the Lounge?
 
You must log in or register to reply here.
Back
Top