Google Spreadsheet Count Pair Combinations Option 2

Question:

( by Jonathan Zyzalo )

Hi there,

Have been using google spreadsheets to do a lot of laborious calculations lately, but I am stuck or have a mental block. Maybe I just don't have enough quite time to think about this problem in terms of the formula I need (been working on it for 3 nights now).

For simplicity, if I have rows of 4 numbers, each cell in a row can contain any number from 1 to 50. Simple example below:

1	2	5	4
2	4	8	10
5	6	7	8

What I am wanting is to count how many times a number pair comes up, ie 2 & 4 in row1 and row2. I believe I have got it to work for each row with the following formula:

=IF(LEN($A$1:$A$3),AND(countif(A1:D1,"2"),countif(A1:D1,"4")),iferror(1/0))

But this results in a boolean TRUE or FALSE for each row (I would like an integer). I could use another countif(E1:E3, TRUE), but that is a lot of extra cells for a range of 1:50 number pairs.

I constructed a table to display the count of pairs, where xx is a space filler as a number cannot appear in a row more than once:

	1	2	3	4	5	6	7	8	9	10
1	xx
2		xx
3			xx
4				xx
5					xx
6						xx
7							xx
8								xx
9									xx
10										xx

Is there a way to combine my two formula so that the answer is obtained by doing the formula calculation in a 1 cell only?

Solution:

Have a look at the following screenshot of 'Sheet 1':

Have a look at the following screenshot of 'Sheet 2':

I have just one formula in Cell A1 of 'Sheet 2':
=kishan('Sheet1'!A1:D10)

I have made custom function named "kishan" in the script:

function kishan()
{
  if (arguments.length < 1)
    return "#NA Please input range to sort";
  else if (arguments.length > 1)
    return "#NA Input Can't be more than one arguement";
  
  var numbers = 50;
  var values = arguments[0];
  var returnValues = new Array();
  
  for( var i=0 ; i<=numbers ; i++ )
    returnValues[i]= new Array();
  
  for( var i=0 ; i<=numbers ; i++ )
  {
    returnValues[0][i] = i;
    returnValues[i][0] = i;
    for( var j=i+1 ; j<=numbers ; j++ )
    {
      for(var x=0;x<values.length;x++)
      {
        for(var y=0;y<4;y++)
        {
          for(var z=0;z<4;z++)
          {
            if(values[x][y]==i && values[x][z]==j && i!=j)
            {
              if(returnValues[i][j]==null)
              {
                returnValues[i][j]=1;
                returnValues[j][i]=1;
              }
              else
              {
                returnValues[i][j]+=1;
                returnValues[j][i]+=1;
              }
            }
          }
        }
      }
    }
  }
  
  return returnValues;

}

Put the above code in the script editor of your spreadsheet.


And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive