+ Reply to Thread
Results 1 to 10 of 10

Bring back a name in one column based on criteria in 2 other columns

  1. #1
    Forum Contributor
    Join Date
    12-13-2012
    Location
    Shropshire, England
    MS-Off Ver
    Excel 2007 /10 /13
    Posts
    118

    Bring back a name in one column based on criteria in 2 other columns

    I have a sheet that is used to rota certain tasks for people over a weekly period, it is laid out as column A has all the names in (repeted for each of 14 tasks). Column B has all the tasks in, then columns C:G are the 5 days. Columns C:G have an "X" in if that person is on that task that day.

    What I am trying to do is translate that table into another that has one (or more) rows for a task, still has the columns for days, but then puts the name into the corresponding cell, so basically, I want to populate the cell in columns C:G with the value in column A, based on criteria for Columns B and C being met, and to look for the next value if already met (as there may be several on that task on that day)

    Can anyone please help with this, as I'm stuck on it now.

    Thanks
    Last edited by BillDoor; 04-18-2013 at 05:18 AM. Reason: updated info

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: Bring back a name in one column based on criteria in 2 other columns

    Prob best post the spreadsheet
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Contributor
    Join Date
    12-13-2012
    Location
    Shropshire, England
    MS-Off Ver
    Excel 2007 /10 /13
    Posts
    118

    Re: Bring back a name in one column based on criteria in 2 other columns

    have attached, I've added a few notes to the sheet itself to try and explain what I'm trying to do

    thanks for your help
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    12-13-2012
    Location
    Shropshire, England
    MS-Off Ver
    Excel 2007 /10 /13
    Posts
    118

    Re: Bring back a name in one column based on criteria in 2 other columns

    Hi, has anyone any ideas how to do this please?
    thanks

  5. #5
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Bring back a name in one column based on criteria in 2 other columns

    perhaps:
    in C3:
    =IFERROR(INDEX('Weekly Rota'!$B$3:$B$478,SMALL(IF('Weekly Rota'!D$3:D$478="X",IF('Weekly Rota'!$C$3:$C$478=$A3,ROW('Weekly Rota'!$D$3:$D$478),""),""),COUNTIF(Summary!$A$2:$A2,$A3)+1)),"")
    array-entered with ctrl+shift+enter then fill right and down
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  6. #6
    Forum Contributor
    Join Date
    12-13-2012
    Location
    Shropshire, England
    MS-Off Ver
    Excel 2007 /10 /13
    Posts
    118

    Re: Bring back a name in one column based on criteria in 2 other columns

    Hi, thanks for that, but I can't seem to get it to work, I've copied the formula as an array and copied to the rest of the sheet, but it's not pulling any data through.

    I've attached the updated sheet, can you see what I've done wrong?

    thanks
    Attached Files Attached Files

  7. #7
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Bring back a name in one column based on criteria in 2 other columns

    none of your tasks actually match-for instance you have 'Task 1' on one sheet and 'Task1' on the other ;-)

  8. #8
    Forum Contributor
    Join Date
    12-13-2012
    Location
    Shropshire, England
    MS-Off Ver
    Excel 2007 /10 /13
    Posts
    118

    Re: Bring back a name in one column based on criteria in 2 other columns

    ah, good point, I've changed them all to match now and it seems to work up to a point, Task 13 and Task 14 don't seem to bring back the right names, I've put some random names in to make it a little easier to see, all the others work fine in this sheet, except these last two.
    Attached Files Attached Files

  9. #9
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Bring back a name in one column based on criteria in 2 other columns

    my bad-forgot to adjust for data starting in row 3 when indexing
    =IFERROR(INDEX('Weekly Rota'!$B$3:$B$478,SMALL(IF('Weekly Rota'!D$3:D$478="X",IF('Weekly Rota'!$C$3:$C$478=$A3,ROW('Weekly Rota'!$D$3:$D$478)-2,""),""),COUNTIF(Summary!$A$2:$A2,$A3)+1)),"")

  10. #10
    Forum Contributor
    Join Date
    12-13-2012
    Location
    Shropshire, England
    MS-Off Ver
    Excel 2007 /10 /13
    Posts
    118

    Re: Bring back a name in one column based on criteria in 2 other columns

    Brilliant!! Thanks for your help!

+ 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