Solved

Revise Excel INDEX formula

Posted on 2013-06-19
16
312 Views
Last Modified: 2013-06-29
Below is the formula that we written prior.  It’s not evaluating the logic correctly.  (I omitted a logical check with I requested it earlier.)
 
=IF(ISNA(IF(COUNTIF('Selected VDOTs'!$B$2:$B$571,$A193)=1,IF(VLOOKUP(A193,'Selected VDOTs'!$B$2:$M$571,12,0)=0,"In Flight",VLOOKUP($A193,'Selected VDOTs'!$B$2:$M$571,12,0)),IF(COUNTIF('Selected VDOTs'!$B$2:$B$571,A193)=COUNTIFS('Selected VDOTs'!$B$2:$B$571,$A193,'Selected VDOTs'!$G$2:$G$571,VLOOKUP($A193,'Selected VDOTs'!$B$2:$M$571,12,0)),IF(VLOOKUP($A193,'Selected VDOTs'!$B$2:$M$571,12,0)=0,"In Flight",VLOOKUP($A193,'Selected VDOTs'!$B$2:$M$571,12,0)),IF(COUNTIFS('Selected VDOTs'!$B$2:$B$571,$A193,$G$2:$G$571,0)>0,"In Flight","Changes required")))),"",(IF(COUNTIF('Selected VDOTs'!$B$2:$B$571,$A193)=1,IF(VLOOKUP(A193,'Selected VDOTs'!$B$2:$M$571,12,0)=0,"In Flight",VLOOKUP($A193,'Selected VDOTs'!$B$2:$M$571,12,0)),IF(COUNTIF('Selected VDOTs'!$B$2:$B$571,A193)=COUNTIFS('Selected VDOTs'!$B$2:$B$571,$A193,'Selected VDOTs'!$G$2:$G$571,VLOOKUP($A193,'Selected VDOTs'!$B$2:$M$571,12,0)),IF(VLOOKUP($A193,'Selected VDOTs'!$B$2:$M$571,12,0)=0,"In Flight",VLOOKUP($A193,'Selected VDOTs'!$B$2:$M$571,12,0)),IF(COUNTIFS('Selected VDOTs'!$B$2:$B$571,$A193,$G$2:$G$571,0)>0,"In Flight","Changes required")))))
 
 
 
Overview:
Two tabs in the spreadsheet are related to this formula: The Key Control tab where the formula will be stored and the “Selected VDOTs” tab where data will be evaluated.
 
The formula will evaluate the value in Column A (Control ID) on the Key Control tab against the related rows for Column A’s value on the Selected VDOTs tab.
 
The columns on the Selected VDOTs tab that must be evaluated are: “Col B-stores the value from Column A (Control ID) [There will be multiple instances of this value because more than one test id may be linked to a Control iD.]” and “Column M – stores the test result values.]  
 
[When the formula above was first proposed, I thought the test Id was relative.  I no longer think column G (Test ID name) is relative for this evaluation.]
 
The formula must perform the below logic:
If the value in “Control ID/Column A” on the Key Control tab is not found on the Selected VDOTs tab, return a blank value.
If all of the rows in Selected VDOT/Column M for any given “Control ID/Column A” value contains blanks, return a blank value.
If all of the rows in Selected VDOT/Column M for any given “Control ID/Column A” values match, then return that value.    
If any of the rows in Selected VDOT/Column M for any given “Control ID/Column A” values contain “In Flight”, then return “In Flight”.
If all of the rows in Selected VDOT/Column M for any given “Control ID/Column A” values contain “Validated” or “Changes Required”, then return “Changes Required”.    
 
The file is attached...
Key-Control-Tracker-061613-v3.xls
0
Comment
Question by:Feisty472
  • 7
  • 4
16 Comments
 
LVL 14

Expert Comment

by:Faustulus
ID: 39261982
Hello Feisty472,
With all due respect, you will never see the day that this workbook serves you. Instead, you will live to serve this workbook. Every minute spent on perpetuating its life is a minute wasted.
Let me help you find a VBA replacement for the dysfunctional formula and put you on a path to replace most formulas in this workbook with code, if not all.
0
 
LVL 81

Expert Comment

by:byundt
ID: 39262343
Rather than wrapping half of your ginormous formula inside ISNA, why not use IFERROR to return the blank?

Your stated logic fails to cover the possibility that column M contains differing values that aren't all Validated or Changes Required. I decided to return "Review" in such situations.

The resulting formula is still so large you will be challenged to maintain it, but...
=IFERROR(IF(COUNTIF('Selected VDOTs'!$B$2:$B$543,$A3)=COUNTIFS('Selected VDOTs'!$B$2:$B$543,$A3,'Selected VDOTs'!$M$2:$M$543,VLOOKUP($A3,'Selected VDOTs'!$B$2:$M$543,12,FALSE)),VLOOKUP($A3,'Selected VDOTs'!$B$2:$M$543,12,FALSE) & "",IF(COUNTIFS('Selected VDOTs'!$B$2:$B$543,$A3,'Selected VDOTs'!$M$2:$M$543,"In Flight"),"In Flight",IF(SUM(COUNTIFS('Selected VDOTs'!$B$2:$B$543,$A3,'Selected VDOTs'!$M$2:$M$543,{"Validated","Changes Required"}))=COUNTIF('Selected VDOTs'!$B$2:$B$543,$A3),"Changes Required","Review"))),"")
0
 
LVL 81

Expert Comment

by:byundt
ID: 39262479
I probably should have mentioned that my suggested formula does not need to be array-entered.

And if you are willing to add some auxiliary columns for the results of the COUNTIF and VLOOKUP (perhaps to columns W and X), then you could simplify the formula to:
=IFERROR(IF(W3=COUNTIFS('Selected VDOTs'!$B$2:$B$543,$A3,'Selected VDOTs'!$M$2:$M$543,X3),X3 & "",IF(COUNTIFS('Selected VDOTs'!$B$2:$B$543,$A3,'Selected VDOTs'!$M$2:$M$543,"In Flight"),"In Flight",IF(SUM(COUNTIFS('Selected VDOTs'!$B$2:$B$543,$A3,'Selected VDOTs'!$M$2:$M$543,{"Validated","Changes Required"}))=W3,"Changes Required","Review"))),"")
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:Feisty472
ID: 39268565
Hey  Byundt,

First of all, thanks for the offer to write a VBA replacement.  The spreadsheet is actually only a temporary situation.  We have developers working on a permanent solution.  I will keep you in mind if that falls through!

Secondly, i love that fact that you were able to *almost* achieve the needed results without an Index.  Yay you!!  


Finally, regarding the formula you provided and your comments:

=IFERROR(IF(COUNTIF('Selected VDOTs'!$B$2:$B$543,$A3)=COUNTIFS('Selected VDOTs'!$B$2:$B$543,$A3,'Selected VDOTs'!$M$2:$M$543,VLOOKUP($A3,'Selected VDOTs'!$B$2:$M$543,12,FALSE)),VLOOKUP($A3,'Selected VDOTs'!$B$2:$M$543,12,FALSE) & "",IF(COUNTIFS('Selected VDOTs'!$B$2:$B$543,$A3,'Selected VDOTs'!$M$2:$M$543,"In Flight"),"In Flight",IF(SUM(COUNTIFS('Selected VDOTs'!$B$2:$B$543,$A3,'Selected VDOTs'!$M$2:$M$543,{"Validated","Changes Required"}))=COUNTIF('Selected VDOTs'!$B$2:$B$543,$A3),"Changes Required","Review"))),"")

Three things:

1. [Byundt] Your stated logic fails to cover the possibility that column M contains differing values that aren't all Validated or Changes Required. I decided to return "Review" in such situations.  
[Feisty472] The column values will only be <Blank>, In-Flight, Changes Required or Validated.  This should be no need to return Review if the logic holds.

2.  A requirement was overlooked: If all of the rows in Selected VDOT/Column M for any given “Control ID/Column A” value contains blanks, return a blank value. [Feisty472]  Currently, it will return "Review".  Please just simply return a blank.

3.  I did forget a piece of logic:  If any of the rows in Selected VDOT/Column M for any given “Control ID/Column A” value contains any Status along with a blank row, return "In-Flight".  (To illustrate: If there are three rows, two contain "Validated" and one is still blank, return "In-Flight".  If there are nine rows, four contain Changes Required, three contain "Validated" and two are blank, return "In-Flight".)

Many, many thanks for you efficiency!
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39268818
The column values will only be <Blank>, In-Flight, Changes Required or Validated.  This should be no need to return Review if the logic holds.
Column M also contains 210 cells that return #N/A error value. Consider the case when column B contains BKCY.PAY.1.2. On row 19 column M has "Changes Required", while on row 29 it has #N/A error value. The column M values don't match, so my formula returns "Review". I think it better to continue returning "Review" until your issues with #N/A are addressed.

I agree that my formula wasn't working if column M contained all blanks. I needed to concatenate an empty string in one more place.

Making also the change for your new condition, you might consider:
=IFERROR(IF(COUNTIF('Selected VDOTs'!$B$2:$B$543,$A3)=COUNTIFS('Selected VDOTs'!$B$2:$B$543,$A3,'Selected VDOTs'!$M$2:$M$543,VLOOKUP($A3,'Selected VDOTs'!$B$2:$M$543,12,FALSE) & ""),VLOOKUP($A3,'Selected VDOTs'!$B$2:$M$543,12,FALSE) & "",IF(SUM(COUNTIFS('Selected VDOTs'!$B$2:$B$543,$A3,'Selected VDOTs'!$M$2:$M$543,{"In Flight",""})),"In Flight",IF(SUM(COUNTIFS('Selected VDOTs'!$B$2:$B$543,$A3,'Selected VDOTs'!$M$2:$M$543,{"Validated","Changes Required"}))=COUNTIF('Selected VDOTs'!$B$2:$B$543,$A3),"Changes Required","Review"))),"")
Key-Control-Tracker-061613-v3Q28.xlsx
0
 

Author Comment

by:Feisty472
ID: 39270106
Ahhhh...now i understand what you are talking about when you reference "#N/A".  When you send the formula, I change the actual column in which the formula will reference from M to L.  "M" is my working column that has the VLOOKUP in it.  Because of some manual checking i have to do, I check each week to see if any values changed from the week before.  If they have, I evaluate them as an audit of our new employee.  If the change is correct, I move the 'official' status to Column L.  Column L will never have "#N/A".  But, many thanks for the heads up.

I'm getting ready to build out another spreadsheet.  If I run into stumbling blocks on creating the formula, like I did with this one, how can I work with you?  (Some of the other guys weren't as good or quick to respond as you.)
0
 

Author Comment

by:Feisty472
ID: 39270112
I've requested that this question be closed as follows:

Accepted answer: 0 points for Feisty472's comment #a39270106

for the following reason:

This expert was not only efficient in the formulas he created, but he is also extremely timely and patient.  I would love to work with him exclusively!!  :-)
0
 

Author Comment

by:Feisty472
ID: 39270108
Many thanks!
0
 

Author Comment

by:Feisty472
ID: 39270115
I don't understand what this "Close Request Pending" is all about.  I must have clicked the wrong button.  I wanted to Grade and Accept the solution.
0
 

Author Comment

by:Feisty472
ID: 39270142
I've requested that this question be closed as follows:

Accepted answer: 0 points for Feisty472's comment #a39270115

for the following reason:

Ok..If I have to type how much I loved this guy, I am going to lose it!!  

I understand that I couldn't simply use a COUNTIFS to evaluate the conditions.  I needed to use deeply embedded formulas.  My expert helped me understand this..
0
 
LVL 81

Expert Comment

by:byundt
ID: 39270135
If you have trouble implementing the solution from this thread in your real workbook (or in your next workbook), please continue to post in this thread. Doing so will increase its value in the Experts Exchange database--and you should find that all of the regulars in the Excel Zone are quite willing to work with you in that fashion.

While I'm flattered that you would like to work with me exclusively in the future, site rules discourage that practice. Rest assured, however, if I see a new question from you, I would be pleased to try to help you again.

Brad
0
 

Author Comment

by:Feisty472
ID: 39272379
Ohhh..I see now.  I guess it's not as intuitive as I thought.  I will correct that now.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question