+ Reply to Thread
Results 1 to 8 of 8

Counting the number of differing values across a group of cells

  1. #1
    Registered User
    Join Date
    10-18-2007
    Posts
    4

    Counting the number of differing values across a group of cells

    hi, can anyone help me with a problem that has me stumped?
    i am trying to calculate the number of times a different combination of 2 cells appears across a range of cells - please see sample below:

    A B C D E F G H I J K L
    E E E E F F F F E E F F

    So for the combination of the value in Cell A+B - I need to count the number of different values across A+B and E+F and I+J - so in this case it would be 2, as we ignore cells C,D,G,H,K,L etc.

    Does this make sense? The cells are basically languages that I am trying to calculate the amount of localisation and translation work required - so the values per cell can have up to 18 differing combinations per cell.

    Thanks

    James

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Hello James,

    Welcome to the forum.

    With your letters in A:L, put this in A2 and copy across to L2

    =CODE(A1), then in A3

    =IF(A2=B2,A2,"") copy formula to E3 and I3, then in M3 put this

    =SUMPRODUCT((1/COUNTIF(A3:L3,A3:L3&"")))-1

    Does that work for you?
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Registered User
    Join Date
    10-18-2007
    Posts
    4
    Thanks OldChippy

    I am still lost, but am convinced it is becuase i didnt explain it... I have attached an extrace of the file with comments on - can you possibly look at it and let me know if there is a forumla i can use for this?
    Attached Files Attached Files

  4. #4
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Just explain from your first comment box,

    number of differing cells contained in I4 and J4 do you mean number of differing characters in each cell?

  5. #5
    Registered User
    Join Date
    10-18-2007
    Posts
    4
    Thank you for keeping on trying... what am i like??

    i dont want to count what is in each cell, but I want to count the combination of the 2 cells vs the combination of the next 2 cells that are positioned 4 cells to the right etc across the row.

    So I4 with J4 make up 1 product, and that product is for the UK, but our French teams may also use the same product - therefore the I4 with J4 would be the same as M4 with N4, but then Germany may take a different product, so although Q4 may be the same as M4 and I4, R4 may be different. So that is why it is important to look at the 2 cells together as making up 1 whole product (I4+J4) (M4+N4) (Q4+R4) repeating every block of 4 till the end of the row.

    I think i have totally confused everyone readingt this now

    Thank you...

  6. #6
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    There may be a better way to do this, but does this help?

  7. #7
    Registered User
    Join Date
    10-18-2007
    Posts
    4
    THANK YOU!!!!

    Saved me endless hours of adding up silly cells... more time in the pub and watching the rugby...


    Thanks again

  8. #8
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Well thats got to be worth it then - thanks for the feedback

+ 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