CODEWITHSUNDEEP
excelvba
DL1
DL1

Reputation: 201

Look for values in column and match

Got damn it! Im stuck again.

I have tried to do some helper columns to sort out information from 1 column and then show me if there is a similiarity in the value.

What I have now is,

values in column A

D3875 HMG XS
D3875 BGE XS
D3874 BGE XS
I3874 BGE XS

I want to use to number to see if I have both D and I at the beginning of string because this means that my value is approved.

Edit- There are only some cells in the column that has the same value exept D and I at the beginning. In abowe example it is 

D3874 BGE XS
I3874 BGE XS

Could i somehow ( dont know how right now) lets say make a loop where i take the first value in column A such as D3875 HMG XS and use 3875 HMG XS too loop in the column untill i find a cell with the same value exept that it holds a I instead of D at the beginning?

Upvotes: 0

Views: 102

Answers (4)

Scott Craner
Scott Craner

Reputation: 152450

This will find if the same number is found with both an I and a D prefix, if so it will return Approved if not Not Approved:

=IF(COUNTIF(A:A,IF(LEFT(A1,1)="D","I","D")&MID(A1,2,LEN(A1))),"Approved","Not Approved")

enter image description here

NOTE:

This only works if I and D are the only prefixes, or you will get false positives.

Upvotes: 1

CallumDA
CallumDA

Reputation: 12113

Here is another way to achieve the same thing. Although if you ever intend to add more than just two conditions (I and D) then I'd go with this:

=IF(ISNA(MATCH(LEFT(A1),{"D","I"},0)),"Not Approved","Approved")

Which you could extend, to include more conditions, in either of these ways

=IF(ISNA(MATCH(LEFT(A1),{"D","I","A","B"},0)),"Not Approved","Approved")
=IF(ISNA(MATCH(LEFT(A1),A1:A4,0)),"Not Approved","Approved")

Where A1:A4 contains your conditions in the last example.

Upvotes: 1

KoderM16
KoderM16

Reputation: 157

You could use something like this:

In Cell B1:

=IF(LEFT(A1,1)="D","Approved",IF(LEFT(A1,1)="I","Approved","Not Approved"))

The result will be:

If the Text in A1 starts with D or I, B1 will say "Approved", otherwise, you guessed it, "Not Approved". Just drag down to reach all required cells. Hope this helps.

Upvotes: 2

Tim Wilkinson
Tim Wilkinson

Reputation: 3801

Use a combination of IF and LEFT to pull the first character and check if its D or I

=IF(OR(LEFT(A1, 1)="D", LEFT(A1, 1)="I"), "Approved", "Not Approved")

Upvotes: 3

Related Questions