Skipping a cell and moving on to the next, with no blanks in betwe

[SteveC]

Three worksheets

Worksheet 1 has values in A7:A2000
Worksheet 2 has values in B8:B18
Worksheet 3 is where I want a formula to do the following:

Worksheet3 Cell A1, for example:
Formula: If worksheet1!A10 does not equal worksheet2!b8:18, then return the
value in worksheet1!A10

Then if I drag down the formula to worksheet3!A2 , and if Worksheet1!A11
DOES equal any values in worksheet2!b8:b18, do not leave blank, but instead
fill return a value in worksheet1!A12 but only if worksheet1!A12 does not
equal any criteria in worksheet2!b8:b18, etc...

so I can pull down the formula a few hundred rows, omitting those values
that are attached to some specififed criteria as listed in worksheet 2.

Hope this makes sense... I appreciate your help... thanks...

 

[Miguel Zapico]

You may use this one:
=IF(ISERROR(MATCH(worksheet1!A10,worksheet2!$B$8:$B$18,0)),worksheet1!A10,"")
This will check if the value in worksheet1 is present in the worksheet2
range, returning that value if so, and blank if not.

Hope this helps,
Miguel.

 

[SteveC]

Thanks... I know how to leave blank, but wondering if instead of leaving
blank it can head on to the next cell... so there are no blanks, only values
filled in... I suppose it's an autofilter macro problem, but was hoping to
avoid that...

 

[Miguel Zapico]

You are right, I misunderstood the question. With the requirement you have,
maybe this workaround can work:
Insert the following array formula (with CTR+SHIFT+ENTER) in worksheet 3,
cell B2 (B1 must be blank or 0):
=B1 + MATCH(TRUE,ISERROR(MATCH(INDIRECT("worksheet1!A" & 7 + B1 &
":A2000"),worksheet2!$B$8:$B$18,0)),0)
Copy and paste the formula over the column, do over 20 rows to see if it
works., later you can extend to the 2000 rows, or less if you know how much
data you expect.
On column A, starting at A2, write the formula:
=INDEX(worksheet1!$A$7:$A$2000,B1)
And copy it to match the column B. This should skip the values that match
the criteria in worksheet 2.

Miguel.

 

[SteveC]

Wow, outstanding, thanks. Works great.

If you have time, can you explain the logic? Index and match formulas
always confuse me. I supsect the values that show up in Col B represent rows
numbers, and Col A is retrieving values that the row numbers represent?

I do see what you are doing with the concatentate &. I never knew you could
use concatenate to reference names of worksheets or names of cells... very
cool.

 

[SteveC]

one thing to note: =INDEX(worksheet1!$A$7:$A$2000,B1)
In Cell A2 of worksheet3 I changed "B1" to "B2" to get it to work...

 

[SteveC]

Miguel, do you know of a way to sort the values returned from the index and
match formulas you provided? Or return the values in descending order? If
not, I have to copy paste value on another worksheet, and sort there.

Thanks for your help!

 

[Miguel Zapico]

Steve,

Yes, the index formula was wrong, good you could see and fix it.

The logic behind the formula is: I want to know the position of the
elements that are not present in the worksheet 2 list, but I need every
element, not just the first one. The MATCH formula returns the first value
found in the given range, so what the solution here uses INDIRECT to shorten
the range to look at, triming at the last value found and all the ones above
it.
I am using array formulas because of the inner MATCH. With that, and the
ISERROR formula, it creates an array of TRUE and FALSE values, that is what
the outer MATCH uses to give a position. In order to give the right values
to the INDEX function, I need to add the previous value found, that is the
part at the beginning of the formula.

I don't know if I have been clear enough, in my head may be clear but now
that I see it writen I am not sure about the expression. One conclusion of
this is that the results are hard to sort, as the indexes are absolute
positions on the list. You may not need to copy and paste values, a simple
reference (=A2) in a separate column should do the trick.

Miguel.

 

[SteveC]

Thanks for the explanation and your help.