[Excel] Comparison of 4 cells

Ok, so okay for the formatting. in that case, I will create a column "C" that will indicate true or false with conditional formatting.

Now I would like to successfully link the VRAI base (tab 1) and the base to verify (tab 2).

In fact, I am looking to perform a synthesis of the airliners I photograph.

The base being considered as true, it informs us of the following information (for example):

Constructure Type Imat n°série

Airbus A320 F-XXXX 3234
Boeing B777 G-XYXY 2345

Now, in tab 2, for each photo taken (even for the same aircraft), I will create a row that lists the information about the aircraft, shooting conditions, location, time, etc.

However, I would like to create formulas that verify that the data in the base are exact, for example:

Airbus A320 F-XXXX 3234 xx 2011 ... > no error
Airbus A320 F-XXXX 3235 xx 2011 ... > error detected because it is not in the base.

I hope you can enlighten me on this problem. Thank you.

Hello,

If I understand correctly, you want to report an airplane not listed in your database when you enter in feuille2

if yes, you name the relevant column of your database (take extra room) for example: base

in feuille 2 in the column "airplane name"), you select a number of rows (also take extra room)

in MEFC you choose the "formula" option
and you write (here we start in A2)

=(A2<>"")*(nb.si(base;A2)=0)

and choose your formatting

the problem is that one cannot know where the data A, B are, what the separator is: we only have what you write to try to help you

If needed, attach the workbook without confidential data in XL97-2003 format at
http://cijoint.fr/
and paste the link suggested in the reply message

Michel

Indeed, this allows you to find unidentified aircraft in my database, but not only that. It should also help recognize a typo error on an aircraft already in the database. For example, a serial number with a typing error is automatically recognized.

I’m not sure I’m clear enough; it’s not that easy to understand ^^

Thank you :)

A typo will therefore correspond to a false aircraft name !!!

unless you have multiple columns and you want to check all these columns, an element I was asking you to provide by attaching your spreadsheet

You go about it in a way that completely discourages me from helping you...
--
Michel

sorry I hadn’t seen the end of the previous message, a thousand apologies :)

I’m going to prepare an Excel file to explain to you.

Thank you in any case !

edit: here is the link to the file.

[URL]http://www.cijoint.fr/cjlink.php?file=cj201105/cijZkTcoT3.xls/URL

In your G2 cell, enter the following formula
=IF(ISERROR(VLOOKUP(F4,base!$C$2:$D$800,2,FALSE));"The registration number does not match any serial number";VLOOKUP(F4,base!$C$2:$D$800,2,FALSE))
Then, simply copy this formula to your following rows.

I used D800 but you can use something else. It’s just to have something to fill your rows
--
The problem one faces is the problem many face, so might as well give the solution!

Okay so I did enter your formula in G2, but it also changes the serial number value? Shouldn’t the formula be put in H2 instead?

by doing this it shows the correct value next to my entered value.
for the last 3 lines it shows The comment: registration number does not match any serial number

Did I do something I shouldn’t have?
sorry but I’m really bad at Excel :s

Thanks

This formula automatically fills column G when you enter the registration number.

For your last three rows, it's because the registrations are not entered in the database.

However, Excel cannot distinguish between cells to be colored red and those to be colored orange.
--
The problem that one faces is a common one, so let's give him the solution!

okay, I agree!
I just tried again and it works correctly this time, thank you very much!

If you don’t have any other questions, think about marking the topic as resolved.
--
The problem that one poses is the problem that many face, so might as well give him the solution!

Actually I have one last question

Now it will save me from input errors with this formula.

However, for data already entered, it’s more complicated to verify that every serial number already entered is associated with its correct registration?

In reality I have a fairly large number of lines and different airplanes, and checking everything manually can quickly take a lot of time.

Thank you.

You just need to copy the formula from top to bottom in the column. Thus, what you had entered before will be replaced by the same values (unless you had made mistakes).

Am I answering your question because I had trouble understanding it.
--
The problem one asks oneself is the problem of many, so might as well give them the solution!

Yes, roughly, it actually makes me think that via conditional formatting or a pivot table, I can immediately check all the false values.

Thank you for your help and your patience!

ps: I may have a question to ask later, this time about the fact of "pulling" certain information from a cell. I’ll post another message.

You're welcome. No worries. Ask me the questions in a private message or I might not see them. I don't look much into the topics posed. I just take from the most recent posts.
--
The problem one faces is the problem that many face, so better give them the solution!