Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Classify Data Based Upon Cell Value

    Hi, looking for help categorizing data. I think it can be done w/formulas but I don't know how. See attached spreadsheet. All help is appreciated!!!

    Mitch
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    First shot.

    I unmerged the cells in the J column and placed this formula in J2 and filled down.

    =IF(A1=A2,"",IF(COUNTIFS(A:A,A2,J:J,J2)=COUNTIF($A :$A,INDIRECT("A"&ROW())),"NONABC","Shared"))

    Other than using a Pivot Table, there must be an easier way...just didn't see it initially.

    I always try for what works first, then try for what is slick.
    Last edited by kweaver; 2014-10-24 at 15:20.

  3. #3
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by kweaver View Post
    first shot.

    I unmerged the cells in the j column and placed this formula in j2 and filled down.

    =if(a1=a2,"",if(countifs(a:a,a2,j:j,j2)=countif($a :$a,indirect("a"&row())),"nonabc","shared"))

    other than using a pivot table, there must be an easier way...just didn't see it initially.

    I always try for what works first, then try for what is slick.
    I tried in a different sheet and it didn't work... I will check into and come back to you. Thanks.
    Last edited by mitch06251962; 2014-10-24 at 15:47.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Mitch

    see kweavers formula in attached file.

    zeddy
    Attached Files Attached Files

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I've also attached my sheet which used conditional formatting to block the cell ranges.
    Attached Files Attached Files

  6. #6
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by kweaver View Post
    I've also attached my sheet which used conditional formatting to block the cell ranges.
    Ok. I am getting there. SO, what if it has one more condition in it that I didn't list before? How to make this work? See bottom of this in yellow... I didn't see a button to reattach file...

    aa002808 FM FLAVOR MATERIAL 198 10,000.00 LB 0.491 LB 0 ABCCANADA
    aa002809 FM FLAVOR MATERIAL 198 10,000.00 LB 0.491 LB 0 ABCCANADA NONABC SHOULD SAY ABCCANADA
    aa002809 FM FLAVOR MATERIAL 198 10,000.00 LB 0.491 LB 0 ABCCANADA
    aa002809 FM FLAVOR MATERIAL 198 10,000.00 LB 0.491 LB 0 ABCCANADA
    aa002809 FM FLAVOR MATERIAL 198 10,000.00 LB 0.491 LB 0 ABCCANADA
    aa002809 FM FLAVOR MATERIAL 198 10,000.00 LB 0.491 LB 0 ABCCANADA
    aa002809 FM FLAVOR MATERIAL 198 10,000.00 LB 0.491 LB 0 ABCCANADA

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Attach your new file. Not sure what the other condition is.

  8. #8
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by kweaver View Post
    Attach your new file. Not sure what the other condition is.
    should be in here now.
    Attached Files Attached Files

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    How about this adjustment? See the attached.
    Attached Files Attached Files

  10. #10
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    KW. Yes, this gets it... How does this magic work???

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Well...

    =IF(A1=A2,"",IF(COUNTIFS(A:A,A2,J:J,J2)=COUNTIF($A :$A,INDIRECT("A"&ROW())),J2,"Shared"))

    I checked to see if in a block of cells that had the same component code and identical ABC? codes (from the J column) and if that number of cells matched the number of cells that were the same as in the corresponding J cell. If so, I inserted the cell data; otherwise, inserted "Shared" in the cell.

    The first test for A1=A2 was to see if the cells needed to be checked (i.e., only the first cell in a block needs to be checked) because the other cells either all match or don't (Shared).

    To put the borders on, check the formula in the Conditional Formatting.

    HTH.

Posting Permissions

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