+ Reply to Thread
Results 1 to 4 of 4

Listing all combinations of two variable data table that satisfy criteria

  1. #1
    Registered User
    Join Date
    04-23-2024
    Location
    Bristol, England
    MS-Off Ver
    365
    Posts
    2

    Smile Listing all combinations of two variable data table that satisfy criteria

    Hi,

    I have a two variable data table where I need to be able to list all of the combinations of actions that produce a green cell. The colour of the cell represents whether actions can be used with each other. This list will then be used to evaluate interactions between these actions with further data. I could list these eligible combinations by hand but realistically there will be a lot more actions with the real-world data. Any help would be greatly appreciated!!

    ExcelClip.JPG

    StackingActions.xlsm

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Listing all combinations of two variable data table that satisfy criteria

    The shading seems (to me) to be random - no logical rule has been applied. Is that correct?

    You didn't really tell us what you expect to see, so this is a guess. In Column AU there is a list of ALL Row/Column combinations (first letter=Row, 2nd letter = Column). The yellow cell has counted them.

    If this IS what you want, let me know and I'll explain. Change a few fills in the original data. It >>should<< update satisfactorily.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  3. #3
    Registered User
    Join Date
    04-23-2024
    Location
    Bristol, England
    MS-Off Ver
    365
    Posts
    2

    Re: Listing all combinations of two variable data table that satisfy criteria

    Hi Glenn,

    Thank you for the help! Legend! Yes the shading is intentionally random at this stage. The list of row/column combinations are exactly what I was looking for, apologies for the unclear description. An explanation would be great thanks.

    Maddy

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Listing all combinations of two variable data table that satisfy criteria

    In the original file, there were two named ranges. I forgot to delte one of them. This version has only the one. CTRL-F3 to view/edit. It's called cell fill.

    =GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-22))

    This uses an elderly Excel V4 macro. The 63 specifies cell fill. The 0 secifies the number of rows away from the "Starting Cell" (C7) the first result will be. The -22 specifies the number of columns away... 22 to the left. The 21 of your array and a space. A fill of 36 = your light green


    =CellFill in Y7 will return the fill of the cell on the same row, 22 columns to the left (i.e. C7). Drag across and dnown to complete a 21x21 cell grid, to match your original.

    I then wasted some time getting the results returned. The formula I used was falkey. I have no idea why. Sometimes it gave 103, sometimes none, sometimes something in between. By accident I found that if I returned the values as TEXT rather than as numbers, it ALWAYS gave 103 results.

    The formula, in AU7 is fairly straightforward:

    =TOCOL(IF(--Y7:AS27=36,B7:B27,z)&IF(--Y7:AS27=36,C6:W6,z),3)

    Red: the -- converts the text "numbers" back to real numbers. This bit says, if the number = 36, return (Blue) the column letter, otherwise return z (this causes Excel to return the #NAME error, as I didn't put the z inside "" to make it text.

    Cyan TOCOL(....,3) returns all the values in a single column, excluding errors (so the #NAME errors vanish)

    Purple joins the row letter with the black bit... which is the same as the first half... except it returns the column header.


    That's it.

    You're welcome. Thanks for letting us know that you got an answer.




    Please take a moment and consider clicking the "Add Reputation" button at the foot of any of the posts of anyone who helped you reach a solution here today.

    Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
    Attached Files Attached Files

+ 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. Generate permutations from a table that satisfy criteria
    By Tom_space9 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-19-2023, 08:26 PM
  2. [SOLVED] Listing of combinations for a peculiar data
    By hemjo in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 03-16-2015, 03:01 PM
  3. Replies: 5
    Last Post: 07-29-2013, 10:02 AM
  4. [SOLVED] listing all the combinations
    By lsubram123 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-07-2013, 11:26 PM
  5. [SOLVED] Listing all unique text combinations from three columns in a table
    By shabalgoel in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-17-2012, 10:33 AM
  6. Finding possible combinations & listing the wanted combinations
    By Zoke in forum Excel Programming / VBA / Macros
    Replies: 30
    Last Post: 07-16-2012, 03:41 PM

Tags for this Thread

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