I'm going crazy looking for this formula. I've tried vlookup, match, and index but cant find the perfect formula to give me what im looking for. So here is what i am looking to accomplish:
Inspector A B C D
Room #
1 T T T F
2 F T F T
3 T F F T
And this is my data
room Inspector
1 A
1 B
1 C
2 B
2 D
3 A
3 D
My mission is to check off which inspector checked each room so all im looking for is a true or false i can convert to a check or X. I have the inspectors names on a side column as well. I understand each inspector needs its own formula, but i need a formula that will go through my data, find the reference room, once that is true it will look for the inspectors that have inspected that room and look up if its true for a specific inspector. It is quite difficult to explain without sending over the worksheet.
If anyone can help it would be huge. I have 15000 items and 1000 rooms and the worksheet needs to be done by today.
Last edited by NBVC; 09-15-2010 at 11:14 AM.
Assuming your raw data is in Sheet1, A1:B8
and then your table is in Sheet2, A1 with Room numbers starting in A2 and Inspectors starting in B1.. then in B2:
=LEFT(SUMPRODUCT(--(Sheet1!$A$2:$A$8=$A2),--(Sheet1!$B$2:$B$8=B$1))>0)
copied down and across.
or in XL2007
=LEFT(COUNTIFS(Sheet1!$A$2:$A$8,$A2,Sheet1!$B$2:$B$8,B$1)>0)
adjust ranges to suit...
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
If you can live with adding an extra column to your list of data, which concatenates the room number and the inspector name then you can do this using a VLOOKUP.
I've coded your sample data into the attached workbook, so you can see what I'm talking about.
You are freaking amazing!!!
Thank you so much for your quick help!
Ok one last twist. Instead of the inspectors i actually have their corresponding agencies. Each agency has 1,2, or 3 inspectors. your formula works flawless with a single inspector but as soon as i add 2 inspectors i think it searches for a match for both where some rooms only have one or the other. Is there a way to change it to an OR. For instance my current forumula reads:
Working formula: =LEFT(COUNTIFS($R$4:$R$13189,$A14,$S$4:$S$13189,$L$6)>0)
Non working : =LEFT(COUNTIFS($R$4:$R$13189,$A14,$S$4:$S$13189,$L$7:$L$8)>0)
The L column is the Inspectors. the working forumla is only looking for L6 and the non working is looking for L7 and L8. How can i rewrite the formula to allow it to look for either L7 or L8?
Can you post a sample so we can better visualize what you mean?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
or maybe this might be what you are after?
=LEFT(SUMPRODUCT(--($R$4:$R$13189=$A14),--(ISNUMBER(MATCH($S$4:$S$13189,$L$6:$L$8,0))))>0)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Here it is attached.
for instance. Populous has 5 reviewers. But i need it to show true if even one of them has inspected the room. When i tired to put $L$12:$L$16 it said false if only one of them had been in there. i can only assume its because it was looking for all 5 reviewers to match the corresponding room.
that formula worked! you must be the most efficient person in the world!
Not sure if it would be much more efficient, but if you kept the headings in F3:H3 consistent with the items in column K, then you could maybe use:
in F4 and copied down the column and over=LEFT(SUMPRODUCT(($K$5:$K$16=F$3)*(ISNUMBER(MATCH(LOOKUP($A4,$R$4:$S$13189),$L$5:$L$16,0))))>0)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks