Need help identifying version x of a code per product type.
I have 3 columns, the first column contains a code (random number), the second columns contains the version of the code, the third colum contains the product within the code.
Looks like this:
A B C
48996 1 48996-1
48996 0 48996-1
48996 2 48996-2
47543 8 47543-11
47543 7 47543-10
40004 0 40004-3
41456 0 41456-4
41762 0 41762-1
It reads, product type 48996-1 has two versions: 0 and 1
I need to identify the codes that only have had version '0'. these codes can't have version '1' or '2' or '3' per product type (Column C)
From the data above, the codes that meet my criteria are: 40004 for product type 40004-3, 41456 for product type 41456-4, and 41762 for product type 41762-1.
My total dataset contains 400K rows. Help!
Use Conditional Formatting on columns A, B & C with:
=AND($A1<>"",$B1=0,RIGHT($C1,1)+0>1)
---
Ben Van Johnson
Thank you. However, the formula does not help me identify the lowest version per product type
See attachment. Book1.xlsx
The version is in Column B.
The formula should only give me the value in column C if:
1. the value in column C has not had any duplicates (it only showed once)
AND
2. the version in column B = zero
Why don't you just use filter. 5 seconds.
To thank someone who has helped you, click on the star icon below their name.
I hate reading
Portfolio
I need a job.I am young and incompetent
That would be lovely. However a value in ColumC (Product Type) can be associated to '0', or '1' or '4' or 'N' in column B
If I select the filter option to only show me column B=0, then i could be including values in column c that should not be there because they have had more versions (zero and another)
My dataset has several row entries...maybe the excel sheet i posted did not have all cases. this is why i am looking for a formula.
o.k.:
=AND($A2<>"",COUNTIF($A:$A,$A2)=1,$B2=0,RIGHT($C2,1)+0>1)
---
Ben Van Johnson
Thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks