I have a worksheet that is approx. 60 columns, and 6,000 rows ...

One of the columns is labeled Code 1; another Code 2. If there is a 1 under
Code 1, it should pull one of any numbers from a list (see below). I am
looking for a way to make sure it is pulling a correct entry ... in other
words, I want to flag it somehow if the entry is NOT in the list of codes.

Code1 Code2
1A 65
1E 42
1D MF
1A CT
1E 90
1E 95
1E 50
1A 39
1A 77

If code1 = 1A, then code2 should = 65, 63, 74, 94, 86, 73, 47, 52, 83, RS,
76, 97, 78, 34, 51, CT, 39, 77, 55, 57, 58, CR, CM
If code1 = 1E, then code2 should = 90, 91, 93, 98, 54, MP, 42, 43, 56, 48,
49, 50, 46, 66, 67, 53, 96, CW
If code1 = 1D, then code2 should = MF

Is there a function that will do this for me?

I'm thinking this might help you.

http://www.contextures.com/xlDataVal13.html
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Cathie" wrote:

I have a worksheet that is approx. 60 columns, and 6,000 rows ...

One of the columns is labeled Code 1; another Code 2. If there is a 1 under
Code 1, it should pull one of any numbers from a list (see below). I am
looking for a way to make sure it is pulling a correct entry ... in other
words, I want to flag it somehow if the entry is NOT in the list of codes.

Code1 Code2
1A 65
1E 42
1D MF
1A CT
1E 90
1E 95
1E 50
1A 39
1A 77

If code1 = 1A, then code2 should = 65, 63, 74, 94, 86, 73, 47, 52, 83, RS,
76, 97, 78, 34, 51, CT, 39, 77, 55, 57, 58, CR, CM
If code1 = 1E, then code2 should = 90, 91, 93, 98, 54, MP, 42, 43, 56, 48,
49, 50, 46, 66, 67, 53, 96, CW
If code1 = 1D, then code2 should = MF

Is there a function that will do this for me?

Not quite what I'm looking for ... I just want it to flag it somehow if what
is showing up in Code 2 isn't in list of numbers to be used for the
corresponding Code 1. Maybe I'm making it more difficult than it really is
????

"Barb Reinhardt" wrote:

I'm thinking this might help you.

http://www.contextures.com/xlDataVal13.html
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Cathie" wrote:

I have a worksheet that is approx. 60 columns, and 6,000 rows ...

One of the columns is labeled Code 1; another Code 2. If there is a 1 under
Code 1, it should pull one of any numbers from a list (see below). I am
looking for a way to make sure it is pulling a correct entry ... in other
words, I want to flag it somehow if the entry is NOT in the list of codes.

Code1 Code2
1A 65
1E 42
1D MF
1A CT
1E 90
1E 95
1E 50
1A 39
1A 77

If code1 = 1A, then code2 should = 65, 63, 74, 94, 86, 73, 47, 52, 83, RS,
76, 97, 78, 34, 51, CT, 39, 77, 55, 57, 58, CR, CM
If code1 = 1E, then code2 should = 90, 91, 93, 98, 54, MP, 42, 43, 56, 48,
49, 50, 46, 66, 67, 53, 96, CW
If code1 = 1D, then code2 should = MF

Is there a function that will do this for me?

.. I just want it to flag it somehow if what is showing up in
Code 2 isn't in list of numbers to be used for the corresponding Code 1

This set up might get you going ..

First, create 2 columnar defined ranges: _1A and _1E
to refer to the respective lists

Then assuming Code1, Code2 data is running in A2:B2 down
you could place this in C2:
=IF(COUNTA(A2:B2)<2,"",IF(OR(AND(A2="1D",B2="MF"), AND(A2="1A",COUNTIF(_1A,B2)),AND(A2="1E",COUNTIF(_ 1E,B2))),"","X"))
Copy C2 down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---

But I'm not counting ... I want it to look in A2 - Code 1(as an example) to
see what number is there (nothing I typed in, it was imported from a
mainframe database) ... I want it to look in a LIST to make sure Code 2 is
the correct Code 2 setting. There could be 19 codes under Code 1 (A2) and
possibly 102 under Code 2. I'm not getting a lookup to work correctly as
these code 2s could apply to more than one different Code 1 number, so I
can't tell it to go to a certain column after it finds a match. I just want
to make sure that Code 1 agrees with whatever Code 2 number the mainframe is
generating.

Did that help or confuse everyone more?

"Max" wrote:

.. I just want it to flag it somehow if what is showing up in
Code 2 isn't in list of numbers to be used for the corresponding Code 1

This set up might get you going ..

First, create 2 columnar defined ranges: _1A and _1E
to refer to the respective lists

Then assuming Code1, Code2 data is running in A2:B2 down
you could place this in C2:
=IF(COUNTA(A2:B2)<2,"",IF(OR(AND(A2="1D",B2="MF"), AND(A2="1A",COUNTIF(_1A,B2)),AND(A2="1E",COUNTIF(_ 1E,B2))),"","X"))
Copy C2 down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---

Don't be so quick to dismiss the earlier suggestion as not helpful ..
(I think I did catch the gist of what you were facing & wanted done)

Ok, based on the details in your original posting,
aren't these the results that you expect to be flagged? :

Code1 Code2 Flag
1A 65
1E 42
1D MF
1A CT X
1E 90
1E 95 X
1E 50
1A 39
1A 77

How about an implemented sample to illustrate
the above set-up working properly:

http://freefilehosting.net/download/42671
Flag If inconsistent with Code list.xls

Take a closer look at the above, then do the correct rating on the response.

But I'm not counting ..
Neither was I. In the suggested expression, COUNTIF is used to check whether
col B's item appears within the Code2 listing corresponding to what's in col
A (1A, 1E). If the item appears, then the COUNTIF, eg: COUNTIF(_1A,B2) will
evaluate to a number greater than zero, ie an equivalent to TRUE.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"Cathie" wrote:
But I'm not counting ... I want it to look in A2 - Code 1(as an example) to
see what number is there (nothing I typed in, it was imported from a
mainframe database) ... I want it to look in a LIST to make sure Code 2 is
the correct Code 2 setting. There could be 19 codes under Code 1 (A2) and
possibly 102 under Code 2. I'm not getting a lookup to work correctly as
these code 2s could apply to more than one different Code 1 number, so I
can't tell it to go to a certain column after it finds a match. I just want
to make sure that Code 1 agrees with whatever Code 2 number the mainframe is
generating.

Did that help or confuse everyone more?