FORMULA

[attyshane]

I have a hodge-podge list of numbers and letter-number combinations. I need
excel to tell me when any of those values appear in collumns J-N . can this
be done and how?

 

[Don Guillett]

Examples?

 

[Andri]

i just try to understand it...
Column J1 contains 123, isnumber(J1) --- TRUE
Column K1 contain A123, isnumber(K1) --- FALSE


HTH.

 

[Fred Smith]

You probably want Countif. If you have a number in A1, and what to know
whether it appears in J1:N12, use:
=Countif(j1:n12,a1)

Anything greater than 0 means it's in the range.
Repeat for other entries in your "hodge-podge"

Regards,
Fred

 

[attyshane]

Here is the list of numbers I have

(003-009, 0010-0039, 005-009, 0010-0059, 008, 00800-00809, 022, 0220-0229,
E800-E899, EE8100-E8499, E800-E899, E8601-E8699, E6700-E7199, E910-E999,
E9100-E9201, E9203-E9210, E927, E9281-E9299, E960-E999, 354, 3540-3549,
35401-35449, 3577, 35770-35779, 3582, 35820-35829, 3594, 35940-35949,
3605-3609, 36010-36069, 37024, 37486, 37652, 37734, 3881-3889, 38810-38812,
4950-4959, 49501-49589, 500, 50100-50899, 501-509, 5001-5099, 5078,
50780-50789, 508, 5080-5089, 50800-50899, 5532, 55320-55329, 5582,
55820-55829, 692, 6920-6922, 6924, 69282, 717, 7170-7179, 719, 7190-7189,
719, 71900-71990, 7217, 722, 72200-72239, 7231, 7241-7245, 7249, 7263-7269,
72610-72649, 7272, 72720-72729, V713, V7130-V7139, V714, V7140-V7149, V716,
V7160-V7169, V825, 800-899, 8100-8399, 840, 8401-8489, 841-849, 850-859,
8501-8599, 860-869, 8601-8699, 870-879, 8701-8799, 880-889, 801-9899,
890-899, 8901-8977, 905-909, 9010-9099, 910, 9100-9103, 9108-9109, 911,
9110-9113, 9118-9119, 912, 9120-9123, 9128-9129, 913, 9130-9133, 9138-9139,
914, 9140-9143, 9148-9149, 915, 9150-9153, 9158-9159, 916, 9160-9163,
9168-9169, 917, 9170-9173, 9178-9179, 918, 9180-9181, 9189, 919, 9190-9193,
9198-9199, 920-929, 9210-9299, 940-949, 9410-9579, 9584-5985, 959, 9510-9599,
960-969, 9610-9649, 96502-97999, 980-989, 9810-9899, 98100-98799, 988,
9880-9894, 9896-9899, 990, 9934, 9941, 9947-9948, 9958, 99580-99585, 996,
9960-9969, 99610-99699, 9982-9989, 99810-99859, 9987, 9989)

I need to know if Columns J-N have any of these values--for example if J1
has 9989 I want to know but if J2 does not have any number on my list then I
either want to know that or have it excluded

thanks

s

 

[attyshane]

Here is the list of numbers I have

(003-009, 0010-0039, 005-009, 0010-0059, 008, 00800-00809, 022, 0220-0229,
E800-E899, EE8100-E8499, E800-E899, E8601-E8699, E6700-E7199, E910-E999,
E9100-E9201, E9203-E9210, E927, E9281-E9299, E960-E999, 354, 3540-3549,
35401-35449, 3577, 35770-35779, 3582, 35820-35829, 3594, 35940-35949,
3605-3609, 36010-36069, 37024, 37486, 37652, 37734, 3881-3889, 38810-38812,
4950-4959, 49501-49589, 500, 50100-50899, 501-509, 5001-5099, 5078,
50780-50789, 508, 5080-5089, 50800-50899, 5532, 55320-55329, 5582,
55820-55829, 692, 6920-6922, 6924, 69282, 717, 7170-7179, 719, 7190-7189,
719, 71900-71990, 7217, 722, 72200-72239, 7231, 7241-7245, 7249, 7263-7269,
72610-72649, 7272, 72720-72729, V713, V7130-V7139, V714, V7140-V7149, V716,
V7160-V7169, V825, 800-899, 8100-8399, 840, 8401-8489, 841-849, 850-859,
8501-8599, 860-869, 8601-8699, 870-879, 8701-8799, 880-889, 801-9899,
890-899, 8901-8977, 905-909, 9010-9099, 910, 9100-9103, 9108-9109, 911,
9110-9113, 9118-9119, 912, 9120-9123, 9128-9129, 913, 9130-9133, 9138-9139,
914, 9140-9143, 9148-9149, 915, 9150-9153, 9158-9159, 916, 9160-9163,
9168-9169, 917, 9170-9173, 9178-9179, 918, 9180-9181, 9189, 919, 9190-9193,
9198-9199, 920-929, 9210-9299, 940-949, 9410-9579, 9584-5985, 959, 9510-9599,
960-969, 9610-9649, 96502-97999, 980-989, 9810-9899, 98100-98799, 988,
9880-9894, 9896-9899, 990, 9934, 9941, 9947-9948, 9958, 99580-99585, 996,
9960-9969, 99610-99699, 9982-9989, 99810-99859, 9987, 9989)

I need to know if Columns J-N have any of these values--for example if J1
has 9989 I want to know but if J2 does not have any number on my list then I
either want to know that or have it excluded

thanks

s

 

[Don Guillett]

Still not very clear as to your desires.

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.