Macro to compare against multiple criteria two sheets

[isasa74]

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.

[PCI]

Try next code
E2 = Check(A2:D2,Sheet2!$A$2:$D$12)
Copy and paste down

Please Login or Register  to view this content.

[isasa74]

Hi thanks so much for you help

I got an error - Ambiguous name detected:check-

[PCI]

"I got an error - Ambiguous name detected:check- "
Is it OK or not ?
Does the file sent OK ?

[isasa74]

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

[isasa74]

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

[isasa74]

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

[PCI]

In sheet "sheet1"
E2 =Check(A2:D2,Sheet2!$A$2:$D$12)
Copy E2 down
In a new file put the code sent in a module and put the formula as shown in the fiel sent: Take care about the ranges used: A2:D2 and Sheet2!$A$2:$D$12
Now if to use an UDF is not convenient for you it is possible to change it to a macro

[PCI]

Here a remake with a macro
Pay attention to next code and adjust to your needs

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[isasa74]

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]

"what to change in case I need to add more criteria?"
The list of criteria is given by

Please Login or Register  to view this content.

"is there a limit for the codes in column A that I want to be checked? "
No, all rows from 2 to the last one are checked, see code

Please Login or Register  to view this content.

[isasa74]

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!

[PCI]

"Can we make the check in column D & E format sensitive for $ vs. € "
It means in columns "D & E" there is numbers and you want to check also the currency used in the format, is it?

[isasa74]

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...

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[PCI]

"I have noticed a strange behavior of the macro, sometimes it doesn't check with "V" some lines that are completely matched in sheet2"
It is NOT coming from the macro but from the data: See your file with some cells highlighted.
About columns D & E in the last file sent there is no currency format, what to use to do the check

[PCI]

Try next code

Please Login or Register  to view this content.

[isasa74]

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

[PCI]

"after the command " End sub" you don't have anything else "
Yes you can have others subroutines and functions
Just keep in the same module all codes sent: Sub and Function
Is it OK for currency now ??

[isasa74]

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

Please Login or Register  to view this content.

[PCI]

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

If you don't send a file with the 2 sheets it is difficult to see why it failed
2. The same for the formulas, it is easier to see the formula inside Excel and see afterwards what to do
Note, using AutoFill as you are doing, you are not flexible versus the last row to use
3. to highlight cells you can see for a Conditional Format
4. About books, no I don't any reference but investigate in this forum there is information

[isasa74]

hi PCI, thanks.

sorry! I have attached the file

[PCI]

Here a remake
Some comments:
There is some error with the $ because is used a custom format and not a currency: See in the file attached the format used
Formulas are prepared in each sheet
The conditional format used in the first row in column "G" of sheet1 is extended to the full range

Please Login or Register  to view this content.

[isasa74]

Hi PCI,

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

[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

[isasa74]

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.

[isasa74]

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

Please Login or Register  to view this content.

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.

[PCI]

" I need to format D accordingly to the currency in F "
Why not to copy column "F" and do a Special Paste Format to column "D" ...?

[isasa74]

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

[PCI]

"in Column F I only have either the word EUR or USD no values no nothing"
Is it column F or column "E": See Currency_F.xlsm

[isasa74]

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

[PCI]

See here after the code
Perhaps it could be more clean not to have the additional column "D" and to read the currency information from column F or other ...

Please Login or Register  to view this content.

[isasa74]

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

"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

[PCI]

The INDEX / MATCH issue is another story and it will good to open a new thread to avoid confusion in the title.
Give more detail what you want to achieve with your formula: Show example

[isasa74]

thanks so much

I'll do.