+ Reply to Thread
Results 1 to 12 of 12

Vertical and horizonal search

  1. #1
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    581

    Vertical and horizonal search

    Hi all
    I attached an example of what I am looking for as it is rather difficult to explain.

    I got a matrix with on top a row of names of persons.
    On the column left of the matrix dates are given.
    In between are codes.

    I am looking for a formula that collects all persons that have one of the codes given in cells L5:L8 on a particular date which is given in cell L9

    The list will start at L11 downwards.
    Attached Files Attached Files

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Vertical and horizonal search

    Well, your references didn't match the attached workbook, so I used what
    was in the workbook. My edited version is attached.

    The base formula for listing matching names is:

    Please Login or Register  to view this content.

    Copy L11 down through L13.

    Note: That formula lists the names in the reverse order of occurrence. I hope that's OK.

    Does that help?
    Attached Files Attached Files
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    581

    Re: Vertical and horizonal search

    Ron this is awesome. Just what I was looking for.
    I tried the formula on my own sheet, but got a strange problem.
    Instead of giving the names of persons correlating to the given date it collects the names of persons of the day after the given date.

    ie. When 25-sep is entered as reference it collects the names of people from the 26th...

    Btw I don't get the $11:11 at the end of the formula..?

    Here is your code used on my sheet.

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    581

    Re: Vertical and horizonal search

    Here is an example of the sheet
    Attached Files Attached Files

  5. #5
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Vertical and horizonal search

    There was a minor flaw in your formula. The OFFSET function's initial range
    was in Row_1. The MATCH function's data began in Row_3. Consequently, we
    need to account for the Row_2 gap by adding 1 to the RowOffset value:


    Please Login or Register  to view this content.

    Copy that formula down as far as you need.

    Regarding the ROWS($47:47)....
    When the formula is copied down, that part only generates an incrementing
    series of numbers (1, 2, 3, 4, etc). The LARGE function uses those values
    to return the 1st largest column number, 2nd largest column number, etc.

    I hope that helps.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    581

    Re: Vertical and horizonal search

    Thanks Ron, I am now going to give it a try.
    What about the blabla1-part why do they appear in the list?
    Is there a way to hide them?

  7. #7
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Vertical and horizonal search

    Quote Originally Posted by Jonathan78 View Post
    Thanks Ron, I am now going to give it a try.
    What about the blabla1-part why do they appear in the list?
    Is there a way to hide them?
    I didn't notice the incorrect returned values.
    Try this:
    Please Login or Register  to view this content.
    Copy that formula down as far as you need.

    When the formula runs out of matched items, it returns the value of cell O1,
    the blank part of a merged cell.

  8. #8
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    581

    Re: Vertical and horizonal search

    Ron, that worked. I canīt thank you enough.
    Thanks for sharing your knowledge

  9. #9
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Vertical and horizonal search

    You're very welcome....I'm glad I could help.

  10. #10
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    581

    Re: Vertical and horizonal search

    Hello RC,

    I have an almost similar situation as the one we discussed before, but can't ament your formula to fit the new situation.

    Would you mind to take a look at it.

    [I started an almost similar thread on the following forum]
    Attached Files Attached Files

  11. #11
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Vertical and horizonal search

    1) Thanks for referencing the other forum's activity.
    2) This post has been inactive for a few months. A new thread would have been more appropriate.
    3) Even though you linked to the other forum, you should still document your issue. It would have been nice to mention that you swapped row and colum headings in your new grid.

    4) Using your posted workbook, put this formula on sheet "b":

    Please Login or Register  to view this content.
    Copy that formula down through C15.

    Does that help?

  12. #12
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    581

    Re: Vertical and horizonal search

    Thank you so much RC.
    I couldn't do it myself.
    I still have a lot to learn

+ 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