+ Reply to Thread
Results 1 to 7 of 7

Need help identifying version x of my data?

  1. #1
    Registered User
    Join Date
    01-22-2012
    Location
    Miami, FL
    MS-Off Ver
    Excel 2007
    Posts
    4

    Need help identifying version x of my data?

    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!

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,601

    Re: Need help identifying version x of my data?

    Use Conditional Formatting on columns A, B & C with:
    =AND($A1<>"",$B1=0,RIGHT($C1,1)+0>1)
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    01-22-2012
    Location
    Miami, FL
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Need help identifying version x of my data?

    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


  4. #4
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Need help identifying version x of my data?

    Why don't you just use filter. 5 seconds.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-22-2012
    Location
    Miami, FL
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Need help identifying version x of my data?

    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.

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,601

    Re: Need help identifying version x of my data?

    o.k.:
    =AND($A2<>"",COUNTIF($A:$A,$A2)=1,$B2=0,RIGHT($C2,1)+0>1)

  7. #7
    Registered User
    Join Date
    01-22-2012
    Location
    Miami, FL
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Need help identifying version x of my data?

    Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1