Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Creating an Exception List in Excel 2

Status
Not open for further replies.

zinderellie

Technical User
Oct 28, 2002
38
US
I am working with an inventory list that has 2 parts that are supposed to be paired when the column is sorted. I need to create an exception list when one of these paired part #'s is missing. My goal is to quickly create an exception worksheet.

Here's an example -

CM776X
CM776XCR

If one or the other is missing in an alpha sorted list, I would like it to populate a cell with the number not missing. Hopefully creating my exception list to work from.

For instance, if CM776XCR is present, but CM776X isn't, I want the formula to check the adjacent cell above or below. If the paired part # is missing, the part # present should be the result of the formula.

I hope I was clear. If not, ask away.

Thanks!
 




Hi,

Are these pairs ALWAYS a length of 6 & 8?

Skip,
[sub]
[glasses] [red][/red]
[tongue][/sub]
 
The length varies, but they always end in "X" and "XCR".
 
It's Monday and getting on in the afternoon, so maybe I'm being a bit dense, but if one of the values is missing, you wouldn't have a "pair" to test:

CM776X
CM776XCR

DM776X

EM776XCR

FM776X
FM776XCR



< M!ke >
"Believe, but verify.
 
Correct, but if the part directly above or below has no match preceding the "X" or "XCR", then I want to know, so I can research. The list is sorted alphabetically, and they should be next to each other.

Let's say part # 8D40567 is above CM776XCR. Is there a way to test and create an exception that CM776XCR's paired # CM776X is missing, and the same in reverse?
 
Ok, so maybe I was being dense.

If you have these values starting in A1:

Code:
CM776X
CM776XCR
DM776X
EM776XCR
FM776X
FM776XCR

and paste this formula starting in B2 on down:
Code:
=IF(LEFT(A2,LEN(A1)-1)<>LEFT(A1,LEN(A1)-1),A1,"")

you'd get the following results:
Code:
CM776XCR
DM776X
EM776XCR

Is that kind of what you were looking for?

< M!ke >
"Believe, but verify.
 



You could use condirional formatting to highlight the cells that do not have a matching pair.

Assuming that your list begins in A2...
[tt]
Formula is: =OR(AND(RIGHT(A2,2)="CR",A2<>A1&"CR"),AND(RIGHT(A2,1)="X",A3<>A2&"CR"))
[/tt]


Skip,
[sub]
[glasses] [red][/red]
[tongue][/sub]
 
LNBruno - For your formula, should the -1 be -2? In your results, Part# CM776XCR should not be there since its paired # is present.


Skip - I only get a True / False returned. Is that what your formula is designed to return?
 
Actually, since both end with an "X", there wasn't a need to subtract...

What about something like this?
Code:
=IF(AND(RIGHT(A1,1)="X",RIGHT(A2,2)="CR"),"","missing part")


< M!ke >
"Believe, but verify.
 
The problem I keep hitting is that if you're missing one, there is no PAIR to test...I don't think you can have a formula that skips to the next row when the test condition is found.

Time for VBA?

< M!ke >
"Believe, but verify.
 
And, Skip - sorry! Just realized that I basically re-wrote your code post.



< M!ke >
"Believe, but verify.
 




It gets entered in the Conditional Formatting Window

NOT on the sheet!

Skip,
[sub]
[glasses] [red][/red]
[tongue][/sub]
 
You may be correct about moving to VBA. This formula didn't create a correct exception list.

Back to the drawing board.

Thanks for all your help Mike and Skip! =)
 
Hey! I totally missed that, Skip! Great job!

Star!

< M!ke >
"Believe, but verify.
 



If you use my CF formula on a SHEET, then it will return TRUE or FALSE. By the filtering on one or the other, you'll get the list that you need.

Skip,
[sub]
[glasses] [red][/red]
[tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top