Using your workbook MAIN FILE.xls:
I started a new worksheet named Results.
In cells C1 to S1 I entered the 17 categories, (You can use Paste Special with Transpose checked, to get the list horizontal)
In cell B1 I entered "No category listed for this company"
In column A, starting at cell A2, I entered the company names in alphabetic order. There were 718 unique names.
To get the unique names I selected B1 to B2234 on Sheet 1, then Data-Filter-Advanced filter...
Select 'Copy to another place', and check 'Unique records only', and select a single cell at the top of an empty column on Sheet1, and click OK.
The list can then be copied from Sheet 1 to the Results sheet.
In cell B2 I entered the following formula
=IF(COUNTIF(C2:S2,"Y")=0,"*","")The formula in B2 was dragged down to B719
In cell C2 I entered the following formula:
=IF(SUMPRODUCT((Sheet1!$B$2:$B$2234=Results!$A2)*(Sheet1!$A$2:$A$2234=Results!C$1))>0, "Y","N")Note the $ signs.
Copy Cell C2 and Paste to the range C2:S719
After the paste, cell S719 contains
If you add new names to the source list on sheet1, (columns A & B) as long as you add new company/category pairs within the existing list, the ranges used on the Results worksheet will change. If you add a new pair below the existing data, i.e., below row 2234, the ranges used on the Results sheet will have to be updated. You could use named ranges for Sheet 1 columns A and B, and use the names in the formulas to simplify this.
Select Sheet1 cells A2 to A2234, then Insert - Name - Define... Name the range 'Category', and Add, then change the range to B2 to B2234 and name the Range 'Company' and Add and OK. (No quotes around the names)
The formula in Cell C2 becomes
Next select the range B1 to S719 on the Results worksheet then Data - Filter - Autofilter.
Now the drop down arrows against each category can be used to select "Y", and all Companies providing that category will be shown.
Selecting "*" from the drop down in column B will show all Companies that have no categories listed.
You may find the results table easier to view if you replaced "N" in the formula with "", so that only positive associations of company and category are shown (Y).