+ Reply to Thread
Results 1 to 18 of 18

Adding multiple "find" with "left" /"Right"

  1. #1
    Registered User
    Join Date
    02-21-2014
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Adding multiple "find" with "left" /"Right"

    I am compiling a database of tennis stats for various players. I input the set scores (like 6-4) and I then need excel to find "-", go to the left and right, and add up all cells containing these scores on a full row.

    For example:

    6-4, 6-4, 6-4 would become 18-12

    I can do this by using "find" and left/right. The problem is, the more records I add, the more I have to keep changing the formula. This starts to get out of hand >

    Please Login or Register  to view this content.

    Is there a faster way to do this, preferably without using macros.

  2. #2
    Forum Contributor
    Join Date
    02-20-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    170

    Re: Adding multiple "find" with "left" /"Right"

    Can you post an example of how your data is set out?

    I think I follow, but would be easier with your example.

  3. #3
    Registered User
    Join Date
    02-21-2014
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Adding multiple "find" with "left" /"Right"

    It's set out by player (this is for personal stat use for our own group of players)

    Dan v Dave is the first record. There are a maximum of 10 sets on a row for that particular day's play.

    So B10 to K10 are the relevant cells. It adds up the set scores on that row if they contain text (B10 to K10).

    At the moment, the full formula I am using to do this is the following:

    Please Login or Register  to view this content.

    There can only be 10 sets on a row. So the above would be complete. However, more days can be added, and then I need to start adding grand totals up. This means I need to know a trick to do the above without all those iterations.

  4. #4
    Forum Contributor
    Join Date
    02-20-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    170

    Re: Adding multiple "find" with "left" /"Right"

    Hi Daniel,

    So someone a bit smarter than me might be able to help you out, as far as I can tell there isn't an easier way to do this.

    My only suggestion would be to change the way you record the scores. I've attached an example that would make your data a fair bit easier to work with.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-21-2014
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Adding multiple "find" with "left" /"Right"

    Thanks for the reply. I can't use that kind of a method sadly. There are far too many records going into this, on different days, and so forth. Using that method would be a bloated nightmare.

    Hopefully someone can work a way to do the above. If I have to use macro, then an example of that would be good too. I program in Delphi (Pascal), and from what I can see it's similar anyway.

  6. #6
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Adding multiple "find" with "left" /"Right"

    Without seeing your workbook, im set up my own example, assumed the data start at Cell A2 to A4 (6-4, 6-4, 6-4):

    =SUM(VALUE(LEFT(A2:A4,SEARCH("-",A2:A4,1)-1)))&"-"&SUM(VALUE(RIGHT(A2:A4,LEN(A2:A4)-SEARCH("-",A2:A4,1))))

    Array Formula

    see the file

    Azumi
    Attached Files Attached Files

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Adding multiple "find" with "left" /"Right"

    Try this...

    Data Range
    A
    B
    C
    D
    E
    1
    6-4
    6-4
    6-4
    -----
    2-1
    2
    3
    20-13


    This array formula** entered in A3:

    =SUM(IF(ISNUMBER(FIND("-",A1:E1)),--LEFT(A1:E1)))&"-"&SUM(IF(ISNUMBER(FIND("-",A1:E1)),--RIGHT(A1:E1)))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    I've assumed the numbers will always be single digits.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Adding multiple "find" with "left" /"Right"

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

    array entered with ctrl+shift+enter
    tennis scores can be in double figures
    eg
    Australian Open 2013, 4th round: Serbia Novak Djoković def. Switzerland Stanislas Wawrinka 1-6, 7-5, 6-4, 6-7, 12-10
    Attached Files Attached Files
    Last edited by martindwilson; 02-21-2014 at 10:41 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Adding multiple "find" with "left" /"Right"

    Quote Originally Posted by azumi View Post

    Array Formula
    Tip: you should assume that the OP doesn't know what an "Array Formula" is/means and include instructions on how to enter an array formula.

  10. #10
    Registered User
    Join Date
    02-21-2014
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Adding multiple "find" with "left" /"Right"

    @ martindwilson

    This appears to do what I want... thanks to all of you !

    I may need further help with this at some stage, but that's a nifty little piece of formula.

  11. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Adding multiple "find" with "left" /"Right"

    Quote Originally Posted by martindwilson View Post
    tennis scores can be in double figures
    In that case I've tweaked my suggestion.

    Data Range
    A
    B
    C
    D
    E
    1
    6-4
    6-4
    10-4
    -----
    2-10
    2
    3
    24-22


    This array formula** entered in A3:

    =SUM(IF(ISNUMBER(FIND("-",A1:E1)),--LEFT(A1:E1,FIND("-",A1:E1)-1)))&"-"&SUM(IF(ISNUMBER(FIND("-",A1:E1)),-MID(A1:E1,FIND("-",A1:E1),3)))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

  12. #12
    Registered User
    Join Date
    02-21-2014
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Adding multiple "find" with "left" /"Right"

    OK, I finished this and it will do the job I wanted. 2 things

    a. Can anyone spot a more efficient way of doing this? Let's pretend I added another 10 people... it would be a ton of manual work this way. Is that unavoidable?

    b. Can anyone get this working properly with google drive? It doesn't convert properly.

    Thanks!

    File is here:

    https://dl.dropboxusercontent.com/u/...02/tennis.xlsx

  13. #13
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Adding multiple "find" with "left" /"Right"

    Pl see attached file with SUMPRODUCT formula, Common to all.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    02-21-2014
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Adding multiple "find" with "left" /"Right"

    That's still showing a parse error when placed into google drive.

  15. #15
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Adding multiple "find" with "left" /"Right"

    =sumproduct((SUBSTITUTE(right($C$10:$Z$10;2);"-";"")))+INDEX(0;1 ) and

    =sumproduct((SUBSTITUTE(LEFT($C$10:$Z$10;2);"-";"")))+INDEX(0;1 )
    will work on google spreadsheets BUT not in excel they seem to parse slightly differently and one works with blanks the other doesnt

  16. #16
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Adding multiple "find" with "left" /"Right"

    ok i put my thinking cap on and did this
    put a helper sheet!1 and created a table for sumproduct to use
    now this sheet should work in excel AND google docs
    Attached Files Attached Files

  17. #17
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Adding multiple "find" with "left" /"Right"

    Quote Originally Posted by Daniel1 View Post

    a. Can anyone spot a more efficient way of doing this?
    Yes.

    Put the scores in separate cells. Then a simple SUM function will do.

    I didn't look at your file.

  18. #18
    Registered User
    Join Date
    02-21-2014
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Adding multiple "find" with "left" /"Right"

    That won't work, for reasons I have explained. If you saw the sheet you would understand that. it's also not efficient to do that.

    @martindwilson

    Thanks a bunch, and to all those who have contributed.

    One final problem now..

    When I add new columns, the absolute cell addresses are still changing

    $Z$2:$ZZ$2 is becoming $AA$2: $AAA$2

    ========

    edit

    Looks like offset can do it.


    I want the former to remain the same, no matter what happens.
    Last edited by Daniel1; 02-22-2014 at 07:25 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  2. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  3. Replies: 2
    Last Post: 06-06-2013, 12:45 PM
  4. [SOLVED] Find multiple "text" criteria and return as ""Yes" in Matrix
    By bertrand82 in forum Excel General
    Replies: 11
    Last Post: 04-30-2012, 09:20 AM
  5. Replies: 5
    Last Post: 06-26-2006, 09:23 PM

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