+ Reply to Thread
Results 1 to 10 of 10

sumproduct - sum the same abbreviated titles of medical journals

  1. 10-25-2018, 08:43 AM #1
    Oncology
    Oncology is offline
    Registered User
    Join Date
    06-23-2017
    Location
    Ljubljana
    MS-Off Ver
    MS Office Proffesional Plus 2010
    Posts
    16

    sumproduct - sum the same abbreviated titles of medical journals

    Dear excel forum users,

    my name is David, a librarian from Institute of Oncology Ljubljana.

    Would anybody on forum be so kind and help me to solve my problem with counting (sum) how many times the same journal abbreviated name (abbreviated title) repeats in the column A (in tab journals 2018).

    For example:
    -Nature 2000; 406: 747-52 - abbreviation for the journal Nature
    -Ann Surg Oncol 2015; 22(Suppl 3): S495-S501 - abbreviation for the journal Annals of Surgical Oncology
    -J Clin Oncol. 2016 Mar 10;34(8):825-32 - abbreviation for the journal Journal of Clinical Oncology
    -etc.

    I would like to have the results as an automatically generated list of journal abbreviated names (abbreviated titles) in the column A (in tab sum journal titles) with their sum in column B.

    Thank you for your help in advance.

    David
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-25-2018, 09:21 AM #2
    dominicb
    dominicb is offline
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: sumproduct - sum the same abbreviated titles of medical journals

    Good afternoon Oncology

    Your request isn't that tricky, however, there's a slight complication.

    To extract the publication abbreviation we need a delimiting character, a unique character in the string found in column A that denotes where the publication name ends. In some instances theres a full stop (.), but not all; in some instances there's a space, but spaces actually within the publication name means we can't do this.

    As I say, this isn't a big ask, but your data needs some serious tidying up.

    HTH

    DominicB
    Please familiarise yourself with the rules before posting. You can find them here.
    Reply With QuoteRegister To Reply
  3. 10-25-2018, 09:24 AM #3
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: sumproduct - sum the same abbreviated titles of medical journals

    Please try at B2 drag down to B7
    =COUNTIF('journals 2018'!A:A,A2&"*")

    For Etc B8
    =COUNTA('journals 2018'!A:A)-SUM(B2:B7)-1
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  4. 10-25-2018, 09:29 AM #4
    Oncology
    Oncology is offline
    Registered User
    Join Date
    06-23-2017
    Location
    Ljubljana
    MS-Off Ver
    MS Office Proffesional Plus 2010
    Posts
    16

    Re: sumproduct - sum the same abbreviated titles of medical journals

    To extract the publication abbreviation we need a delimiting character, a unique character in the string found in column A that denotes where the publication name ends. In some instances theres a full stop (.), but not all; in some instances there's a space, but spaces actually within the publication name means we can't do this.
    As I say, this isn't a big ask, but your data needs some serious tidying up.

    OK, we can manually clean our data - add full stop (.) where needed.

    How will then be the formula for column A and column B?
    Reply With QuoteRegister To Reply
  5. 10-25-2018, 09:35 AM #5
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: sumproduct - sum the same abbreviated titles of medical journals

    Please try at journals 2018 B2 for extract the publication abbreviation and remove "."

    =SUBSTITUTE(LEFT(A2,MIN(FIND({0,1,2,3,4}+{0;5},A2&1/17))-1),".","")
    Reply With QuoteRegister To Reply
  6. 10-25-2018, 09:39 AM #6
    dominicb
    dominicb is offline
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: sumproduct - sum the same abbreviated titles of medical journals

    Hi Oncology

    Are you going to have a prepopulated form with the titles already there - just waiting for the values to be populated?
    If this is the case, then Bo_Ry's solution will suffice. The data won't need cleansing.

    If you want the abbreviated journal name creating "on-the-fly" as it were, then you will have to tidy up the data.
    Which way are you going to go?

    HTH

    DominicB
    Reply With QuoteRegister To Reply
  7. 10-25-2018, 09:46 AM #7
    CK76
    CK76 is offline
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,882

    Re: sumproduct - sum the same abbreviated titles of medical journals

    You can just use Bo_Ry's formula result as source for pivot table, and do count on title.

    See attached.
    Attached Files Attached Files
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein
    Reply With QuoteRegister To Reply
  8. 10-25-2018, 10:01 AM #8
    mohan.r1980
    mohan.r1980 is offline
    Valued Forum Contributor mohan.r1980's Avatar
    Join Date
    09-18-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2010 (windows7)
    Posts
    729

    Re: sumproduct - sum the same abbreviated titles of medical journals

    Hi,

    try attached file 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 10-25-2018, 05:17 PM #9
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: sumproduct - sum the same abbreviated titles of medical journals

    If it makes a difference to anyone's formulas/code there are also CHAR(160) present in the data.
    Dave
    Reply With QuoteRegister To Reply
  10. 10-26-2018, 05:50 AM #10
    Oncology
    Oncology is offline
    Registered User
    Join Date
    06-23-2017
    Location
    Ljubljana
    MS-Off Ver
    MS Office Proffesional Plus 2010
    Posts
    16

    Re: sumproduct - sum the same abbreviated titles of medical journals

    Thank you.
    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. How to keep titles, sub-titles, and blank space when sorting
    By wanttolearnexcel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-13-2018, 12:54 AM
  2. [SOLVED] Sumproduct based on employee number and column titles
    By jantonio in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-21-2018, 04:10 PM
  3. Automated posting from journals to ledgeraccounts
    By TennisShoesFitSz12 in forum Excel General
    Replies: 5
    Last Post: 05-22-2014, 05:57 AM
  4. Ideas on a spreadsheet to create sheets from multiple journals
    By ShadowRider in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-31-2011, 12:06 AM
  5. [SOLVED] accounting ledgers and journals
    By llv8rgrl in forum Excel General
    Replies: 1
    Last Post: 07-09-2011, 02:13 AM
  6. Changing the column titles to the spreadsheet titles when scrolling down
    By Frank the Haddock in forum Excel General
    Replies: 4
    Last Post: 06-22-2011, 09:50 AM
  7. [SOLVED] spreadsheets and auto General Journals postings
    By Gemini Crafts in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-14-2006, 10:50 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