+ Reply to Thread
Results 1 to 6 of 6

Index/Match Function & Filters - NodeXL

  1. 06-05-2014, 02:47 AM #1
    r.cristobal
    r.cristobal is offline
    Registered User
    Join Date
    06-04-2014
    Posts
    5

    Index/Match Function & Filters - NodeXL

    I am making a NodeXL spreadsheet examining partnerships between NGOs and the topic of collaboration (ex. Education, Economic Development, Medicine).

    The Edge cells (Network) looks kind of like this:

    Org I Org I Topic
    Org A I Org B I Education
    Org A I Org C I Medicine
    Org A I Org D I Education
    Org A I Org E I Education
    Org B I Org C I Medicine
    Org B I Org D I Medicine
    Org B I Org D I Medicine
    ...

    I want to know what kind of partnership (Topic) is most prevalent in each NGO's portfolio. I used this:

    =INDEX(Range,MATCH(MAX(COUNTIF(Range,Range)),COUNTIF(Range,Range),0))

    The problem I encountered was that if I rearranged the order of the rows using Filter, obviously, the INDEX/MATCH function won't work.

    Is there anyway to fix the INDEX/MATCH function to the organizations listed in Column A? So, "Among the cells under Topics for ORG A, which Topic of partnerships occurs the most?"

    Thank you for your time and help!
    Reply With QuoteRegister To Reply
  2. 06-05-2014, 03:32 AM #2
    Saarang84
    Saarang84 is offline
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Index/Match Function & Filters - NodeXL

    Hi,

    Can u post a workbook with sample data (remove sensitive data) of about 25 rows to work with?
    If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.

    If your requirement has been solved please mark your thread as Solved.
    In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".

    Kindly use [FORMULA] or [CODE] tags when posting your code.

    Regards,
    Sarang
    Reply With QuoteRegister To Reply
  3. 06-05-2014, 04:14 AM #3
    r.cristobal
    r.cristobal is offline
    Registered User
    Join Date
    06-04-2014
    Posts
    5

    Re: Index/Match Function & Filters - NodeXL

    Sample - Excel Forum.xlsx

    I made a mock-up of the sort I am trying to do.

    Thank you so much for your time and help!
    Reply With QuoteRegister To Reply
  4. 06-05-2014, 05:50 AM #4
    Saarang84
    Saarang84 is offline
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Index/Match Function & Filters - NodeXL

    Is the list of topics in column C in the Edges sheet, fixed? I mean, will the list of topics vary across organizations? Can you upload that list of topics ?
    Will these topics be repeated for all Funder names in column A in the Node Attributes worksheet?

    Note : The below formulas are just for my reference (to be used later) just don't mind the below content Ryo

    For helper column in Edges sheet, use the below formula and copy down :
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    In Edges sheet, for number of occurrences of topic 1,
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    For number of occurrences of topic 2,
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    For number of occurrences of topic 3,
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Last edited by Saarang84; 06-05-2014 at 10:28 PM.
    Reply With QuoteRegister To Reply
  5. 06-17-2014, 03:59 AM #5
    Saarang84
    Saarang84 is offline
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Index/Match Function & Filters - NodeXL

    Hi,

    Linking your related post and the workbook here.. You can continue it in this thread.

    IF and INDEX/MATCH
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 06-17-2014, 04:33 AM #6
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Index/Match Function & Filters - NodeXL

    Hi,

    Just seen the link in your other thread that is directed here. So posted her again in case you don't spot it

    Actually if you put the formula I gave you in C2 on the Partner information sheet it will return the value 5, being 5 instances of partner A in column A of Network Data that also belong to Marketing.

    Using both columns just extend the formula. i.e.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Index, Match and filters
    By Steve M in forum Excel General
    Replies: 6
    Last Post: 07-28-2017, 11:12 AM
  2. Use Index:Match function to match a number to the row heading
    By ahenobarb in forum Excel General
    Replies: 6
    Last Post: 03-17-2014, 08:10 PM
  3. index function error using match function to get data from a cell in an array
    By mabildgaard in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-17-2013, 02:21 AM
  4. Lookup or Index(match) function not in ascending order and not an exact match
    By Tobias1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-17-2013, 12:37 PM
  5. Inserting an INDEX,MATCH function into a HYPERLINK function instead of cell reference
    By giallofever in forum Excel General
    Replies: 2
    Last Post: 03-20-2009, 01:29 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1