+ Reply to Thread
Results 1 to 15 of 15

Compare rows and present how equal they are in a table

  1. #1
    Registered User
    Join Date
    05-09-2015
    Location
    Göteborg
    MS-Off Ver
    2013
    Posts
    5

    Compare rows and present how equal they are in a table

    Hello,

    I would like to present a commonality ratio for products passing through a number of processes. The table looks someting like this:
    Exempel.PNG

    I can change the "x"s to a digit if that makes it easier...

    I would like to compare how many processes they have in common and present it in a table like this:
    Commonality table.PNG

    Any ideas?

    Kind regards,
    Guran0

  2. #2
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Compare rows and present how equal they are in a table

    Hi,

    Welcome to the forum.

    Can you please post the sample template in excel format?

  3. #3
    Registered User
    Join Date
    05-09-2015
    Location
    Göteborg
    MS-Off Ver
    2013
    Posts
    5

    Re: Compare rows and present how equal they are in a table

    Hi cbatrody,

    Thank you for your feedback.

    The table looks like this:
    Commonality table.xlsx

    The models A-N are presented to the left and process on top. Yellow and "x" is indicating that the model is using that process.

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Compare rows and present how equal they are in a table

    I can' see the pictures that you posted so am taking a guess at what you want.

    The second file has values counting the number of commonalities for the process.
    Attached Files Attached Files
    Last edited by newdoverman; 05-10-2015 at 01:06 PM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Registered User
    Join Date
    05-09-2015
    Location
    Göteborg
    MS-Off Ver
    2013
    Posts
    5

    Re: Compare rows and present how equal they are in a table

    Newdoverman, thank you for your contribution but its not really what I was looking for.

    I would like a table that is showing commonality ratio i percent between the models. For example model A and model B has 73% (11 out of 15) of the processes in common.

    Ill try to repost an example of what it could look like.
    Commonality table.PNG

    A table like this:

    ___A__B__C ...
    A 100 73__80

    B____100_67

    C________100

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Compare rows and present how equal they are in a table

    One way:

    Row\Col
    R
    S
    T
    U
    V
    W
    X
    Y
    Z
    AA
    AB
    AC
    AD
    AE
    AF
    2
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    3
    A
    1.000
    0.692
    0.786
    0.538
    0.538
    0.846
    0.786
    0.833
    0.692
    0.538
    0.833
    0.538
    0.917
    1.000
    4
    B
    0.692
    1.000
    0.667
    0.667
    0.667
    0.600
    0.667
    0.692
    0.692
    0.667
    0.571
    0.667
    0.769
    0.692
    5
    C
    0.786
    0.667
    1.000
    0.533
    0.533
    0.929
    1.000
    0.786
    0.667
    0.533
    0.786
    0.533
    0.857
    0.786
    6
    D
    0.538
    0.667
    0.533
    1.000
    1.000
    0.571
    0.533
    0.667
    0.818
    1.000
    0.667
    1.000
    0.500
    0.538
    7
    E
    0.538
    0.667
    0.533
    1.000
    1.000
    0.571
    0.533
    0.667
    0.818
    1.000
    0.667
    1.000
    0.500
    0.538
    8
    F
    0.846
    0.600
    0.929
    0.571
    0.571
    1.000
    0.929
    0.846
    0.714
    0.571
    0.846
    0.571
    0.786
    0.846
    9
    G
    0.786
    0.667
    1.000
    0.533
    0.533
    0.929
    1.000
    0.786
    0.667
    0.533
    0.786
    0.533
    0.857
    0.786
    10
    H
    0.833
    0.692
    0.786
    0.667
    0.667
    0.846
    0.786
    1.000
    0.692
    0.667
    0.833
    0.667
    0.769
    0.833
    11
    I
    0.692
    0.692
    0.667
    0.818
    0.818
    0.714
    0.667
    0.692
    1.000
    0.818
    0.833
    0.818
    0.643
    0.692
    12
    J
    0.538
    0.667
    0.533
    1.000
    1.000
    0.571
    0.533
    0.667
    0.818
    1.000
    0.667
    1.000
    0.500
    0.538
    13
    K
    0.833
    0.571
    0.786
    0.667
    0.667
    0.846
    0.786
    0.833
    0.833
    0.667
    1.000
    0.667
    0.769
    0.833
    14
    L
    0.538
    0.667
    0.533
    1.000
    1.000
    0.571
    0.533
    0.667
    0.818
    1.000
    0.667
    1.000
    0.500
    0.538
    15
    M
    0.917
    0.769
    0.857
    0.500
    0.500
    0.786
    0.857
    0.769
    0.643
    0.500
    0.769
    0.500
    1.000
    0.917
    16
    N
    1.000
    0.692
    0.786
    0.538
    0.538
    0.846
    0.786
    0.833
    0.692
    0.538
    0.833
    0.538
    0.917
    1.000


    The formula in S3 and copied down and across is

    =COUNTIFS($B3:$P3, "x", col, "x") / (COUNTIF($B3:$P3, "x") + COUNTIF(col, "x") - COUNTIFS($B3:$P3, "x", col, "x"))

    ... which uses the dynamic named range col to retrieve the row specified by the column header:

    With S3 selected, define col refers to:=INDEX(Blad1!$B$3:$P$16, MATCH(Blad1!S$2, Blad1!$A$3:$A$16), 0)

    The 0.692 for A and B reflects that 9 of the 13 processes used by either one are used by both.
    Last edited by shg; 05-10-2015 at 02:44 PM.
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Compare rows and present how equal they are in a table

    If you want the result upper triangular, change the formula to

    =IF(S$2 < $R3, "", COUNTIFS($B3:$P3, "x", col, "x") / (COUNTIF($B3:$P3, "x") + COUNTIF(col, "x") - COUNTIFS($B3:$P3, "x", col, "x")))

    Row\Col
    R
    S
    T
    U
    V
    W
    X
    Y
    Z
    AA
    AB
    AC
    AD
    AE
    AF
    2
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    3
    A
    1.000
    0.692
    0.786
    0.538
    0.538
    0.846
    0.786
    0.833
    0.692
    0.538
    0.833
    0.538
    0.917
    1.000
    4
    B
    1.000
    0.667
    0.667
    0.667
    0.600
    0.667
    0.692
    0.692
    0.667
    0.571
    0.667
    0.769
    0.692
    5
    C
    1.000
    0.533
    0.533
    0.929
    1.000
    0.786
    0.667
    0.533
    0.786
    0.533
    0.857
    0.786
    6
    D
    1.000
    1.000
    0.571
    0.533
    0.667
    0.818
    1.000
    0.667
    1.000
    0.500
    0.538
    7
    E
    1.000
    0.571
    0.533
    0.667
    0.818
    1.000
    0.667
    1.000
    0.500
    0.538
    8
    F
    1.000
    0.929
    0.846
    0.714
    0.571
    0.846
    0.571
    0.786
    0.846
    9
    G
    1.000
    0.786
    0.667
    0.533
    0.786
    0.533
    0.857
    0.786
    10
    H
    1.000
    0.692
    0.667
    0.833
    0.667
    0.769
    0.833
    11
    I
    1.000
    0.818
    0.833
    0.818
    0.643
    0.692
    12
    J
    1.000
    0.667
    1.000
    0.500
    0.538
    13
    K
    1.000
    0.667
    0.769
    0.833
    14
    L
    1.000
    0.500
    0.538
    15
    M
    1.000
    0.917
    16
    N
    1.000

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Compare rows and present how equal they are in a table

    Or, a little simpler,

    =IF(S$2 < $R3, "", COUNTIFS($B3:$P3, "x", col, "x") / (COUNTIF($B3:$P3, "x") + COUNTIFS($B3:$P3, "", col, "x")))

  9. #9
    Registered User
    Join Date
    05-09-2015
    Location
    Göteborg
    MS-Off Ver
    2013
    Posts
    5

    Re: Compare rows and present how equal they are in a table

    Thank you very much shg, that is exactly what I am looking for!

    But my experience level with excel isnt very high and Im not able to get it to work... Would you be able to post the .xlsx file?

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Compare rows and present how equal they are in a table

    Attached .
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    05-09-2015
    Location
    Göteborg
    MS-Off Ver
    2013
    Posts
    5

    Re: Compare rows and present how equal they are in a table

    Fantastic! Thank you very much!

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Compare rows and present how equal they are in a table

    You're welcome.

  13. #13
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Compare rows and present how equal they are in a table

    Could someone tell me where I went wrong with my interpretation of the problem as my values are very different. I didn't get to creating a single formula to copy.
    Attached Files Attached Files

  14. #14
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Compare rows and present how equal they are in a table

    Your formula divides by 15 (the total number of processes), rather than the number of processes in the union of the two items. So two items that were identical, sharing a single common process, would have a result of 1/15, rather than 1/1.
    Last edited by shg; 05-11-2015 at 03:07 PM.

  15. #15
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Compare rows and present how equal they are in a table

    This had me confused because I was looking only for processes that were shared (both have an X). Without the /15 I had a count of those "shared processes" and the /15 gave me a decimal fraction of the total available processes.

    Your explanation makes perfect sense. I wasn't accounting for processes only done by 1 of the two row combination....thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Compare a table against rows and update if row not in the table.
    By brent_milne in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-12-2015, 02:32 PM
  2. Replies: 2
    Last Post: 09-03-2014, 03:37 AM
  3. huge table to assemble colomns and rows when to equal to 0
    By melieetnala in forum Excel General
    Replies: 1
    Last Post: 08-11-2014, 05:49 PM
  4. [SOLVED] Compare two worksheets and present differences in a third
    By olivierpbeland in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 10-28-2013, 03:14 PM
  5. [SOLVED] Auto Hiding rows based on range/data present or not present.
    By raze in forum Excel Programming / VBA / Macros
    Replies: 27
    Last Post: 02-10-2013, 11:27 AM

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