Creating an Exception List in Excel 2

[zinderellie]

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!

 

[SkipVought]

Hi,

Are these pairs ALWAYS a length of 6 & 8?

Skip,
[sub]
[red][/red]
[/sub]

 

[zinderellie]

The length varies, but they always end in "X" and "XCR".

 

[LNBruno]

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.

 

[zinderellie]

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?

 

[LNBruno]

Ok, so maybe I was being dense.

If you have these values starting in A1:

CM776X
CM776XCR
DM776X
EM776XCR
FM776X
FM776XCR

and paste this formula starting in B2 on down:

=IF(LEFT(A2,LEN(A1)-1)<>LEFT(A1,LEN(A1)-1),A1,"")

you'd get the following results:

CM776XCR
DM776X
EM776XCR

Is that kind of what you were looking for?

< M!ke >
"Believe, but verify.

 

[SkipVought]

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]
[red][/red]
[/sub]

 

[zinderellie]

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?

 

[LNBruno]

Actually, since both end with an "X", there wasn't a need to subtract...

What about something like this?

=IF(AND(RIGHT(A1,1)="X",RIGHT(A2,2)="CR"),"","missing part")

< M!ke >
"Believe, but verify.

 

[LNBruno]

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.

 

[LNBruno]

And, Skip - sorry! Just realized that I basically re-wrote your code post.



< M!ke >
"Believe, but verify.

 

[SkipVought]

It gets entered in the Conditional Formatting Window

NOT on the sheet!

Skip,
[sub]
[red][/red]
[/sub]

 

[zinderellie]

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! =)

 

[LNBruno]

Hey! I totally missed that, Skip! Great job!

Star!

< M!ke >
"Believe, but verify.

 

[SkipVought]

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]
[red][/red]
[/sub]