K
K
Hi all, does any friend know that how can I make below formula work
MATCH(A2,$K$2:$M$30,0)
MATCH(A2,$K$2:$M$30,0)
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Dave Peterson
Maybe you can use =countif()
=if(countif($k$2:$M$30,a2)=0,"not there","it's there at least once")
=if(countif($k$2:$M$30,a2)=0,"not there","it's there at least once")
E
Eduardo
Hi,
If you provide an example of your data and the results you are looking for,
we can help
If you provide an example of your data and the results you are looking for,
we can help
J
Jacob Skaria
Check your other post. You dont need to multi-post
K
K
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
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
D
Dave Peterson
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.
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
K
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.
--
Dave Peterson- Hide quoted text -
- Show quoted text -
thanks lot dave