Solved: vlookup non-contiguous ranges

[goodwin57]

I'd like a worksheet_selectionChange(By Val Target as Range) macro to:

On a change in the range D4:Q300

Delete all values in columns B and C

Set column b values with reference to formula:
For each cell going down column B.

if the lookup of: b (offset-1 [i.e. a]) is in the range D:D, f:f,h:h,j:j,L:l,N:n,P:P then print an x in column b for each occurence of A in that range.

if c offset by -2 (i.e. a) is in the range as above except offset by 1 then do the same.


Any suggestions would be much appreciated

[mdmackillop]

Formula solution
=REPT("x",COUNTIF(D:D,A1)+COUNTIF(F:F,A1)+COUNTIF(H:H,A1)+COUNTIF(J:J,A1)+COUNTIF(L: L,A1)+COUNTIF(N:N,A1)+COUNTIF(P:P,A1))

I don't follow this bit


if c offset by -2 (i.e. a) is in the range as above except offset by 1 then do the same.

[grichey]

haha pwnt by the emoticons yet again.

[Bob Phillips]

I don't follow this bit

He means that if C = A, do the same on E:E, G:G, I:I, etc.

[mdmackillop]

Delete all values in columns B and C


C has been cleared.

[Bob Phillips]

So has B, being a bit more accurate I should say ... He means that if C offset -2, that is A, do the same against E:E, G:G, I:I, etc.

[mdmackillop]

So has B, being a bit more accurate I should say ... He means that if C offset -2, that is A, do the same against E:E, G:G, I:I, etc.
In that case, it's the same formula with the first COUNTIF deleted. (I think!)

[goodwin57]

Thank you so much for your responses. xld thanks for the interpretation - it was completely correct, sorry I wasn't explicit in my original post. The second countif (in column c) used column A as its condition, counting in columns e,g,i,k,m,o,q. Problem solved anyway, so thank you :)