Returning remaining values: Two-Index Comparison, Elimination of List Items

    Hello all,


    This is my first post to the help forum, so I hope I have made what I am looking for as clear as possible and that it might help others with similar questions. It is hard to find help for this specific problem because of the way it is worded. Especially because I am not looking to match items but to find list items that have not yet been taken into consideration.



    I have attached the example I am dealing with to this post.


    The objective is to return the elements that remain from the index E152-E166, in order, compared to the opposing index G153-G162, the list of items that have already been selected. The resulting list location should be in cells E169 and down until there are no longer any items that have not been considered.


    So I am trying to use one formula that I can paste from E169, E170, E171 etc and when there are no more items left to return, it leaves all additional cells blank. IE. the excel command "".


    Thanks so much for the assistance,


    Andrew

    Try this Array* Formula in E169, copied down:


    =IFERROR(INDEX($E$152:$E$166,SMALL(IF(ISNA(MATCH($E$152:$E$166,$G$153:$G$162,0)),ROW(($E$152:$E$166)-ROW(($E$152)+1),ROWS(E$169:E169))),"")


    [arf]*[/arf]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Thanks for the quick response NBVC.


    Excel is telling me there is a problem with the formula, then asks if I mean to type in a formula as opposed to just text. Might you know what part of formula you pasted needs to be modified? I tried as an array and normal and had no luck with either attempt.


    Uploaded the error message.


    Andrew

    Sorry about that a couple of extra opening parentheses in there....


    Try:


    =IFERROR(INDEX($E$152:$E$166,SMALL(IF(ISNA(MATCH($E$152:$E$166,$G$153:$G$162,0)),ROW(E$152:$E$166)-ROW($E$152)+1),ROWS(E$169:E169))),"")

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    NBVC,


    That is as close to a work of art as I have seen as far as excel formulas go. Or some type of operational mathematics poetry. Either way, both subject matters make my head spin, leaving me incredibly frustrated and confused.


    I decided to track the steps using the excel formula operators and wrote them down and attached them to this response, simply to show that I am attempting to understand it and not just sitting back and having you do all the work for me. I would like to be able to construct these on my own as well.


    I do understand the theory behind constructing the formulas, such as getting excel to identify the cells you are looking for by attaching a number or word value that only those cells have in common [For example, a "1" or "FALSE"], and then instructing excel to return the text from the first intersecting row that has a cell value of "1" or "FALSE," depending on what you want to accomplish. Unfortunately, I just get terribly confused in the process as this is not a particular strength of mine!


    Would you mind explaining the logic in plain English from each step to the next so I can understand how you applied the theory to make the formula work as I had requested? I would be happy to make a new topic post and send you the link as that might be considered a better way to help others who struggle applying these same concepts in excel.


    Thanks a ton,


    Andrew

    This inner part INDEX($E$152:$E$166,SMALL(IF(ISNA(MATCH($E$152:$E$166,$G$153:$G$162,0)),ROW(E$152:$E$166)-ROW($E$152)+1),ROWS(E$169:E169))) is like a Vlookup for multiple results.



    It uses the Index() function which requires you to index the table or column to extract from and it requires the Row number to extract from which is gotten from this part: SMALL(IF(ISNA(MATCH($E$152:$E$166,$G$153:$G$162,0)),ROW(E$152:$E$166)-ROW($E$152)+1),ROWS(E$169:E169))


    The Small() function just allows us to step up and extract one match at a time, starting from the first match found. It will extract only if a there is no match found of items in range $E$152:$E$166 within range $G$153:$G$162 using the ISNA(MATCH()) configuration and then it will return the corresponding row number within the range. The IF() function checks if the ISNA(MATCH()) returns a TRUE and if so it goes to the ROW(E$152:$E$166)-ROW($E$152)+1 condition which returns row numbers where TRUE exists. The -ROW($E$152)+1 is added for robustness (in case you insert rows above, then the result won't skew).


    The last ROWS(E$169:E169) is the k factor for the Small() function, which is like a step factor in a For...next loop in VBA. As you copy the formula down you will see the second E169 change to E170, E171, etc...


    The CSE confirmation is because it is an array formula and you have to confirm with those keys to make these formulas work.


    Then you copy down... The only thing that changes copying down is the ROWS(E$169:E169) .. again to determine the step or row number we're in.


    The IFERROR() wrapped around the main formula will allow a blank to be returned instead of an error indicating that there are no more matches found.



    Hope this clarifies a bit.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login