Count word combinations in individual columns of a multi column database

{ 6 Comments }

By Ashish Mathur · · · Leave a comment

Assume a database of five rows and four columns.  Each cell of the database has some text value – let’s call this the source grid.  In another part of the same worksheets, the text values of source grid have been arranged as rows and column headings – let’s call this the destination grid.

The task is – “To display in the blank cells of the destination grid, total count of row and column heading combinations in each column of the source grid.”.  So the counting of row and column heading combinations has to happen in each individual column of the source grid and then all these numbers should be summed up.

The source grid, expected result and formula in individual cells of the destination grid haven been shown in this workbook.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • Hi Ashish,

    I checked your formula for counting combinations in the workbook but I still have some questions:

    1. why in this part of the formula ( TRANSPOSE(ROW($A$2:$A$6)-ROW($A$1:$A$5)), you included only “ROW($A$1:$A$5)”, instead of “ROW($A$2:$A$6)” ?

    2. will this formula work with columns with different numbers of rows?

    thank you in advance

    Reply

    • Hi,

      1. ROW($A$2:$A$6)-ROW($A$1:$A$5) is to generate a series of five consecutive 1’s. For performing a matrix multiplication, the number of columns in array1must equal the number of rows in array2. Columns in array1 are given by ROW($A$2:$A$6)-ROW($A$1:$A$5) – This will generate {1;1;1;1;1}. Number of rows in array2 i.e. rows in base data are 5

      2. This formula should work with different number of rows as well. Just expand the range from $A$2:$D$6 to $A$2:$D$10. Also, the transpose function would become ROW($A$2:$A$10)-ROW($A$1:$A$9)

      Hope this helps.

      Reply

  • Is there a way this could be adapted for counting combinations in a row rather than a column? Thanks

    Reply

  • Hi Ashish, thanks for your response, onedrive is not working for me at the moment so I will try again later.

    My data is the same kind of scenario as yours except arranged in rows instead of columns. So Group 1 would be: Cat, dog, mouse, ant, elephant in separate columns in a row. I want to know what the most common combinations in a row are.

    I hope that clarifies it but will try upload an example later on.

    Thanks

    Reply