Comparing info between rows and columns

[Sharkybu]

Hello,

I have a very complicated table that I'm trying to sort -find errors.

I've done multiple attempts to do this, and I always feel like I'm doing this too complicated. Plus I built it to find problems I saw, I don’t know if there are others.

So, I'm asking for help.

This is a sample of one person out of over a 1000. The number of contracts and locations can vary.

ID	date	Status	Contract	Located
1	30/06/2003	open	B	There
1	01/10/2009	Close	A	There
1	01/10/2020	open	B	Here
1	01/10/2009	close	A	Here

No matter how many locations a person has the contracts are always equal between them.

Mistakes in this example alone:

Located "there"- contract B start date is before contract A

And Contract B has different dates in "Here" and "There".

I have over 1000 people, over 100 different units and 10+ contracts.

My latest attempt was to compare rows by ID (creating 16 comparisons in this case)

And creating different "if" columns.

The original data is an excel file I get sent periodically. And the problem shown here is new even though the last change for this person was done in 2020.

I have so many other problems with the data, but this is the one I'm finding the hardest to tackle.

If what I want is not possible and or doesn't make sense, then I'm sorry.

Thank you.

[Sharkybu]

The expected output is the same file with the errors marked. So I can send it back and ask them to fix it.

types of error that can happen are:

A person can be connected to either one or multiple locations but they always have the same contracts.

Only one contract can be in open status at a time, and it has to be open in all locations.

A contract can only appear once per location(I have a case that someon has contract A twice- once open, once close)

The date for a contract is the same in all locations.

an open contract can't have an earlier date then a closed one. 

"null" values are an error.

Contracts have order- Contract B can only happen when contract A closes, not the other way around. 

That's all that I can think of now.

What do you mean by " What are we doing to identify what is an error?"

Thank you

[spinfuzer]

What is your expected output?  What are we doing to identify what is an error?  What are all the types of errors that could occur?