Can MS count?

[mduffin]

I have a spreadsheet with 30 rows of data, the data in column 7 will either have a 1 or an 8 to indicate a person.. The number of 1s and 8s is different every time.
Can MS look at that column and determine how many 1s and how many 8s and return that number as a variable?

I was thinking something along the lines of running it through a loop 30 times to get the number of 1s and 8s.
Like this...

Let>Loop=0
Let>result1=0
Let>result8=0
Let>row=0
Repeat>loop
Let>row=row+1
XLGetCell>file,Sheet1,row,7,result

If>result=1
Let>result1=result1+1
Endif

If>result=8
Let>result8=result8+1
Endif

Let>loop=loop+1
Until>loop=30

But I'm wondering if there isn't an easier way.
Thanks much

[hagchr]

Hi, One way would be to do as you suggest, to simply loop over the relevant cells and just add up the numbers. An alternative (since there are so many powerful functions in excel) is to place a formula (in this case COUNTIF) in an empty cell to calculate the value and then bring the result back into MS. It is maybe an overkill in this simple example but illustrates how you can do it.

Code: Select all

XLOpen>C:\...\your excelfile.xlsx,1,xlBook

//Define name of Worksheet in question as well as an empty cell where you can put a temporary formula 
Let>Sheet=Sheet1
Let>row=1
Let>col=100

//Define formulas that will count number of "1" and "8" in the whole G-column
//If only part of G-column is needed then just change to eg G1:G50 etc
Let>tmp1==countif(G:G,"1")
Let>tmp2==countif(G:G,"8")

//Loop over the two criteria, person 1 and 8, place the formula in the empty cell and get the result back
Let>ct=0
While>ct<2
    Let>ct=ct+1
    Let>formula=tmp%ct%
    XLSetCell>xlBook,Sheet,row,col,%formula%,scResult
    XLGetCell>xlBook,Sheet,row,col,result
    Let>Res%ct%=%result%
EndWhile

XLQuit>xlBook

MDL>%Res1%, %Res2%

[hagchr]

Another alternative could be to use RegEx. (I think the XLSheetToArray requires xls format so for new excel files you may need to save as xls)

Code: Select all

XLSheetToArray>C:\...\Your excelfile.xls,Sheet1,arr

Let>tmp2=

//Create a string, concatenating the relevant cells separated with commas
//If column 7 contains irrelevant cells you need to change the loop
//to just run over the relevant cells, ie to change the limits
Let>ct=0
While>ct<arr_count
    Let>ct=ct+1
    //Get value for row ct and column 7
    Let>tmp=arr_%ct%_7
    Let>tmp2=%tmp2%,%tmp%,
EndWhile

//RegEx to search for number of 1s
Let>tmp0=(?<=,)1(?=,)
RegEx>tmp0,tmp2,0,Matches1,Res1,0,,

//RegEx to search for number of 8s
Let>tmp0=(?<=,)8(?=,)
RegEx>tmp0,tmp2,0,Matches2,Res2,0,,

MDL>%Res1%, %Res2%