Comparing Multiple criteria

[Sandip Shah]

Hi,

We are in a process of moving from an old accounting package to a new
one. Hence we need to test the data from the old software with the
test data loaded into the new software.

This involves comparing multiple criteria in one spread sheet with
another ( the output from new software with old software ).

for eg. the following 3 criteria needs to be compared

Old Software output

Col B Col F Col H
Location Country City
31900 ABC XYZ
08765 DEF RTY

Similarly the new software would also have a similar output in
different columns. The data may be on a different row, however in the
same line.

What formula do I use to compare based on the above multiple criteria,
and if all the 3 criteria matches with the data in the other sheet, it
should say ok and if it does match, then I can put up whatever comment
I want.

Also incase the data in the output from the new software is
duplicated( or more than once ), the formula would identify it and
state that its a duplicate.
Appreciate anyone's help on this.

Regards
Sandip.

 

[Andy B]

Hi

Using a helper column alongside your data you could do this with a COUNTIF()
function. This would count the occurrences of each line. A zero would
indicate it wasn't there (or was there but incorrect), a 1 would indicate it
is in the other list, correctly, and more than 1 would indicate a duplicate.
On each spreadsheet, also, I would put a helper column containing the three
cells together (31900ABCXYZ) as this will make the job easier.
To concatenate the cells use
=B2&F2&H2
in J2 (for example) and fill down
The other formula should be entered alongside the concatenated data (eg K2)
and be
=COUNTIF(Range1,J2)
and again needs to be filled down.

Good luck!

Andy.