How to compare 2 columns and count if conditions are met

[Anonymous]

I would like to check a number of barcodes if they are correctly matched with the correct product types. So for e.g. a barcode that begins with 111 is a hat , 123 is a shoe etc. Some products have been wrongly labeled. so 123 is put down as a hat instead of a shoe. So I want to filter this out. This is what I have tried so far and I will appreciate some suggestions. thanks in advance

Count({<Barcode = {'*^111*'}, [Product Type] = {'hat'}>} 1)

I also tried

Nested IF Statements‌

=Count(if(Barcode='*^111*' and [Product Type]= 'hat',1,

if((Barcode) = '*^123*' and [Product Type] = 'Shoe', 1,

if((Barcode) = '*^124*' and [Product Type] = 'Gloves', 1,

if((Barcode) = '*^125*' and [Product Type] = 'dress', 1,

if((Barcode) = '*^126*' and [Product Type] = 'Shirt', 1,

if((Barcode) = '*^127*' and [Product Type] = 'Tops', 1,0

)

)

)

)

)

)

[sunny_talwar]

Would you be able to elaborate what exactly are you trying to do and where with a help of any example?

[Anonymous]

Building number	Product type	Barcode
000045630	Hat	1110000001679
000045669	Shoe	1110000012345
000045634	Dress	1240000000001
000045634	Socks	1250000000025
000045634	Shirt	1260000034035
000045712	Shoe	1230000000444
000045731	Hat	1110000000799

in the table, you can see that the product type 'Shoe' appears as with barcode prefix - 111 and 123(1110000012345 and 1230000000444) This is inconsistent and so it is wrong.

I want to be able to check the barcode against the product type. so all hats have 111 and shoes are 123 ... etc

So

=Count(if(Barcode='*^111*' and [Product Type]= 'hat',1,

if((Barcode) = '*^123*' and [Product Type] = 'Shoe', 1,

if((Barcode) = '*^124*' and [Product Type] = 'Dress', 1,

if((Barcode) = '*^125*' and [Product Type] = 'Socks', 1,

if((Barcode) = '*^126*' and [Product Type] = 'Shirt', 1,0

)

)

)

)

)

does this make sense?

[sunny_talwar]

It does, but I am confused as to what is ^ used for? Do you may be need this

=Sum(If(

(WildMatch(Barcode, '111*') and [Product Type] = 'hat') or

(WildMatch(Barcode, '123*') and [Product Type] = 'Shoe') or

(WildMatch(Barcode, '124*') and [Product Type] = 'Dress') or

(WildMatch(Barcode, '125*') and [Product Type] = 'Socks') or

(WildMatch(Barcode, '126*') and [Product Type] = 'Shirt'), 1, 0))

If you notice, I removed the initial * also in the WildMatch function above, because you are checking the first 3 characters... adding * in the beginning will make it a wild card search anywhere in the string....

[Anonymous]

I used this ^ to define all barcode beginning with 111, 123 etc.

Since I am trying to ensure that all the barcodes that begin with 111, 123, 124... are hat, shoe, Dress... respectively(basically if it begins with 111 and its a shirt, then I want to count it as an error and flag it but if it begins with 111 and is a hat, then it is okay not to count as it is not an error),

I have now tried the following also

= Count(DISTINCT Aggr(If

(Barcode = '*^111*' and [Product Type] = 'hat') or

(Barcode = '*^123*' and [Product Type] = 'shoe') or

(Barcode = '*^124*' and [Product Type] = 'Dress'), 0, 1))

It however doesnt return the correct value I am looking for.  I am not sure if i have missed something. Please have a look?

[sunny_talwar]

Did you ever got a chance to try this?

=Sum(If(

(WildMatch(Barcode, '111*') and [Product Type] = 'hat') or

(WildMatch(Barcode, '123*') and [Product Type] = 'Shoe') or

(WildMatch(Barcode, '124*') and [Product Type] = 'Dress') or

(WildMatch(Barcode, '125*') and [Product Type] = 'Socks') or

(WildMatch(Barcode, '126*') and [Product Type] = 'Shirt'), 1, 0))

[Anonymous]

yes it didnt work as expected

[sunny_talwar]

Would you be able to share a sample to help you better?