Find / compare result with list
 

I am going to have a set of data to be checked against a set of default
data, and then flagged if it outside the ranges.

I have attached a quick example (i have not included all possibilites)

For example for Ref 1 : this is from area 12, expected 1000 with an a
result of 23, b result of 100 and c result of 50.

It is required that the results are checked against the min and max
targets, however these targets are dependant on the area / expected.

Therefore for Ref 1, using the constant data, the min max for type are
50 / 200. As the actual result is 23 this is outside the permitted
range and therefore needs notification.

The data will be imported from another system, and therefore the layout
will be similar to the data, so I am not sure how the problem ones will
be actually identified (as each row will have different results).

I am not sure as the best way to start looking at this (the only
thought i have had so far is IF/OR!)

Thanks


+-------------------------------------------------------------------+
|Filename: example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4905 |
+-------------------------------------------------------------------+

--
mdma
------------------------------------------------------------------------
mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
View this thread: http://www.excelforum.com/showthread...hreadid=553169

Find / compare result with list
 

Do you have an idea about how many types of results (a, b, c in your example)
you can expect, and how many different Areas (12, 15, etc) you can expect?

"mdma" wrote:


I am going to have a set of data to be checked against a set of default
data, and then flagged if it outside the ranges.

I have attached a quick example (i have not included all possibilites)

For example for Ref 1 : this is from area 12, expected 1000 with an a
result of 23, b result of 100 and c result of 50.

It is required that the results are checked against the min and max
targets, however these targets are dependant on the area / expected.

Therefore for Ref 1, using the constant data, the min max for type are
50 / 200. As the actual result is 23 this is outside the permitted
range and therefore needs notification.

The data will be imported from another system, and therefore the layout
will be similar to the data, so I am not sure how the problem ones will
be actually identified (as each row will have different results).

I am not sure as the best way to start looking at this (the only
thought i have had so far is IF/OR!)

Thanks


+-------------------------------------------------------------------+
|Filename: example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4905 |
+-------------------------------------------------------------------+

--
mdma
------------------------------------------------------------------------
mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
View this thread: http://www.excelforum.com/showthread...hreadid=553169

Find / compare result with list
 

in the constants :

areas will be upto 5 different areas

as for the results, there will be 5 types of results, each then with
the expected and min / max levels

The number of actual result lines (ie ref) could be anything between
100 and 1000.


--
mdma
------------------------------------------------------------------------
mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
View this thread: http://www.excelforum.com/showthread...hreadid=553169

Find / compare result with list
 

Take a look at this:
http://www.jlathamsite.com/uploads/for_mdma.xls

No code in it, all done with formulas. I've included a sheet with my
attempted explanation of how I arrived at the result, plus an added sheet
where the formulas are put to work so you can see it all in action.

I hope this comes close to what you want, and if not, well, then I'll just
have to work a little harder won't I?

"mdma" wrote:


in the constants :

areas will be upto 5 different areas

as for the results, there will be 5 types of results, each then with
the expected and min / max levels

The number of actual result lines (ie ref) could be anything between
100 and 1000.


--
mdma
------------------------------------------------------------------------
mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
View this thread: http://www.excelforum.com/showthread...hreadid=553169

Find / compare result with list
 

thank you so much. that has worked a treat.

i made the formula "real life" into a hidden sheet, and now the user
just has to post the data into the initial sheet and it will then
update a summary sheet accordingly.

again, thanks alot :)


--
mdma
------------------------------------------------------------------------
mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
View this thread: http://www.excelforum.com/showthread...hreadid=553169