Hello.
Background: I have 3 lists. List 1 is a column of unique identifiers. List 2 is a column of unique identifiers. List three is supposed to contain as many rows as the number of rows in List 1 * the number of rows in List 2. Each row in List three has as its unique identifier a value from List 1 concatenated with a value from List 2.
What my problem is: I want to determine, when looking at each row in list 1, if there is an corresponding entry in list 3 for every row in list 2.
Last edited by Philster; 03-17-2009 at 02:00 PM. Reason: No responses, trying to make it more tempting to solve
Sorry, let me restate that last statement:
What my problem is: I want a formula beside each element in list 1 to show, if there is not a corresponding entry in list 3 for any row in list 2, any one of the missing elements from list 2.
See example for an illustration.
Last edited by Philster; 03-13-2009 at 09:11 AM. Reason: clarity
Ok, my posts seem to have generated zero responses...maybe it was how I asked. Let me try again:
Ok I have one worksheet called Risks. New risks could be added later.
Risk Probability Impact All Apps covered? (for example)
App not Backed up Low Hi formula goes here 1
Data not Backed up Low Hi formula goes here 2
No Vendor Contract Med Low formula goes here 3
Not DR tested Low Low formula goes here 4
I have a 2nd worksheet called Applications. new apps could be added later.
Application Version Description
SMB 4.9 Central distribution
AVM 2.2 design app
FNT 8.0 renderer
I have a 3rd worksheet called Application-Risks
Application-Risk Application Risk responsible to check checkdate
SMB App
not Backed up SMB App not Backed up Joe Schmoe 2010-03-04
SMB Data
not Backed up SMB Data not Backed up Joe Schmoe 2010-03-09
SMB No
Vendor Contract SMB No Vendor Contract Jane Doe 2010-03-12
SMB Not DR
tested SMB Not DR tested Joe Schmoe 2010-03-04
FNT App
not Backed up FNT App not Backed up Frank Smith 2010-03-07
FNT Data
not Backed up FNT Data not Backed up Joe Schmoe 2010-03-04
FNT No
Vendor Contract FNT No Vendor Contract Jane Doe 2010-03-04
AVM No
Vendor Contract FNT No Vendor Contract Jane Doe 2010-03-04
the cells with formula goes here should either
1. indicate if Application-Risks have the if there is an app missing for this risk in the apps-risk table (Y/N), OR
2. indicate if the name of the first app that is missing in the App-risk table for a risk
-- whatever you find easiest. This will just tell me that there is something missing in Application-Risks, that I should fix. These lists are LONG, so this is why I need it.
For example, if the formula goes here indicates if the name of the first app that is missing in the App-risk table for a risk,then the table would read
Risk Probability Impact First App missing
App not Backed up Low Hi AVM
Data not Backed up Low Hi AVM
No Vendor Contract Med Low
Not DR tested Low Low FNT
Hope you can help.
For simplicity - working from your original sample file:
How practical the above will be on large datasets is obviously debatable.B1: =IF(COUNTIF($F$1:$F$10,$A1)=COUNTA($C$1:$C$3),"",INDEX($C$1:$C$3,MATCH(TRUE,INDEX(ISNA(MATCH($A1&" "&$C$1:$C$3,$E$1:$E$10,0)),0),0))) copied down to B4 D1: =IF(COUNTIF($G$1:$G$10,$C1)=COUNTA($A$1:$A$4),"",INDEX($A$1:$A$4,MATCH(TRUE,INDEX(ISNA(MATCH($A$1:$A$4&" "&$C1,$E$1:$E$10,0)),0),0))) copied down to D3
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Wow! I am soooo impressed! Seriously, THANK YOU!
I think I spoke too quickly. I realized that I only need to track risks for apps that are explicitly in-DR-scope. Essentially, there are 4 types of apps:
1) explicitly in-DR-scope "Y";
2) implicitly in-DR-scope "Implied";
3) Involved or impacted by DR "Involved";
4) not in-DR-scope "N".
I have created an example2 spreadsheet, and placed the previously suggested formula into it, and updated the headers.
I can figure out how to modify the formula in column First Missing Risk in the Apps list. However, I am do not know how to modify the formula in column First Missing App in the Risks list report on the first missing in-DR-scope "Y" app.
I have highlighted the problem cells. They show "", "d", "b". They should be "e", "", "e"
sorry, I saw that I made an error in the sheet. Here it is
If I've understood...
(apologies for delay - have not been online this afternoon)E3: =LOOKUP(REPT("Z",255),CHOOSE({1,2},"",INDEX($A$3:$A$10,MATCH(1,INDEX(($B$3:$B$10="Y")*ISNA(MATCH($A$3:$A$10&" "&$D3,$F$3:$F$21,0)),0),0)))) copied down
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
First off, you replied quite speedily, sir, and I thank you.
Secondly...in a word...wow...I am blown away by that.
I'm now curious...I tried the ($B$3:$B$10="Y")* logic, but obviously not the right way, because it caused Excel to consistently blow up over here. I probably mixed types some how. What I'm curious about is the first part, where I see you have changed it, and I am wondering why?
namely instead of
=IF(COUNTIF($H$3:$H$21,$D4)=COUNTA($A$3:$A$9),""
you changed it to
=LOOKUP(REPT("Z",255),CHOOSE({1,2},""
I am sure it's an improvement over the first method, but I don't understand why you chose what you chose...care to explain? I'm interested.
I changed the first part because the original logic is no longer valid... ie before we said:
conduct the calculation looking for "missing" items only if the frequency of the item being searched for <> count of items in the other table...
eg "1" appeared 8 times in the final table and there were 8 terms in the first table thus we know "1" exists for all.
We could do this because we knew that where the above did not hold true their must be a missing item to be found... this is no longer the case.
Using your most recent sample file - we can demo this with "2"
We know that 2 appears 7 times in our final table.
Similarly we know that our table 1 has 8 terms.
We know by our superlative powers of deduction that 1 item is missing...
However, by looking ourselves we can see that the missing item is "d" and we know that "d" is "N" not "Y".
So using the COUNTIF approach we'd have said - ok we need to find the item - but the result of the INDEX would have been an Error as no item in table1 meets our criterion of being both missing from the final table and having the "Y" criteria.
Make sense ?
The LOOKUP(2,1/...) approach may seem elegant but actually in cases such as this it's far from ideal.
Why ? Well, because in essence it now means that whereas before we were only conducting the potentially expensive "find the error" search on occasion (& where nec.) we're now processing it for each cell irrespective of whether it's warranted or not.
The reality is however that replacing the COUNTIF with something more "sophisticated" would actually necessitate an equally inefficient formula as the "find the error" formula so I opted to use LOOKUP on that basis.
IN terms of explaining the method itself - I've put a few things together on this in the past (as have others and more elegantly than myself I'm sure) but the below thread has a brief synopsis of the basics and a further link to a more detailed walkthrough:
http://www.excelforum.com/2252293-post5.html
EDIT:
use of CHOOSE is not documented in the above link - in essence the CHOOSE is used to generate a 2 value lookup_vector - the first value being our default return (Null string) and the second being the result of "find the error"
Last edited by DonkeyOte; 03-11-2010 at 03:32 PM.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thank you again for the detailed explanation. Appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks