# Help regarding Formula

Microsoft Microsoft excel 2007 (pc)
December 3, 2009 at 02:12:44
Specs: Windows XP

See More: Help regarding Formula

#1
December 3, 2009 at 06:09:20
 Hi,I have a solution for you.First your business - category list will need to be in separate columnsUse Left and Right functions to separate out the business name from the category.Put Categories in column A and Businesses in column B.Using your example data:``` A B 1 Cat Busn 2 AA1 BUS1 3 AD6 BUS4 4 AB2 BUS2 5 AB1 BUS4 6 AA1 BUS3 7 AD6 BUS1 8 AA1 BUS4 ```The results are in a table as follows:Category headings in row 1Business headings in column C``` C D E F G 1 AA1 AD6 AB2 AB1 2 BUS1 3 BUS2 4 BUS3 5 BUS4 ```The following formula goes in cell D2`=IF(SUMPRODUCT((\$A\$2:\$A\$8=D\$1)*(\$B\$2:\$B\$8=\$C2))=1,"Y","N")`Note the \$ signs. These are required so that the formula can be dragged, to fill the whole table.After dragging the formula in cell G5 should contain`=IF(SUMPRODUCT((\$A\$2:\$A\$8=G\$1)*(\$B\$2:\$B\$8=\$C5))=1,"Y","N")`The result using your data was:``` AA1 AD6 AB2 AB1 BUS1 Y Y N N BUS2 N N Y N BUS3 Y N N N BUS4 Y Y N Y ```(Note: In your example data you had Category AB1 for Business BUS4, so this result is slightly different to your example result).Hope this gives you what you want.Regards

Report •

#2
December 3, 2009 at 06:34:17
 Still now it has worked perfectly. I hope it will also work when I increase the category numbers to 17.Many many thanks to you.Soumya

Report •

#3
December 3, 2009 at 06:39:31
 Hi,Glad it worked, and it should work for more categories.If you have problems, please post back.Regards

Report •

Related Solutions

#4
December 4, 2009 at 03:42:56

Report •

#5
December 4, 2009 at 03:46:10
 Sorry there may be problem in understanding a category name or a company name. Please check the list below. I have tried to add separator between company and category.categories+company list[this list includes duplicates]------------------------------------------------------------------------------COMPUTED TOMOGRAPHY------ AADCO Medical, IncCOMPUTED TOMOGRAPHY------ AccelaradCOMPUTED TOMOGRAPHY------ Accusoft PegasusCONTRAST AGENTS, DRUGS AND PHARMACEUTICALS------ ACR Image MetrixCONTRAST AGENTS, DRUGS AND PHARMACEUTICALS------ b.e.imaging GmbHCONTRAST AGENTS, DRUGS AND PHARMACEUTICALS------ Bayer HealthCare PharmaceuticalsCONTRAST AGENTS, DRUGS AND PHARMACEUTICALS------ BeckelmannCONTRAST AGENTS, DRUGS AND PHARMACEUTICALS------ BraccoCONTRAST AGENTS, DRUGS AND PHARMACEUTICALS------ Cone InstrumentsCONTRAST AGENTS, DRUGS AND PHARMACEUTICALS------ CovidienCONTRAST AGENTS, DRUGS AND PHARMACEUTICALS------ DMS Health TechnologiesDARKROOM EQUIPMENT AND SUPPLIES------ Atlantis Worldwide, LLCDARKROOM EQUIPMENT AND SUPPLIES------ Bar-Ray Products, IncDARKROOM EQUIPMENT AND SUPPLIES------ BeckelmannDARKROOM EQUIPMENT AND SUPPLIES------ CIRS, IncDARKROOM EQUIPMENT AND SUPPLIES------ CNMC Co., IncDARKROOM EQUIPMENT AND SUPPLIES------ CodonicsDARKROOM EQUIPMENT AND SUPPLIES------ Cone InstrumentsDARKROOM EQUIPMENT AND SUPPLIES------ DMS Health TechnologiesDEPARTMENTAL MANAGEMENT------ 3M Health Information Systems, IncDEPARTMENTAL MANAGEMENT------ AADCO Medical, IncDEPARTMENTAL MANAGEMENT------ AccelaradDEPARTMENTAL MANAGEMENT------ Accusoft PegasusDEPARTMENTAL MANAGEMENT------ Advanced MobilityDEPARTMENTAL MANAGEMENT------ AFC Industries, IncDEPARTMENTAL MANAGEMENT------ Agfa HealthCareDEPARTMENTAL MANAGEMENT------ A-Life MedicalDEPARTMENTAL MANAGEMENT------ American Institute of Architects (AIA-AAH)Thanks,Soumya

Report •

#6
December 4, 2009 at 04:38:07
 Hi,To help me understand this, can you confirm:AThere are only800 unique companies and17 categoriesBSome companies provide materials or services in more than one category.CThe total of 2234 companies you refer to are records containing a company name and a category, Dthe 2234 records contain many duplicates of a company's name, but no duplicates of category plus company nameRegards

Report •

#7
December 4, 2009 at 05:01:46
 Hi,To help me understand this, can you confirm:AThere are only 744 unique companies and 17 categories - TRUE [changes in the company number. Its final]BSome companies provide materials or services in more than one category.-TRUECThe total of 2234 companies you refer to are records containing a company name and a category- Its actually approximate. But its around 2300 total companies.Dthe 2234 records contain many duplicates of a company's name, but no duplicates of category plus company name--YES.Regards

Report •

#8
December 4, 2009 at 05:43:55
 Hi,You can download the main file also.Note: Only one company is missing in the company with category list. i.e. 5 Star Medical, Inc

Report •

#9
December 4, 2009 at 06:24:46
 Hi,I took the main list on sheet 1, and selected the company names in column B (B1 to B2234, including heading)I used Data - Filter - Advanced Filter, with copy to another location and unique records only.This gave me 580 unique company names which I placed in column G.As the unique, filtered company names in column G were found to have spaces after the names, the names were copied into column F using =TRIM(G2). This was dragged down column F to the bottom of the data in column G.Then the column F data was selected, then Copy-Paste Special - Values, to allow column G to be deleted.In column C of sheet 1 there is a list of company names labeled as Company[unique ones only]To identify companies in this list that did not appear in the company names in column F, i.e., companies with a category listing, I put the following formula in column D D2 contains: =IF(ISERROR(VLOOKUP(C2,\$F\$2:\$F\$581,1,FALSE)),"Not listed", "Listed")This resulted in 147 company names which were not in the list in column B.As a result of this I have a question:Is the list of categories and companies on sheet 1 in columns A (Categories) and B (company names) the definitive list of categories that each company provides. Regards

Report •

#10
December 4, 2009 at 06:40:51
 Yes it is.

Report •

#11
December 5, 2009 at 07:16:18
 Hi,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 B719In 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:S719After the paste, cell S719 contains`=IF(SUMPRODUCT((Sheet1!\$B\$2:\$B\$2234=Results!\$A719)*(Sheet1!\$A\$2:\$A\$2234=Results!S\$1))>0, "Y","N")`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`=IF(SUMPRODUCT((Company=Results!\$A2)*(Category=Results!C\$1))>0, "Y","N")`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).Regards

Report •