Help regarding Formula

Microsoft Microsoft excel 2007 (pc)
December 3, 2009 at 02:12:44
Specs: Windows XP
I have an excel list.

There are different categories of business types. total of 17 different category. Problem is that most of the categories have few unique business. I want to have only the unique business. But I want it in a different way.

WHAT I HAVE CURRENTLY::Its an example. Real categories and business names are different.

CATEGORY[A2] -- BUSINESSNAME[A2]
------------------------ ---------------------------------
AA1------------------BUS1
AD6------------------BUS4
AB2------------------BUS2
AB1------------------BUS4
AA1------------------BUS3
AD6------------------BUS1
AA1------------------BUS4

WHAT I WANT::

AA1--AB2---AD6---BUSINESSNAME
Y-----N-----Y------BUS1
N-----Y-----N------BUS2
Y-----N-----N------BUS3
Y-----Y-----Y------BUS4

Total number of actual business records--768
Total Categories : 17

I heard that it can be done by lookup or vlookup formula. But I failed to use them properly.

Please help if anybody have any idea solve this.

Thanks


See More: Help regarding Formula

Report •


#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 columns
Use 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 1
Business 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
Hi Humar,

I think I have wrongly described what I need.

Now I have extracted all the categories and companies in a excel file.

There are total of 17 unique category and total of 2234 companies. But there are 800 unique company records. In many cases same company listed in more than one category.

I have the unique company list in another excel sheet.

So, now what I want::

Compare two list and add the respective category/categories for all companies in the unique list.

I am trying to giving an example:

categories+company list[this list includes duplicates]
-----------------------------------
COMPUTED TOMOGRAPHY AADCO Medical, Inc
COMPUTED TOMOGRAPHY Accelarad
COMPUTED TOMOGRAPHY Accusoft Pegasus
CONTRAST AGENTS, DRUGS AND PHARMACEUTICALS ACR Image Metrix
CONTRAST AGENTS, DRUGS AND PHARMACEUTICALS b.e.imaging GmbH
CONTRAST AGENTS, DRUGS AND PHARMACEUTICALS Bayer HealthCare Pharmaceuticals
CONTRAST AGENTS, DRUGS AND PHARMACEUTICALS Beckelmann
CONTRAST AGENTS, DRUGS AND PHARMACEUTICALS Bracco
CONTRAST AGENTS, DRUGS AND PHARMACEUTICALS Cone Instruments
CONTRAST AGENTS, DRUGS AND PHARMACEUTICALS Covidien
CONTRAST AGENTS, DRUGS AND PHARMACEUTICALS DMS Health Technologies
DARKROOM EQUIPMENT AND SUPPLIES Atlantis Worldwide, LLC
DARKROOM EQUIPMENT AND SUPPLIES Bar-Ray Products, Inc
DARKROOM EQUIPMENT AND SUPPLIES Beckelmann
DARKROOM EQUIPMENT AND SUPPLIES CIRS, Inc
DARKROOM EQUIPMENT AND SUPPLIES CNMC Co., Inc
DARKROOM EQUIPMENT AND SUPPLIES Codonics
DARKROOM EQUIPMENT AND SUPPLIES Cone Instruments
DARKROOM EQUIPMENT AND SUPPLIES DMS Health Technologies
DEPARTMENTAL MANAGEMENT 3M Health Information Systems, Inc
DEPARTMENTAL MANAGEMENT AADCO Medical, Inc
DEPARTMENTAL MANAGEMENT Accelarad
DEPARTMENTAL MANAGEMENT Accusoft Pegasus
DEPARTMENTAL MANAGEMENT Advanced Mobility
DEPARTMENTAL MANAGEMENT AFC Industries, Inc
DEPARTMENTAL MANAGEMENT Agfa HealthCare
DEPARTMENTAL MANAGEMENT A-Life Medical
DEPARTMENTAL MANAGEMENT American Institute of Architects (AIA-AAH)
-------------------------------------------------------------------------------------
Unique company list
-------------------------------
CIRS, Inc
Conmedi Limited
Accessential
AccuSync Medical Research Corp
ADVANCE
Advanced Imaging Research/Sree Medical Systems
Advanced Mobility
AEA Technology, Inc
Aerospace Composite Products
AFIP - Armed Forces Institute of Pathology
AHRA: The Association for Medical Imaging Management
Alliance for Radiation Safety in Pediatric Imaging
Alliance Storage Technologies, Inc.
American Association for Women Radiologists (AAWR)
American Association of Medical Dosimetrists
American Express OPEN
American Institute of Architects (AIA-AAH)
American Society of Neuroradiology (ASNR)
-------------------------------------------------------------------------------------

Is it possible?

If you need any more details please let me know.

Will be waiting for your reply.

Thanks,
Soumya


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, Inc
COMPUTED TOMOGRAPHY------ Accelarad
COMPUTED TOMOGRAPHY------ Accusoft Pegasus
CONTRAST AGENTS, DRUGS AND PHARMACEUTICALS------ ACR Image Metrix
CONTRAST AGENTS, DRUGS AND PHARMACEUTICALS------ b.e.imaging GmbH
CONTRAST AGENTS, DRUGS AND PHARMACEUTICALS------ Bayer HealthCare Pharmaceuticals
CONTRAST AGENTS, DRUGS AND PHARMACEUTICALS------ Beckelmann
CONTRAST AGENTS, DRUGS AND PHARMACEUTICALS------ Bracco
CONTRAST AGENTS, DRUGS AND PHARMACEUTICALS------ Cone Instruments
CONTRAST AGENTS, DRUGS AND PHARMACEUTICALS------ Covidien
CONTRAST AGENTS, DRUGS AND PHARMACEUTICALS------ DMS Health Technologies
DARKROOM EQUIPMENT AND SUPPLIES------ Atlantis Worldwide, LLC
DARKROOM EQUIPMENT AND SUPPLIES------ Bar-Ray Products, Inc
DARKROOM EQUIPMENT AND SUPPLIES------ Beckelmann
DARKROOM EQUIPMENT AND SUPPLIES------ CIRS, Inc
DARKROOM EQUIPMENT AND SUPPLIES------ CNMC Co., Inc
DARKROOM EQUIPMENT AND SUPPLIES------ Codonics
DARKROOM EQUIPMENT AND SUPPLIES------ Cone Instruments
DARKROOM EQUIPMENT AND SUPPLIES------ DMS Health Technologies
DEPARTMENTAL MANAGEMENT------ 3M Health Information Systems, Inc
DEPARTMENTAL MANAGEMENT------ AADCO Medical, Inc
DEPARTMENTAL MANAGEMENT------ Accelarad
DEPARTMENTAL MANAGEMENT------ Accusoft Pegasus
DEPARTMENTAL MANAGEMENT------ Advanced Mobility
DEPARTMENTAL MANAGEMENT------ AFC Industries, Inc
DEPARTMENTAL MANAGEMENT------ Agfa HealthCare
DEPARTMENTAL MANAGEMENT------ A-Life Medical
DEPARTMENTAL 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:
A
There are only
800 unique companies and
17 categories

B
Some companies provide materials or services in more than one category.

C
The total of 2234 companies you refer to are records containing a company name and a category,

D
the 2234 records contain many duplicates of a company's name, but no duplicates of category plus company name

Regards


Report •

#7
December 4, 2009 at 05:01:46
Hi,

To help me understand this, can you confirm:
A
There are only 744 unique companies and 17 categories - TRUE [changes in the company number. Its final]

B
Some companies provide materials or services in more than one category.-TRUE

C
The 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.

D
the 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.

Link : http://www.yousendit.com/download/M...

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 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(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 •


Ask Question