lookup problem
Posted by Stephanie on June 29, 2001 2:23 PM
I have a table of companies where each company has multiple projects. The projects are numbered 1, 2, etc. If a company has more than one project it is listed more than once. (In other words, company name in column a, project number in column b. Column a has many duplicate entries because most companies have more than one project.)
What I want to do is find out:
How many companies that have a project #1 also have a project #2? I've been fooling around with different kinds of vlookups, offsets, etc, but I want this to work even if the data is not sorted by company name. Any ideas? I don't think I'm up to doing any VB today!
Posted by IML on June 29, 2001 2:36 PM
Could you simply get away with counting the number of time each company name occurs in A? For example,
=COUNTIF(A2:A100,"CompanyA")
If this would work, you could list your unique company names and have a list of the number of outstanding projects by tying this formula to the list?
Good luck.
Posted by IML on June 29, 2001 2:47 PM
After I re-read this, you are just after the number of companys with more than one, try this in your project column (this assumes they are only numbers)
=COUNT(B2:B100)-COUNTIF(B2:B100,1)
Posted by Stephanie on June 29, 2001 2:52 PM
The data looks like
companyA 1
companyA 2
companyB 1
companyC 1
companyC 2
companyC 3
etc.
Posted by IML on June 29, 2001 3:01 PM
On the surface,
=COUNTIF(b1:b100,2) should work. It won't work if say companyC completes project 2 before project 3 and project 2 is removed from the sheet?
good luck, I'm out of here for the week.
Posted by Aladin Akyurek on June 30, 2001 12:02 AM
Stephanie,
Lets assume your sample data to be in A1:B7.
{"companyA",1;"companyA",2;"companyB",1;"companyC",1;"companyC",2;"companyC",3;"companyB",3}
In C1 enter: Project 1 [ just a label ]
In D1 enter: 1
In E1 enter: 2
In C2 enter: Project 2
In D2 enter: 2
In E2 enter: 3
In C3 enter: N(Companies)
Enter all of the distinct company names from C4 on.
In D4 array-enter: =SUM(SUM(($A$1:$A$20=$C4)*($B$1:$B$20=D$1)))*(SUM(($A$1:$A$20=$C4)*($B$1:$B$20=D$2))) [ To array-enter this formula, you need to hit CONTROL+SHIFT+ENTER at the same time, not just ENTER. ]
Copy the above array-formula first to E2 then down up to the row of the last company in C.
In D3 enter: =SUM(D4:D6) [ Copy this to E3 ]
D3 now contains the number of companies that have Project #1 as well as Project #2,
E3 shows the number of companies that have Project #1 as well as Project #3.
The range where all these computations occur looks as:
{"Project 1",1,1;"Project 2",2,3;"N(Companies)",2,2;"companyA",1,0;"companyB",0,1;"companyC",1,1}
Aladin
===============
