Match formula to match values in multiple columns
 

Thanks lot Dave Peterson. Your formula works. What i was trying to
achive that i got data in three columns like see below

K L M.....col
XX YY GG
SS TT NN
RR VV AA
etc...

then i have data in column A like see below

A....col
XX
DD
SS

I needed some formula in column B to match column A values in columns
K, L and M to come back with result like see below

A B....col
XX Match
DD Dont Match
SS Match

sorry i didnt explain my question clearly as i was trying to keep it
short. The only thing i am trying to workout that how can i achive
same thing with macro. Like if i click button and column B should get
filled automatically. It will much appricated if any friend can help

Match formula to match values in multiple columns
 

I'd just insert the formula into the macro:

Dim wks as worksheet
dim LastRow as long

set wks = worksheets("Sheet1")

with wks
lastrow = .cells(.rows.count,"A").end(xlup).row
with .range("B2:B" & lastrow)
.formula = "=if(countif($k$2:$M$30,a2)=0,""no match"",""match"")"
.value = .value 'convert formulas to values???
end with
end with

Notice that the double quotes in the formula string are doubled. Something to
watch out for if/when you change that formula.




K wrote:

Thanks lot Dave Peterson. Your formula works. What i was trying to
achive that i got data in three columns like see below

K L M.....col
XX YY GG
SS TT NN
RR VV AA
etc...

then i have data in column A like see below

A....col
XX
DD
SS

I needed some formula in column B to match column A values in columns
K, L and M to come back with result like see below

A B....col
XX Match
DD Dont Match
SS Match

sorry i didnt explain my question clearly as i was trying to keep it
short. The only thing i am trying to workout that how can i achive
same thing with macro. Like if i click button and column B should get
filled automatically. It will much appricated if any friend can help

--

Dave Peterson

Match formula to match values in multiple columns
 

On Apr 21, 4:53*pm, Dave Peterson wrote:
I'd just insert the formula into the macro:

Dim wks as worksheet
dim LastRow as long

set wks = worksheets("Sheet1")

with wks
* lastrow = .cells(.rows.count,"A").end(xlup).row
* with .range("B2:B" & lastrow)
* * .formula = "=if(countif($k$2:$M$30,a2)=0,""no match"",""match"")"
* * .value = .value 'convert formulas to values???
* end with
end with

Notice that the double quotes in the formula string are doubled. *Something to
watch out for if/when you change that formula.





K wrote:

Thanks lot Dave Peterson. *Your formula works. *What i was trying to
achive that i got data in three columns like see below

K * *L * M.....col
XX *YY GG
SS TT NN
RR VV AA
etc...

then i have data in column A like see below

A....col
XX
DD
SS

I needed some formula in column B to match column A values in columns
K, L and M to come back with result like see below

A * * *B....col
XX * *Match
DD * Dont Match
SS * Match

sorry i didnt explain my question clearly as i was trying to keep it
short. *The only thing i am trying to workout that how can i achive
same thing with macro. Like if i click button and column B should get
filled automatically. *It will much appricated if any friend can help

--

Dave Peterson- Hide quoted text -

- Show quoted text -

thanks lot dave