How to validate the same set of codes in multiple divisions?

[inguli]

Hi 

I'd like to perform couple tests and I am thinking what would be the most efficient way of achieving this.

I have:

Codes (XX - represents a division)	Amount			Divisions
XX1P1	£50			AA
XX2P1	£450	different amount on DB		AB
XX3P1	£85			AC
XX1P2	£135			AD
XX2P2	£227			etc.
XX1P3	£228
XX2P3	£462	different amount on DB
XX3P3	£183
XX4P3	£163
XX5P3	£17
etc.

There are around 50 codes with 3 ending alphanumeric characters within each division. XX - represents division. There are about 100 divisions. Some codes within each division have the same amount and some codes within each division have a different amount.

I'd like to do 2 tests:
Test 1: to check that each division has the same set of codes (AA1P1, AA2P1, AA3P1 etc.) within DB. And that there aren't any extra codes with different ending or duplicate codes and that none of them missing.
Test 2: to check that the amount matches for each code for each division and to show the lines on DB where it doesn't match.

I attached excel file with the information above and a snapshot of DB.

What tools would it be the best to use to do these tests?

Many thanks

[OTrieger]

@inguli 

If you are not familiar with the process then process visualization can help here, try to create a process map and or a flowchart for the process, visualize it and then try to consult or brainstorm with others what will be the best way to go about.

[ChrisTX]

The attached workflow should point you in the right direction.

I can't accurately help with a test for "there aren't any extra codes with different ending" because you didn't mention how to identify a "code" or "ending".  Guessing code is characters 3 and 4 and ending is the last character?

When providing sample data, please provide all of the data, and avoid ETC.  You have an example of "different amount on DB" but one of those rows has a blank Division.  I assumed both of those rows were division AB.

For the test "duplicate codes", I guess you mean a duplicate code within a Division?

Chris