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?

I actually finally got it to work. Took more steps than I wanted ... but
it's working. But in regard to your comment flagging what you thought was
incorrect ... those actually are correct. See, code 1A could be ANY of
these: 65, 63, 74, 94, 86, 73, 47, 52, 83, RS, 76, 97, 78, 34, 51, CT, 39,
77, 55, 57, 58, CR, CM.

What I actually wound up doing was to add a column and first do a vlookup -
and in the lookup table itself, I rearranged the columns ... that seems to
work fine. Then I added another column with an if function that compares
Code 1 with whatever was returned by the vlookup ... and simply gives me an
answer of true or false. That makes it MUCH easier to spot the errors in
6,000 rows of data ... and then I can research those and see why it was
generated that way by the mainframe.

I really wanted to nest the two functions into one ... but I'm at least
happy it works this way.

Thanks for your thoughts/help, everyone.

Cathie


"Max" wrote:

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?

For this flag:
1A CT X
Ok, I've re-checked the sample file. There were unfortunate leading spaces
for all the alphas listed in E1:E23 (ie the defined range _1A). And that was
throwing the COUNTIF for "CT" off. When all the leading spaces were cleared
up, it worked fine, ie that line shouldn't and won't be flagged.

As for this other one flagged:
1E 95 X
It is still valid as there's no 95 within the defined range _1E

Trust me that what I suggested earlier should work fine for you
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"Cathie" wrote:
I actually finally got it to work. Took more steps than I wanted ... but
it's working. But in regard to your comment flagging what you thought was
incorrect ... those actually are correct. See, code 1A could be ANY of
these: 65, 63, 74, 94, 86, 73, 47, 52, 83, RS, 76, 97, 78, 34, 51, CT, 39,
77, 55, 57, 58, CR, CM.

What I actually wound up doing was to add a column and first do a vlookup -
and in the lookup table itself, I rearranged the columns ... that seems to
work fine. Then I added another column with an if function that compares
Code 1 with whatever was returned by the vlookup ... and simply gives me an
answer of true or false. That makes it MUCH easier to spot the errors in
6,000 rows of data ... and then I can research those and see why it was
generated that way by the mainframe.

I really wanted to nest the two functions into one ... but I'm at least
happy it works this way.

Thanks for your thoughts/help, everyone.

Cathie

Here's an update to the earlier sample,
with all extraneous spaces cleaned up:

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

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---