Matching Duplicates then searching for a specific value

[asusser]

I have the following table:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17 x:num>91</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" width=64>j561</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>91</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">qa</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>91</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">rdock</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>56</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">rj</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>71</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">jr</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>91</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">qst</TD></TR></TBODY></TABLE>

If "qa" shows up in the second column, then the number associated with it in the first column (91 in the example) is now in the clear status so we do not need to audit it. In this example, all of the 91s can be removed from the list regardless of what the corresponding letter is in the second column since at least one of the 91s has a qa in the second column.

How can I create a macro or formula in the third column to show me which items still need to be audited and which do not?

 

[Claymationator]

Welcome to the board!

You could apply a filter to your data, and filter out all of the 91's in column A.

 

[asusser]

That would work but the entire spreadsheet is 5000 entries long with hundreds of number combinations which would take a LOT of filtering.

 

[Claymationator]

Can you post back with the combinations that you need to filter out?

In your original post you mention that you want to filter out "qa" from the second column, and then you later mention that all 91's can be filtered out of the first column. What other combinations are there that need to be accounted for?

 

[asusser]

Column 1 Column 2
9345690 gr
8293380 ap
9345690 qa
8293829 qa
2897290 jjm
8293829 xlv

Here's another example. The number combinations can change daily but if any number is followed by qa in column 2, I can cancel every instance of that number out. 9345690 is an example--I can eliminate that number because at least one instance is followed by QA in the next column.

 

[c_m_s_jr]

I have the following table:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17 x:num>91</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; WIDTH: 48pt; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" width=64>j561</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>91</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">qa</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>91</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">rdock</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>56</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">rj</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>71</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">jr</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>91</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">qst</TD></TR></TBODY></TABLE>

If "qa" shows up in the second column, then the number associated with it in the first column (91 in the example) is now in the clear status so we do not need to audit it. In this example, all of the 91s can be removed from the list regardless of what the corresponding letter is in the second column since at least one of the 91s has a qa in the second column.

How can I create a macro or formula in the third column to show me which items still need to be audited and which do not?

In Column C (assuming the number and qa signify no audit needs to be performed on any group):

=IF(ISNA(MATCH(A2&"qa",INDEX($A$3:$A$10&$B$3:$B$10,0),0)),"","NO AUDIT")

If the item needs to be Audited it will be blank all others will return "No Audit". You can then apply a filter and delete all of the records that say no audit.

 

[asusser]

Works like a charm! Thank you!

 

[Claymationator]

Deleted as working solution was provided.