Flag columnA only if column B matches

[Goldbadger]

The description sounds simple but what I need is a bit more complicated.

I have a table of transactions.

Column A contains part numbers
Column B contains a list of transaction codes.

Now each part number may have multiple transaction codes.

What I need is to flag if a part number has a TYPE1 transaction AND a TYPE2 OR TYPE3

for example the following image shows a TYPE1 and TYPE2 transaction. In column C I would want a flag to say there is a match.

ADJ.JPG

I have tried this:

Please Login or Register  to view this content.

but it doesn't work, any ideas?

Thanks

[Pete_UK]

You could try it like this:

=SUMPRODUCT(([PartNumber]=[@PartNumber])*([TransactionCode]="TYPE1")*(([TransactionCode]="TYPE2")+([TransactionCode]="TYPE3")))

Hope this helps.

Pete

[Goldbadger]

Thanks for the reply Pete, I tried the formula and it returns a zero.

I have attached an image to my original post. I was hoping to return a 1 or something to flag there was a match. Maybe I'm asking for the wrong formula?

[Pete_UK]

We can't do much with a picture - it would be better if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

[Goldbadger]

thanks, please see attached.

[JohnTopley]

Try

=IF(COUNTIFS([PartNumber],[@PartNumber],[TransactionCode],"TYPE1"),IF(COUNTIFS([PartNumber],[@PartNumber],[TransactionCode],"TYPE2")+COUNTIFS([PartNumber],[@PartNumber],[TransactionCode],"TYPE3"),1,""),"")

[Goldbadger]

Hi John,

Thank you, your formula works perfectly.

Pete, thanks for your assistance, much appreciated.