Formula that lists out a unique value

Hello All,

Thanks in advance for any help...

Please see attached spreadsheet.

I need a formula in Column H that will look at Column C and G.

If the Column G has only "BASE" or "Blank" for Column C to return "No WC"
If the Column G has only Anything other then "Base" or "Blank" to state what it is. I've used for example SAW, GOW and HHHC in my example; but this could be anything. But if it has something not Blank or "BASE" to not put in "No WC"

Also, I would want "No WC" to only show up once for that reference number (Column C) as Column C maybe sorted differently, I would only want the "No WC" on the first time it comes up.

Is such a formula possible?

Thanks,

-Jason

Not sure if this helps as I was trying to figure this outmyself but I don't know how to find unique values.

In second sheet I put in the formula in Column I:

=IF(ISNUMBER(SEARCH("*Base*",G4)),"",G4)

This removed Base and made it blank. And then anything else woulc come over. I highlighted in blue the values that should be No WC.

Is there a way to make this one formula?

Thanks,

Originally Posted by ChemistB

Try in H4 copied down

=IF(AND(G4<> {"base",""}), G4, IF((COUNTIFS($C$4:$C$23,C4, $G$4:$G$23, "Base")+COUNTIFS($C$4:$C$23,C4, $G$4:$G$23, ""))=COUNTIF($C$4:$C$23, C4), IF(C3=C4,"","No WC"),""))

You didn't put anything in H16 and I think that should have "No WC" If not, explain.

Awesome! This works perfectly. H16 was my human error (hence why i love excel).

this is great "(AND(G2<> {"base",""})"

Just for my own reference what does the { and } do?

Thanks for the explaination. I will have to try this!