If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Match formula to match values in multiple columns
Hi all, does any friend know that how can I make below formula work
MATCH(A2,$K$2:$M$30,0) |
#2
|
|||
|
|||
Match formula to match values in multiple columns
Maybe you can use =countif()
=if(countif($k$2:$M$30,a2)=0,"not there","it's there at least once") K wrote: Hi all, does any friend know that how can I make below formula work MATCH(A2,$K$2:$M$30,0) -- Dave Peterson |
#3
|
|||
|
|||
Match formula to match values in multiple columns
Hi,
If you provide an example of your data and the results you are looking for, we can help "K" wrote: Hi all, does any friend know that how can I make below formula work MATCH(A2,$K$2:$M$30,0) . |
#4
|
|||
|
|||
Match formula to match values in multiple columns
Check your other post. You dont need to multi-post
"K" wrote: Hi all, does any friend know that how can I make below formula work MATCH(A2,$K$2:$M$30,0) . |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|