Formula Needed

[MikeDBMan]

I need a formula that will do this:
1. I have a list of 8 codes.
2. The list may contain duplicates.

What I need is a formula in 8 cells of another list that is based on the initial list.
1. It is also 8 cells of data.
2. The data will contain each code from the initial list but only one time.
3. There may be a specific code, "BAD1" or "BAD2" that I need to omit.
4. If I have found all the unique codes that are not "BAD1" or "BAD2", then the formula returns "".

So if My initial list is:
CODE1
CODE2
CODE3
CODE1
CODE4
BAD1
EMPTY CELL
EMPTY CELL

Then my resulting list would be (order is not important except the "" cells at the end):
CODE1
CODE2
CODE3
CODE4
""
""
""
""

Can anyone help me with the formulas that would be in the resulting list cells? Thanks.

 

[GRTorres]

Hello MikeDBMan, are you comfortable with VBA and/or macros? This would make it much more simpler. On your worksheet hit Alt + F11 and paste this code, it should do the trick. Let me know if this is not what you are looking for.

Sub FindAndRemove()
Dim i As Long
Dim LastRow As Long
     
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = LastRow To 1 Step -1
    If Application.WorksheetFunction.CountIf(Range("A1:A" & i), Range("A" & i).Text) > 1 Then
            Range("A" & i).EntireRow.Delete
    End If
    If Left(Range("A" & i), 1) = "B" Or Range("A" & i) = "" Then
        Range("A" & i).EntireRow.Delete
        End If
Next i
For i = Range("A" & Rows.Count).End(xlUp).Row + 1 To 8
    Range("A" & i) = """"
    Next i
    
End Sub

 

[MikeDBMan]

I am comfortable with VBA and macros. However the users who will be using this can change data from time to time in the first table and I would like formulas in the 8 cells of the second table that auto-allow for their changes. I could handle it via macros, which is what I have been doing, but was wanting a more efficient method to create the second list. Thanks for the help though.

 

[GRTorres]

i placed your second table in column b, you can move it where you like, I also assumed you had a header.

B2 =IF(AND(LEFT($A$2,1)<>"B",$A$2<>""),$A$2,"""""")
B3 =IF(AND(LEFT($A$3,1)<>"B",$A$3<>""),IF(ISNA(VLOOKUP($A$3,$B$2,1,FALSE)),$A$3,"""""), """""""))
B4 =IF(AND(LEFT($A$4,1)<>"B",$A$4<>""),IF(ISNA(VLOOKUP($A$4,$B$2:$B$3,1,FALSE)),$A$4,""""""), """""")
B5 =IF(AND(LEFT($A$5,1)<>"B",$A$5<>""),IF(ISNA(VLOOKUP($A$5,$B$2:$B$4,1,FALSE)),$A$5,""""""), """""")
B6 =IF(AND(LEFT($A$6,1)<>"B",$A$6<>""),IF(ISNA(VLOOKUP($A$6,$B$2:$B$5,1,FALSE)),$A$6,""""""), """""")
B7 =IF(AND(LEFT($A$7,1)<>"B",$A$7<>""),IF(ISNA(VLOOKUP($A$7,$B$2:$B$6,1,FALSE)),$A$7,""""""), """""")
B8 =IF(AND(LEFT($A$8,1)<>"B",$A$8<>""),IF(ISNA(VLOOKUP($A$8,$B$2:$B$7,1,FALSE)),$A$8,""""""), """""")
B9 =IF(AND(LEFT($A$9,1)<>"B",$A$9<>""),IF(ISNA(VLOOKUP($A$9,$B$2:$B$8,1,FALSE)),$A$9,""""""), """""")

Then once in place, you can switch your table 1 values to test it out. You will have to sort the list on your own, which is simple enough. Once sorted, caution, if you attack the integrity of the formula, you in a way restart the code and it will then calculate over.