Multiple Set of List Comparison Driven by Cells Value

**nucky** · 02-18-2013, 09:49 AM

I am developing a scrub field that needs to compare cell A value to 1 of 3 sets of lists and cell B value to a different 1 of 3 sets of lists. THEN the list answer to comparison 1 needs to equal the answer to comparison 2. The final value should equal either “GOOD” or “CHECK”. See attached spreadsheet.

Scrub Sample.xls

If a value is chosen from the list in cell Request!A2 and a value is chosen from the list in Request!B2, the scrub field in Request!C2 needs to compare if the “Leave Reason” matches an acceptable “Leave Relationship”.

All lists are represented on the ‘Reference’ worksheet:
Reason1 List = Relation1 List
Reason2 List = Relation2 List
Reason3 List = Relation3 List

Examples:
IF Request!A2 = Employee’s Serious Health Condition, THEN = Reason1 list. And if Request!B2 = Self / Employee, THEN Relation1 list. Which equals a final result of “GOOD”.

IF Request!A2 = Maternity, THEN = Reason1 list. And if Request!B2 = Child Under 18 Years of Age, THEN Relation2 list. Which equals a final result of “CHECK”.

IF Request!A2 = Family Member’s Serious Health Condition, THEN = Reason2 list. And if Request!B2 = Child Under 18 Years of Age, THEN Relation2 list and Relation3 list (if two different results is not possible for the formula to work, I can eliminate Reason3 and Relation3 List from the formula). Since, ‘Child Under 18 Years of Age’ is a value on two different list but Reason2 list = Relation2 list, which equals a final result of “GOOD”.

**JBeaucaire** · 02-18-2013, 11:30 AM

Only three named ranges/formulas are needed.

First, delete columns A:B so that you just have the lists starting in column A, each list in a separate column. Now copy all the values UP so the row 1 has the actual "reasons" across that row, with the options for that reason starting in row2 and going down.

Then, in the Name Manager clear all the other entries, then put in these 3:

AnchorCell - =Reference!$A$1

Reason - =OFFSET(Reference!$A$1, , , , COUNTA(Reference!$1:$1))

Relationship - =OFFSET(AnchorCell, 1, MATCH(Request!$A2, Reason, 0)-1, COUNTA(OFFSET(AnchorCell, 1, MATCH(Request!$A2, Reason, 0)-1, 100, )), )

Now your DV lists in columns A and B on the Request sheet are linked. The column A selection will drive the options that appear in column B.

Lastly, select B2:B100 and create this Conditional Formatting rule:
=NOT(ISNUMBER(MATCH(B2, Relationship, 0))) (choose the FONT color white)

Now, use A, then B to select your options. If someone then goes back and changes A to a different category, the B cell will vanish if the old entry is no longer valid. It's still there, it's just painted white, nice trick.

**nucky** · 02-18-2013, 11:55 AM

Unfortunately, that work around does not apply for my purposes. Cells A & B that I am comparing are being entered in by clients and they commonly copy and paste over cells rather choosing from the list. Therefore, once I receive their file, I want to scrub what they entered by dragging cell C down to compare the two values they entered in cell A & cell B by the guidelines I originally posted.

Please note there is a lot more to this file then what is being provided in the example. Also, there is a scrub field (that isn't provided in the example) to ensure the values in cell A and cell B match the exact character values from the list.

**nucky** · 02-19-2013, 11:55 AM

Any other suggestions out there or is it not possible?

**JBeaucaire** · 02-19-2013, 12:08 PM

The solution given is how I would approach, and do so in all my client-based projects. I've shown you a simpler setup without all those named ranges, it's dynamic for columns and rows so is self-sustaining, AND a way to detect visually when someone has mismatches.

Your use-process sounds more chaotic than I would allow.

I'll ping some others to take a look, I don't have any more on this to offer.

**nucky** · 02-19-2013, 12:50 PM

You hit the nail on the head on 'chaotic'. We instruct our clients over and over but they are pulling from a variety of systems and generally copy and paste information in or build their own files to match our field placement but never pay attention to the rules of each value. The end result to this file is an upload into our database hence the need to scrub the values of what was inputted. Thanks for the help!