+ Reply to Thread
Results 1 to 11 of 11

Count multiple conditions array only works with 1 value

  1. #1
    Forum Contributor
    Join Date
    01-25-2013
    Location
    near Philly, PA USA
    MS-Off Ver
    Excel 2019
    Posts
    178

    Count multiple conditions array only works with 1 value

    I have a file (attached) of articles with titles and multiple authors (if more than one contributed to the article).
    I need to count the number of times one author co-authored an article with another author.
    I thought a table/array of all author names vertically as table row names with same names across the top as column labels and the number of times they've co-authored in the cells beneath.

    Perhaps I need a macro (not sure how to do that) but I thought an array formula might work. It works for the first author but not thereafter.
    Suggestions are greatly appreciated!
    Attached Files Attached Files

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Count multiple conditions array only works with 1 value

    In your sample, the original and the desired output are identical exept for column widths.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    01-25-2013
    Location
    near Philly, PA USA
    MS-Off Ver
    Excel 2019
    Posts
    178

    Re: Count multiple conditions array only works with 1 value

    My mistake in explaining perhaps:
    I need to examine rows 2:1038 and output the data on rows F1040:acd1793
    After some additional googling, I am thinking some combination of INDEX and MATCH in an array formula?

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Count multiple conditions array only works with 1 value

    Clear your OUTPUT sheet completely. Then manually mockup the actual desired results from that sample data sheet.

  5. #5
    Forum Contributor
    Join Date
    01-25-2013
    Location
    near Philly, PA USA
    MS-Off Ver
    Excel 2019
    Posts
    178

    Re: Count multiple conditions array only works with 1 value

    JBeaucaire,
    I entered faux data in a few of the cells in the output range.
    An author will co-author with themselves the total number of times they are listed in the data range. They will co-author with other authors as elaborated in the data range.
    Does this help?
    Attached Files Attached Files

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Count multiple conditions array only works with 1 value

    The only thing I see different on the OUTPUT sheet is the added FIRST/LAST flags in column G. A formula that will do that is:

    =IF(COUNTIF(B1:B2, B2)=1, "First", IF(COUNTIF($B$1:$B2, $B2)=COUNTIF($B:$B, $B2), "Last", ""))

  7. #7
    Forum Contributor
    Join Date
    01-25-2013
    Location
    near Philly, PA USA
    MS-Off Ver
    Excel 2019
    Posts
    178

    Re: Count multiple conditions array only works with 1 value

    I think you are answering a different question.
    My problem is: how to count the # of times each author co-authors with another author.
    The output is in the "table/array" starting on B1040 using the data from above in rows 2 to 1038.
    Updated sample file attached with a few cells of data entered in the cells to the right of row B1040.
    I appreciate your patience!
    Attached Files Attached Files

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Count multiple conditions array only works with 1 value

    I think you're looking at a different sheet. This is cell B1040 from your output sheet.

    http://screencast.com/t/arkOYldF8Ft

  9. #9
    Forum Contributor
    Join Date
    01-25-2013
    Location
    near Philly, PA USA
    MS-Off Ver
    Excel 2019
    Posts
    178

    Re: Count multiple conditions array only works with 1 value

    I've uploaded the file again; I had to delete a lot of columns to meet file size requirement for uploading.
    I hope it makes sense.
    Thanks!
    Bob
    Attached Files Attached Files

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Count multiple conditions array only works with 1 value

    Makes much more sense, yes.

    Ok, cell A1 must have the word "Title" on it before this macro will create the OUTPUT sheet. Make sure that data sheet is onscreen. Data must be in the two-column format demonstrated in your last workbook.

    I've also adjusted the output, row/column intersection where the same name appears will only show values if they are the sole author of one or more publications, the table as a whole represents:

    1) Number of time row/column intersecting authors co-authored publications
    2) Number of times an author was the sole author of a publication. (See Miller, Lisa A. as an example)

    The macro is CreateRelationalAuthorTable and goes in a standard code module.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by JBeaucaire; 02-19-2013 at 11:58 AM.

  11. #11
    Forum Contributor
    Join Date
    01-25-2013
    Location
    near Philly, PA USA
    MS-Off Ver
    Excel 2019
    Posts
    178

    Re: Count multiple conditions array only works with 1 value

    THANK YOU SOOOO MUCH!!!!!!
    I found it won't give correct data if the titles aren't in alpha order but once I figured that out all is good.
    It took it over 5 minutes to run so it was doing a lot of crunching.
    I very much appreciate your efforts on my behalf.
    Best,
    Bob

+ 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