+ Reply to Thread
Results 1 to 2 of 2

need a search option i cant find!

  1. #1
    Registered User
    Join Date
    01-06-2009
    Location
    Winsum
    MS-Off Ver
    Excel 2007
    Posts
    2

    need a search option i cant find!

    Hello,

    My Excel is dutch but i still will atach it! Im working on a sheet for a speeddate at my school. Now in page one there are the results of men. In page 2 the results of women. Now in page 3 are the results of both if they both have the same you have a match!! :D

    Now i would like to have on page 4 all men (101-150) with behind it the numbers of the women they match....

    If you can find the formula for this in english its easy for me to translate so please help me with this one!

    greetings,

    Frans
    Attached Files Attached Files

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Suffolk, UK
    MS-Off Ver
    2002, 2007 & 2010
    Posts
    21,526
    Not entirely sure I'm following but if I've interpreted correctly I'd probably go for something along the lines of the following:

    1 - Sheet Resultaat:
    B2: =IF(AND(vrouwen!B2="x",mannen!B2="x"),COUNT($A2:A2),"")
    copy down for all rows and across to column AY
    AZ2:=COUNT(B2:AY2)
    copy down for all rows (effectively = count of *matches* for given man)

    2 - Sheet Resultaat mannen
    Insert Row in Row 1 (ie existing row 1 becomes row 2)
    In D1: "Match"
    In B2: "Row"
    In C2: "Matches"
    In D2: =N(C2)+1
    copy across to say J2
    In B3: =MATCH($A3,Resultaat!$A$1:$A$100,0)
    copy down for all Man ID's in A
    In C3: =INDEX(Resultaat!$AZ$1:$AZ$100,$B3,1)
    copy down for all Man ID's in A

    Now to populate matrix
    In D3: =IF($C3<D$2,"",INDEX(Resultaat!$A$1:$AZ$1,1,MATCH(D$2,INDEX(Resultaat!$A$1:$AZ$100,$B3,0),0)))
    copy down for all Man ID's in A and across to J (ie to cater for max possible number of matches)

    Hope that helps.

    (note 2nd use of INDEX in the Matrix is volatile as it's being used to return a Range as opposed to a Value)

+ 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