Macro to compare against multiple criteria two sheets

Hi Guys,


I would need a macro that can check if for a given code in Column A all the criteria/values (columns B:C:D etc.) are the same for the same code in another sheet of the workbook.
Could anyone help me please?




1.The same code can be repeated several times with different values.
2.The numbers of row of the two sheets can be different.
3.Some value-cells can be empty.
4.Criteria could be more then 3. (lets say 4-5)
5.I would like a column in the sheet-1 that gives back a "V" only if ALL the criteria for the given code match in the two sheets.
6.I tried the below formula with Index/Match in Array (Match with a boolean) but it doesn't work:

=INDEX(Sheet2!$A$2:$E$12,MATCH(1,(Sheet1!A2=Sheet2!$A$2:$A$12)*(Sheet1!B2=Sheet2!$B$2:$B$12)*(Sheet1!C2=Sheet2!$C$2:$C$12)*(Sheet1!D2=Sheet2!$D$2:$D$12),0),5)

Thanks everybody in advance.

Hi thanks so much for you help

I got an error - Ambiguous name detected:check-

Hi thanks for the reply again.

I didn't use your file, I've created a macro with your code and run it in my dummy file, the one I originally posted and when running the macro I got the Ambiguous thing.
Now I saw your file, what am I supposed to do in order to test it? If I look at the result it is OK YES

1. Create a macro with your code? locally in your file or for all the workbook (this doesn't matter that much at least for the test I guess.)
2. Copy and fill all the way down the formula in E2 - sheet 1? --> =Check(A2:D2,Sheet2!$A$2:$D$12)
3. In your file I see also another MATCH formula in column H; What should I do with it?
4. I can see you created two new columns "F" in both sheets, with concatenated values, should I deleted those before running the Macro?
5. Run the macro in your file?

THANKS

Results ARE OK, only I need to know how to use the macro please GREAT

hi PCI,

your file seems to work, but I can I use the Macro? I'm not that bad with excel but I can't do that, appreciated your help

Hi PCI,

That is simply GREAT...it works a treat...only two questions please if you have still a bit of time for me of course.

• what to change in case I need to add more criteria?
• is there a limit for the codes in column A that I want to be checked? and if yes I do extend this limit .
  

PCI...I saw you called the Macro "TREAT" I won't change it as that is exactly what you have provided me with ; A TREAT

I was able to run it for 7 criteria that is fantastic!!
THANKS!

one thing more, please! please!

Can we make the check in column D & E format sensitive for $ vs. € ? or maybe for you it is easier to make it sensitive for all the columns? but I only need for those two (D&E).
super THANKS really you are a master!

you've read my mind , yes it is, thanks.


I have noticed a strange behavior of the macro, sometimes it doesn't check with "V" some lines that are completely matched in sheet2.

I have attached a file where lines 2/10/13/14 should be checked too but for some reasons they are not, I also tried to align the formats but it doesn't work.

please find the code I used which is slightly adjusted by me (for more criteria), thanks again and again...

 LR = .Cells(Rows.Count, 1).End(xlUp).Row
      Range(.Cells(2, 7), .Cells(LR, 7)).ClearContents
      For I = 2 To LR
         If (ObjDic.exists(Join(Array(.Cells(I, 1), .Cells(I, 2), .Cells(I, 3), .Cells(I, 4), .Cells(I, 5), .Cells(I, 6)), "/"))) Then
            .Cells(I, 7) = "v"

Sub Treat()
Dim ObjDic   As Object
Set ObjDic = CreateObject("Scripting.Dictionary")
Dim LR  As Long
Dim WSh1  As Worksheet, WSh2  As Worksheet
Dim I As Long
Dim CheckChar As String
   Set WSh2 = Worksheets("Sheet2")
   Set WSh1 = Worksheets("Sheet1")
   CheckChar = "v"
   
   Application.ScreenUpdating = False
   With WSh2
      For I = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
         ObjDic.Item(Join(Array(.Cells(I, 1), .Cells(I, 2), .Cells(I, 3), .Cells(I, 4), .Cells(I, 5), .Cells(I, 6)), "/")) = Empty
      Next I
   End With
   With WSh1
      LR = .Cells(Rows.Count, 1).End(xlUp).Row
      Range(.Cells(2, 7), .Cells(LR, 7)).ClearContents
      For I = 2 To LR
         If (ObjDic.exists(Join(Array(.Cells(I, 1), .Cells(I, 2), .Cells(I, 3), .Cells(I, 4), .Cells(I, 5), .Cells(I, 6)), "/"))) Then
            .Cells(I, 7) = "v"
         End If
      Next I
   End With
   Application.ScreenUpdating = True
End Sub

Hi PCI,


I understood, so it was for a different format coming from the system we use at my office, thanks very much

I have attached a file with currencies if it can help, but maybe you already sorted it out? , basically macro shouldn't consider equal a value with different currencies, of course in case of value equal to zero the currency must be ignored.


I don't understand when to insert the second code that you posted, usually after the command " End sub" you don't have anything else right?

THANKS and if it is too much disturb please just leave it , I can manage the currencies manually

Hi great PCI

1.ok I did some test on the currencies function, please find it attached, only two fails.

2.please see below the code that I'm using , I have added two formulas that I need ('extended), it seems to work , any suggestion super appreciated of course.

3.for me you are already a friend, I'm super happy with the Macro, the state of art would be to have the cells that don't match highlighted in Sheet1 , but I guess it is too work

4.could you please suggest me a book for starting VBA studies?

Thanks a lot but really too much

Sub Treat()
Dim ObjDic   As Object
Set ObjDic = CreateObject("Scripting.Dictionary")
Dim LR  As Long
Dim WSh1  As Worksheet, WSh2  As Worksheet
Dim I As Long
Dim CheckChar As String
Dim ValD, ValE
   Set WSh2 = Worksheets("Sheet2")
   Set WSh1 = Worksheets("Sheet1")
   CheckChar = "v"
   
   Application.ScreenUpdating = False
   With WSh2
      For I = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
         ValD = CurAdj(.Cells(I, 4)): ValE = CurAdj(.Cells(I, 5))
         ObjDic.Item(Join(Array(.Cells(I, 1), .Cells(I, 2), .Cells(I, 3), ValD, ValE, .Cells(I, 6)), "/")) = Empty
      Next I
   End With
   With WSh1
      LR = .Cells(Rows.Count, 1).End(xlUp).Row
      Range(.Cells(2, 7), .Cells(LR, 7)).ClearContents
      For I = 2 To LR
         ValD = CurAdj(.Cells(I, 4)): ValE = CurAdj(.Cells(I, 5))
         If (ObjDic.exists(Join(Array(.Cells(I, 1), .Cells(I, 2), .Cells(I, 3), ValD, ValE, .Cells(I, 6)), "/"))) Then
            .Cells(I, 7) = "v"
         End If
      Next I
   End With
   Application.ScreenUpdating = True
   
   
   'Extended

    Sheets("Sheet2").Select
    Range("G2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-6]),"""",CONCATENATE(RC[-6],""  "",RC[-5],""  "",RC[-4],""  "",RC[-3],""  "",RC[-2],""  "",RC[-1],))"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G79")
    Range("G2:G79").Select
    ActiveWindow.SmallScroll Down:=15
    Sheets("Sheet1").Select
    Range("H2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-7]),"""",IF(RC[-1]<>""v"",INDEX(Sheet2!C1:C7,MATCH(Sheet1!RC[-5],Sheet2!C3,0),7),""""))"
    Range("H2").Select
    Selection.AutoFill Destination:=Range("H2:H220"), Type:=xlFillDefault
    Range("H2:H220").Select
    ActiveWindow.ScrollRow = 198
    ActiveWindow.ScrollRow = 100
    ActiveWindow.ScrollRow = 1
End Sub



Function CurAdj(WkVal As Range) As String
Dim WkF As String
   WkF = WkVal.NumberFormat
   CurAdj = IIf(InStr(1, WkF, "$$") <> 0, "$", IIf(InStr(1, WkF, "$€") <> 0, "€", "")) & WkVal
End Function

hi PCI, thanks.

sorry! I have attached the file

Hi PCI,

I'm a bit panicking because my formula (the index/match one ) is not working properly, please see the attachment

Hi PCI,

I don't have words to thank you, it is simply amazing and it does what I need perfectly.
Now the only issue left is the below, I quoted you and attached my formula fails.
Basically as you suggested I should add more keys to search, but can that be done with Index/Match?

any time in case you are fed up with me and my tool just mention it please

Originally Posted by PCI

"my formula (the index/match one ) is not working properly"
Yes it is working correctly because the key to search the data is only cell in column C, I would suggest to use something like CONCATENATE(A45," ",B45," ",C45," ",D45," ",E45," ",F45,) for the key
Clarify the the result you expect
Now a macro can do it ...
Note: All can work only if there is no duplicate

Hi PCI

Sorry I'm out of the office so slower with replying.
Then I think I should add the concatenated key as you suggested, there are not duplicates in the two sheets.
Can I add the concatenation key in the formula?


Biggest problem with the all tool is that currecies for column E are coming out from the system in a separated column and not as a format, so I have a column F with the words eur/usd, can macro read it and format the currencies in E accordingly?
For example :
In E12 we have the value 1,20 with no currency format, in the next F12 we have the word EUR, could macro read F12 and format E12 accordingly with eur as currency?
Please not that fortunately this is needed only for column E where we have the freights and not for prices in column D.

Hi PCI ,

I'm back in the office, sorry maybe I didn't explain clear in the previous post, apologies.
the problem with the tool that we are creating is that:

• The extract (sheet1) from SAP that I need to compare with what in Sheet2 comes with Currency for Column D expressed in a separated column and not as format.
• Currency for column D would be expressed with two words : EUR or USD in an additional column (let's say column F)
• This way sheet1 will have one column more than sheet 2 (this actually messes up your Macro)
• So before comparing Sheet1 vs. Sheet 2 with you super macro, I need to format D accordingly to the currency in F and get read of column F (to not mess up your Macro).
• I thought to do that run a first short macro that prepares sheet1 for you macro, so a two steps tool. (run Macro1 then run Macro 2)

So I created the below code , but it doesn't work as what I need is that for each cells in Column D a currency format should be applied according to the word (EUR/USD) in the correspondent cell in columm F.
So if F45=EUR D45= to be formatted in EUR.
My code doesn't do that , as it simply format the entire Column D

Sub AddFormCond()

    With Range("D1:D100").FormatConditions.Add( _
        Type:=xlExpression, _
        Formula1:=F = EUR)
    
        .NumberFormat = "$#,##0.000"
    
     Columns(5).EntireColumn.Delete
    End With

End Sub

I hope you can help me if not please just let me know so I will close the thread with ALL possible positive feedback for you and many many thanks

I'm Also exploring a solution with the conditional formatting but it seems more complicated to me.

Hi PCI,

thanks , I cannot do that because in Column F I only have either the word EUR or USD no values no nothing, only the currency expressed in letters/words (EUR/USD).
Unfortunately from our system the currency for the values in column D is indicated with a word (EUR/USD) in column F.
That s why I need to fix this to create the tool.

Thanks again

I meant Column E sorry, actually the word in column E indicates the currency for the values in D

thanks so much

I'll do.