• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Comment through Macro

A

Abhijeet

Active Member
Hi

I have data in excel Column A number Column B Name i have list of people & access if that match with Column A & B then put comment in Column C. In attach file i mention problem & what i need that result also.Please tell me how to do this
 

Attachments

Try this..
While i am looking for other alternative also.

Code: 
=IF(OR(A2<>"",B2<>""),IF(B2=IFERROR(LOOKUP(A2,$H$3:$H$8,$H2),FALSE),"He has Access",IF(B2=IFERROR(LOOKUP(A2,$I$3:$I$8,$I$2),FALSE),"He has Access",IF(B2=IFERROR(LOOKUP(A2,$J$3:$J$8,$J$2),FALSE),"He has Access","Do not Have Access"))),"")
 
Or this too!!!

Code: 
=IF(OR(A2="",B2=""),"",IF(IFERROR(IF(MATCH(A2,$H$3:$H$8),$H$2),IFERROR(IF(MATCH(A2,$I$3:$I$8,0),$I$2),IFERROR(IF(MATCH(A2,$J$3:$J$8,0),$J$2),"Do not Have Access")))=B2,"He has Access","Do not Have Access"))
 
Deepak said:
Try this..
While i am looking for other alternative also.

Code: 
=IF(OR(A2<>"",B2<>""),IF(B2=IFERROR(LOOKUP(A2,$H$3:$H$8,$H2),FALSE),"He has Access",IF(B2=IFERROR(LOOKUP(A2,$I$3:$I$8,$I$2),FALSE),"He has Access",IF(B2=IFERROR(LOOKUP(A2,$J$3:$J$8,$J$2),FALSE),"He has Access","Do not Have Access"))),"")
Click to expand...

This is better & small too!!!

Code: 
=IF(OR(A2="",B2=""),"",IF(IFERROR(LOOKUP(A2,$H$3:$H$8,$H$2),IFERROR(LOOKUP(A2,$I$3:$I$8,$I$2),IFERROR(LOOKUP(A2,$J$3:$J$8,$J$2),"Do not Have Access")))=B2,"He has Access","Do not Have Access"))
 
Hi Deepak Formula is good but i have 30 to 40 People access list so it very lengthy formula then so please give me alternate solution for this
 
Try this one..

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column < 3 And IsEmpty(Cells(Target.Row, 3)) And Target.Cells.Count = 1 Then
    Cells(Target.Row, 3).FormulaArray = "=IF(OR(ISBLANK($A" & Target.Row & ":$B" & Target.Row & _
        ")),"""",CHOOSE(ISNA(MATCH($A" & Target.Row & ",INDEX($H$2:$J$20,,MATCH($B" & Target.Row & _
        ",$H$2:$J$2,0)),0))+1,""He Has"",""Do Hot Have"")&"" Access"")"
    End If
End Sub
 

Attachments

You must log in or register to reply here.
Back
Top