+ Reply to Thread
Results 1 to 9 of 9

How to look up COLUMN and ROW headings that match table value

  1. #1
    Registered User
    Join Date
    08-18-2015
    Location
    Brussels
    MS-Off Ver
    2013
    Posts
    24

    How to look up COLUMN and ROW headings that match table value

    I have a problem that bugs me a lot. It is sort of reverse lookup issue. I have a table that I want to find ALL combinations of column and row headings that match a condition for a value in a table.

    Let me illustrate:


    10:00 11:00 12:00
    1 2 3 run
    5 6 7 kayak
    2 3 4 bike


    I would like to get two tables. One is by getting values of column and row headings for each value in the table

    1: 10:00 - run
    2: 10:00 - bike
    2: 11:00 - run
    3: 11:00 - bike
    3: 12:00 - run
    4: 12:00 - bike
    5: 10:00 - kayak
    6: 11:00 - kayak
    7: 12:00 - kayak


    Second: value for each activity:

    run: 10:00 - 1
    run: 11:00 - 2
    run: 12:00 - 3
    bike: 10:00 - 2
    bike: 11:00 - 3
    bike: 12:00 - 4
    ...

    What I tried to do was to take the original matrix and run vlookup by putting in lookup value each value from the matrix (1,2,3,4,5,..) and then run it for each column of (10:00, 11:00, etc). However, I feel that that is not efficient. Any suggestions. Same for the second part of the problem.

  2. #2
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: How to look up COLUMN and ROW headings that match table value

    the second part can be done using INDEX-MATCH combination. For e.g.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    for the first list, it is unclear how you want it to work. Do you want to simply enter the value and have a formula return its row heading and column heading? If so, what happens in case of a duplicate value? or do you simply want to list every single value with its row heading and column heading? please attach a sample workbook showing the input and the desired solution.

  3. #3
    Registered User
    Join Date
    08-18-2015
    Location
    Brussels
    MS-Off Ver
    2013
    Posts
    24

    Re: How to look up COLUMN and ROW headings that match table value

    I am attaching the original problem. It is multifaceted and I tried to simplify it.

    I start with the original schedule. That is on the list one. It is prepared for each activity and lists teams per each time window.

    Then I want to make a schedule for all teams - list two. I have made the list manually but that is very laborious. This is where I need help to create it automatically with formulas.

    Tnen I want to make a schedule for each individual team - list three. Example is given. Also need help here how to automate.

    Finally, - need a schedule per each activity. I have made an example again. Would like help with formulas too.

    Does that help?

    Vytenis
    Attached Files Attached Files

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to look up COLUMN and ROW headings that match table value

    vytenis12,

    I find that I am having to guess which list goes with which .... and from there

    Could you try re-posting post #3 and designate by specific sheet names and range sections what it is you wish to do with each?

    It might help. This looks like one of those 'projects-difficult-to-explain'. We're going to need your help on this.
    Dave

  5. #5
    Registered User
    Join Date
    08-18-2015
    Location
    Brussels
    MS-Off Ver
    2013
    Posts
    24

    Re: How to look up COLUMN and ROW headings that match table value

    My appologies. I am reposting with list names:

    I am attaching the original problem. It is multifaceted and I tried to simplify it.

    I start with the original schedule[LIST: Schedule-per-activity]. That is on the list one. It is prepared for each activity and lists teams per each time window.

    Then I want to make a schedule for all teams - list two[LIST: Schedule-per-team]. I have made the list manually but that is very laborious. This is where I need help to create it automatically with formulas.

    Tnen I want to make a schedule for each individual team - list three[LIST: 1 team sheet]. Example is given. Also need help here how to automate.

    Finally, - need a schedule per each activity. I have made an example again[LIST: Football]. Would like help with formulas too.

    Does that help?

    Vytenis

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to look up COLUMN and ROW headings that match table value

    It's a good start. I may have more questions as we go.

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to look up COLUMN and ROW headings that match table value

    vytenis12,

    You've chosen to use Tables for your source data (Schedule-per-activity) and output range (Schedule-per-team).

    In formula those require a peculiar syntax that is unwieldy to work with.

    If I return those tables to regular data (non-Tables) will that cause difficulties for you?

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to look up COLUMN and ROW headings that match table value

    I was able to go ahead with the first problem without converting the Tables to ranges.

    I took the liberty of re-arranging your setup of Table2 in ‘Schedule-per-activity’. It is generally advantageous to have contiguous headers of times and Activities.

    The result is a Table2 that is offset at the second half of times and Activities. It looks odd but works better.

    I also realigned the Tables of both ‘Schedule-per-activity’ and ‘Schedule-per-team’ so they both start in the same row.

    I made a copy of Table1 for cross-checking. It starts in column T.

    The time headers need to exactly match. There were extra spaces in some that were not in others. I took care of that. It wasn’t an issue for this part of the project, but it is likely to be one later.

    Array enter this formula in C3 of ‘Schedule-per-team’. Fill down and across to R34.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

    Please let me know if this takes care of the first part. Will continue from there when I hear from you.

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to look up COLUMN and ROW headings that match table value

    I went ahead with the next step.

    I filled out ‘Schedule-per-team’ in order to make ‘1 team sheet’ work.

    I changed the reference in B1 from Team 1 to 1 team, 2 team, 3 team, etc. so that they would match the Table1 row headers.

    Rather than make 32 separate sheets I put enough # team sections in one sheet to make certain it would work for the offsetting “Rests” in Table1 (which begin with 17 team).

    For 1 team array enter this formula (Ctrl + Shift + Enter) into B2. Fill down to B10.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then in C2:C10 this non-array formula.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Copy this range including the numbers in column A and paste below each heading of # teams. (You’ll need to change the row references for teams in the new rows below.)

    Now when you change data in ‘Schedule-per-activity’ the other sheets will update accordingly.

    I find no data for points or rankings so I am not able to do the last step in sheet ‘Football’.

    Please see the attached.

+ 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. Three Variable Match / Index (With different column headings)
    By burgie10 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-04-2015, 12:54 PM
  2. Match files to column headings
    By visualnewbie09 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-31-2014, 10:40 AM
  3. Replies: 0
    Last Post: 04-11-2014, 06:07 PM
  4. Macro to match column headings and paste into next available row.
    By vizzy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-06-2013, 10:38 AM
  5. INDEX MATCH and change column headings
    By thart21 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-22-2011, 11:19 AM
  6. Match any value in row array to column headings
    By learnerabc in forum Excel General
    Replies: 12
    Last Post: 11-15-2010, 03:35 AM
  7. Can I invert a table so row headings are now column headings etc
    By Sharon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-10-2005, 04:06 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