I need formula to auto detect errors

[kywenyi]

Hi, i will insert 2 sets of data to insert in excel. i need formula to auto check which are the data that have error. like data 1 is the correct data, data 2 is the data that might have error. so data 2 have to source for code in data 1 and see if it match or error. each set of data there's a code number and a alphabet code eg 12345 is the item code, DDD is the availability code. so in data 2 they have to see code 12345 and find this code in data 1 and then match the availability code DDD.

 

[CyrusTheVirus]

Welcome to the forums. Can please copy/paste your what your data looks like AND your expected results?

Showing your data tremendously helps the viewers.

 

[kywenyi]

SET A SET B
ITEM CODE QTY ITEM CODE QTY RESULT
Banana 5 Banana 4 Wrong
Apple 8 Apple 8 Correct
Grapes 3 Mangosteen 1 Correct
Mangosteen 1 Peach 7 Missing
Durian
Lemon 10

Usually set A qty will change but we will generate Set B to check for mistake that have not been updated correctly in our system. So for Durian and Lemon i will also need to check and they might not appear in Set B if it has been updated wrongly.

Actually our codes will be fixed in multiple files already. It will be good if i can just generate Set B code and link it to check for error.

 

[DataBlake]

without seeing your code we can't be the most helpful but try wrapping this around your formula, which will notify you of which cells throw back an error
Its also confusing when you dont explain certain terms like "for mistake that have not been updated correctly" - we do not know what constitutes as a mistake, what is correct, etc.
but to catch the errors before they get to this point try

=iferror(

your code in between

,"error")

Also, Mr.Excel has a very cool table making tool in your posts to help illustrate your data.

 

[kywenyi]

i'll try to explain again. maybe in another way. let me think.. hmmm..

1) how do I extract data from 1 workbook to another workbook? i have 8 workbook and was thinking to extract all data of a certain cells to 1 workbook first.

2) after extracting, on my new workbook i have my "product code" in column A and "stock qty" in column B.

3) i will need to generate data from my system and make sure it is tally with new new workbook. so i was think to copy and paste this generated data to my consolidated workbook in point 2 eg in column D and E.

4) on column C, i need the fomular to tell me the difference between these 2 data. column D and E must be the same as column A and B.