cell selection and data entry in binary matrix

Hi excel world,

I'm running into a bit of a roadblock, and I'm hoping someone might have some helpful suggestions -

I've set up a binary matrix, such that I have 421 unique headings repeated twice: once in each column and once in each row (i.e., in the matrix the headings for row 5 and column 5 will trace back to the same description in the column headings and in the row headings).

I need to fill in 1s (case satisfied) or 0s (not satisfied) in this 421X421 matrix. Where my problem lies is in finding the exact row-column cell combination in which I need to enter the 1s. Descriptions that belong to the same group or category will be marked by these 1s in the matrix. For example, if I have descriptions 5, 9, 13, and 17 belonging to the same group I need to enter a 1 in each possible combination of these numbers: {5,9} {5,13} {5,17} {9,13} {9,17} {13,17}. I need a quick formula that will compute all possible combinations for inputted descriptions AND will enter a 1 into the cells, which correspond to these combinations.

I apologize if this is unclear. Explaining the setup in words is proving difficult.

Any assistance would be a great help as I'm running out of time with these analyses!

Cheers,

Nick

I've attached an example spreadsheet.

The headings are listed as "unique x" across 16 rows and mirrored across 16 columns. I created 5 mock categories, and labeled them in the AFTER sheet. Each cell with a 1 in it denotes that those two 'items' belong to the same category. So, for say, category 2 in my example workbook (items 5, 7, 14, 15), 1s are entered in the cells pertaining to each possible combination of those items: {5,7}{5,14}{5,15}{7,14}{7,15}{14,15}

After all 1s are entered into the cells for each category, 0s will be entered into those cells that remain blank.

What I need is an automated method of doing this because my matrix is rather large and manual entry would take eons.

See attached file.

HTH,
WindKnife

Thanks WindKnife (and to the others),

I'm trying to wrap my head around the function you created. If you wouldn't mind, could you translate it into the vernacular of an excel newbie like myself? I'm going to try and apply what you did to my main workbook and play around with the data. Much appreciated!

Nick

Specifically, the parts of the function "," that are concatenated to the specified rows/columns. I haven't come across this before ... thanks!

WindKnife,

I've made attempts to apply the formula to my master sheet, but I can't seem to figure it out. In the example I provided, there were 5 mock categories in a 17X17 matrix. In the real file, the number of categories are not set (although they will probably be around 15) and the matrix is 421x421. I've tried increasing the range while introducing a sixth mock category, but to no avail: The formula returns a false, or in our case a 0, where it should be true, or a 1. Any ideas? I'm pressed for time and losing hair by the second it seems!

Look at this:
The input sheet was constructed manually: If your input sent has the potential to get complex, we can
think of a macro to generate the input columns.

Otherwise this should serve your purpose. Since the formulas are arrays I am not sure if
performance will be a factor.

Maybe, you can upload your sample file.
I think you forgot to committed with Ctrl+Shift+Enter.
By the way, I modified the formula for all cells.
See attached file.

WindKnife

Hi WindKnife,

Thanks again for the help. Everything seemed to be working until I got up into the higher values of my matrix. I'm thinking the function doesn't apply to triple-digit numbers. What do you think? I was so close to having it finalized! Thanks again - your generosity is appreciated ...

Nick

I have tried to test a 150x150 matrix. I don't find any error data.
You can upload a dummy file with error situation.

WindKnife

Sure thing. Here's a working example of a 200X200 matrix.

In the first two categories (10,20,30,40,50 & 90,99,91,5) the function returns the 1s in the respective cell combinations. But when I introduce triple digit numbers (98,100,101,102) in the third category, the 1 doesn't appear where it should.

I'm scratching my head over here...!

You get a wrong answer because you type "98100101102" in a cell.
You must type 98,100,101,102 in this cell, or '98,100,101,102 .

WindKnife