+ Reply to Thread
Results 1 to 32 of 32

Total Number of Occurences Based on Equation

  1. #1
    Registered User
    Join Date
    06-21-2018
    Location
    Calgary, AB
    MS-Off Ver
    2013
    Posts
    94

    Total Number of Occurences Based on Equation

    Hi folks,

    I had posted a thread about this yesterday, but I managed to make quite a bit of progress on what I was trying to complete and now my goal has changed somewhat.
    If I am going against forum rules, please let me know and I will continue on the original post - if not I will gladly close the thread down (not sure how to do that, I assume I need to request it from an admin?)

    I have created a merged spreadsheet with tabs containing formulas hard coded with numbers, not cell references, e.g. =(250+75)*4)*2+...
    What I am trying to do is search through all the tabs in the workbook and determine how many times the number is multiplied in a row - for example, in the case of the last cell, the column *250* would display "8"; I hope this is clear.

    What is my best way of going about this? Now that I have the merged sheet, I only have a total of 6 tabs to look through in one workbook. I do need to look up about 8 different values in each cell and total these at the end of the row for each individual value.

    Thank you!
    Last edited by visualnotsobasic; 07-12-2018 at 01:43 PM. Reason: New objective

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Total Number of Occurences Based on Equation

    My first thought is that this seems like it would be a lot easier if the values were not hard coded into the formula. Then VBA would not be needed. My solution would be to store the values in cells, then refer to those cells in the formula(s).
    Please Login or Register  to view this content.
    Is that something you can work with, or are you required to use VBA on values hard coded into formulas?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    06-21-2018
    Location
    Calgary, AB
    MS-Off Ver
    2013
    Posts
    94

    Re: Total Number of Occurences Based on Equation

    Thanks for the reply.

    I'm unfortunately working with a pile of worksheets/workbooks where these values are already hard coded in - that's part of the issue.
    If you think that that would be a better solution, I suppose that I write a find and replace script and run that through all of the workbooks first; I'm not sure if that would end up being any faster in the long run but you're far more experienced than I am so I could be way off.

    I essentially have to look through expense reports for a group of people (each has their own workbook), pull out counts of each of these numbers as each applies to a different item (travel, meals, etc.) and then populate a master report with the counts.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Total Number of Occurences Based on Equation

    In that case, I'd probably use the FORMULATEXT() function to extract the formula string into a cell, then use the SEARCH() function to find which numbers are present in that string. Perhaps something like
    Please Login or Register  to view this content.
    then, at the bottom (or top) of each column, add a COUNTIFS(C2:C200,TRUE) type formula.

    Maybe I am just a little reluctant to use VBA when I don't think it is necessary, so again, I propose something that does not require VBA. If you must use VBA, it can be done using VBA. Will that work for you?

  5. #5
    Registered User
    Join Date
    06-21-2018
    Location
    Calgary, AB
    MS-Off Ver
    2013
    Posts
    94

    Re: Total Number of Occurences Based on Equation

    I suppose I just thought it would be easier to autopopulate everything and run it through all the workbooks using VBA! You are the expert - I will give your way a go!

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Total Number of Occurences Based on Equation

    Easier probably has more to do with which programming language you are more comfortable with. I tend to be more comfortable with the "spreadsheet formula" programming language, so I tend to use it first. If you want to use VBA, the same basic flow of logic applies:

    1) Read formula text from cell (.Formula property).
    2) Search formula text for each value of interest (InStr function)
    2a) If value is found in formula, increment variable/memory location storing the count for that value by 1
    3) Repeat for each formula cell (some kind of loop structure, depending on exact needs).

    I don't know that that is any easier or more difficult than the spreadsheet approach I proposed, but if you are more comfortable with VBA, let us know and we can help you implement that kind of algorithm in VBA.

  7. #7
    Registered User
    Join Date
    06-21-2018
    Location
    Calgary, AB
    MS-Off Ver
    2013
    Posts
    94

    Re: Total Number of Occurences Based on Equation

    I really appreciate the thoroughness of the responses.

    Although I am rather new to VBA, I have invested far more time into it and been far more successful than other approaches. I think rather than piddle away your time I will upload sample worksheets and we can then determine what would be the most prudent solution.

    I will upload them here within the next few minutes.

  8. #8
    Registered User
    Join Date
    06-21-2018
    Location
    Calgary, AB
    MS-Off Ver
    2013
    Posts
    94

    Re: Total Number of Occurences Based on Equation

    removed due to irrelevance
    Last edited by visualnotsobasic; 07-12-2018 at 01:44 PM. Reason: no longer relevant

  9. #9
    Registered User
    Join Date
    06-21-2018
    Location
    Calgary, AB
    MS-Off Ver
    2013
    Posts
    94

    Re: Total Number of Occurences Based on Equation

    As well MrShorty, I did try your solution with just inserting formulas to extract the cell text - I'm wondering if there's a quick way to run it through all the tabs, and then autopopulate another sheet.
    Thank you for your assistance.

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Total Number of Occurences Based on Equation

    Assuming every tab is laid out exactly the same, then you can copy the formulas from the first tab, select tabs 2 through whatever (select tab 2->press and hold shift while selecting the last tab), select the upper left cell where the formulas should paste into and paste. If you have built the formulas for easy copying, then Excel should paste all of the formulas into all of the selected tabs.

    I would probably copy and paste from the first tab into tab 2 and carefully verify that all formulas copied correctly before copying into tabs 3 to whatever. If you have a lot of formulas, or a lot of tabs to paste into, I would consider pasting into 5 or 10 or 20 tabs at a time. Theoretically you can paste into as many cells as you have patience for, but many don't have patience to wait 5 or 50 minutes for Excel to complete a large paste operation. Breaking the paste operation up into manageable chunks alleviates the long "excel is not responding so how long should I wait for excel to begin responding again" waiting process.

  11. #11
    Registered User
    Join Date
    06-21-2018
    Location
    Calgary, AB
    MS-Off Ver
    2013
    Posts
    94

    Re: Total Number of Occurences Based on Equation

    Thanks a lot, that part is done and now all formulas/hardcoded numbers are displayed under the same category names.
    Now the issue is just determining the count of each number for each row

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Total Number of Occurences Based on Equation

    I am having trouble visualizing the spreadsheet as you now have it. It seems like such a count should be had from some kind of COUNTIFS() or similar function. What data is in each row now?

  13. #13
    Registered User
    Join Date
    06-21-2018
    Location
    Calgary, AB
    MS-Off Ver
    2013
    Posts
    94

    Re: Total Number of Occurences Based on Equation

    Please see the attached spreadsheet - I listed some sample costs to the right of the main data as an example
    Attached Files Attached Files

  14. #14
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Total Number of Occurences Based on Equation

    I don't understand what you are trying to count in each row. The number 250 appears to only occur either once or none, and only appears in column A. So a possible count for 250 could be something like =IFERROR(1*(SEARCH(250,$A2)>0),0)
    500 appears to be possible in columns A and C, so a count of 500s could be =IFERROR(1*(SEARCH(500,$A2)>0),0)+IFERROR(1*(SEARCH(500,$C2)>0),0)
    A sum of IFERROR(SEARCH(),0) functions should work for each value you need to count.
    Will something like that work?

  15. #15
    Registered User
    Join Date
    06-21-2018
    Location
    Calgary, AB
    MS-Off Ver
    2013
    Posts
    94

    Re: Total Number of Occurences Based on Equation

    I think I am explaining myself poorly, and I apologize.
    The number itself occurs once, but it is *MULTIPLIED* by the number 2. I therefore would like it to count as 2 instead of 1.

  16. #16
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Total Number of Occurences Based on Equation

    I'm not good with text manipulation functions, but that sound like it means:
    1) Find the value (250).
    2) Find the multiplier, which, because of the way things are grouped could be outside of parentheses, or, if the multiplier is 1, will be nonexistent. Will the multiplier always be after the value (250*3) or can that appear in reverse order (3*250)? Almost all example look like value*multiplier, but there is one case in column B where there are 60*12 and 65*12 and one 12*60 (rows 38:43). Should this last one be captured as twelve sixties or sixty twelves? I also see several cases where there are two multipliers ((250+75)*2)*4, for example. Do we need to capture the multiple multipliers so that this counts eight 250s?

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Total Number of Occurences Based on Equation

    Im also wondering if adding a space after the operator + * (using find/replace) would help? Because it looks like the multiplier could be 1 or 2 (or more?) digits, that might help to find/use it?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  18. #18
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Total Number of Occurences Based on Equation

    Why wouldn't:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    display 12??
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Total Number of Occurences Based on Equation

    You have
    =(250+75+80)*2*8+700*2+1300*2 in A2
    250: *2*8 = 16 times
    75: *2*8 = 16 times
    80: *2*8 = 16 times
    700: *2 = twice
    1330: *2 = twice

    How do you want the result in which column?

    Also for the data in B2 etc.

    If above is true, If you upload a workbook with data and your desired result, it will be easier.

  20. #20
    Registered User
    Join Date
    06-21-2018
    Location
    Calgary, AB
    MS-Off Ver
    2013
    Posts
    94

    Re: Total Number of Occurences Based on Equation

    EDIT: Apologies for the long wait on the reply, I was out of town for the weekend. Thank you for your responses

    I filled in an example column with the intended format
    Attached Files Attached Files

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Total Number of Occurences Based on Equation

    Still not sure though...
    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    06-21-2018
    Location
    Calgary, AB
    MS-Off Ver
    2013
    Posts
    94

    Re: Total Number of Occurences Based on Equation

    This is fascinating - I'm somewhat understanding what's going on here, could you perhaps explain a little bit?

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Total Number of Occurences Based on Equation

    1) Column A.
    Extract first appeared set of * & number(s) from String that is less closing bracket ).
    e.g
    =(250+75+80)*2*8+700*2+1300*2
    =(250+75+80)*3
    =((250+75+80)*2)*8+700*4
    Then calculate the result with Evaluate method like
    2*8
    3
    2*8

    2) Column F.
    Extract part of * & number(s) and / and number(s) if there is at the end
    e.g
    =(600*12+750)/2
    =(650*12+1000)
    Then also calculate the result with Evaluate method

  24. #24
    Registered User
    Join Date
    06-21-2018
    Location
    Calgary, AB
    MS-Off Ver
    2013
    Posts
    94

    Re: Total Number of Occurences Based on Equation

    This is honestly mindblowing - I've never seen the .Pattern before so it's a whole new thing to me.
    What part of the code differentiates it between column l and column n?

  25. #25
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Total Number of Occurences Based on Equation

    1)
    Please Login or Register  to view this content.
    Data array consists of columns A, H(Empty column) & F.
    So array variable a has only 3 columns to fit the result columns.

    2) You see 2 If clauses within For Next loop.
    One for col.A a(i, 1) and one for Col.F a(i 3)
    Last edited by jindon; 07-16-2018 at 11:37 AM.

  26. #26
    Registered User
    Join Date
    06-21-2018
    Location
    Calgary, AB
    MS-Off Ver
    2013
    Posts
    94

    Re: Total Number of Occurences Based on Equation

    So I could expand the "a" variable to include other columns and conditions - roughly 6 or 7 different numbers and respective columns?

  27. #27
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Total Number of Occurences Based on Equation

    If you want, yes.

  28. #28
    Registered User
    Join Date
    06-21-2018
    Location
    Calgary, AB
    MS-Off Ver
    2013
    Posts
    94

    Re: Total Number of Occurences Based on Equation

    And what if I need to extract data on two separate numbers from the same cell - for example, 250 and then 75?

  29. #29
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Total Number of Occurences Based on Equation

    Too abstract.

    If you want to continue, you should learn about Regular Expressions first.

    Google with the key words like

    VBA Regular Expressions

  30. #30
    Registered User
    Join Date
    06-21-2018
    Location
    Calgary, AB
    MS-Off Ver
    2013
    Posts
    94

    Re: Total Number of Occurences Based on Equation

    Any specific learning materials you've found especially useful?
    I appreciate all the advice.

  31. #31
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Total Number of Occurences Based on Equation

    Unfortunately none as long as Pattern is concerned.
    Anyway you need to learn about Meta characters in Regular Expressions.

    Google first and learn the concept first.
    Nothing will start without it.
    Last edited by jindon; 07-16-2018 at 12:59 PM.

  32. #32
    Registered User
    Join Date
    06-21-2018
    Location
    Calgary, AB
    MS-Off Ver
    2013
    Posts
    94

    Re: Total Number of Occurences Based on Equation

    Thank you for the response. I have started looking into it - it seems very complex but also useful.

+ 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. [SOLVED] Calculate Total Hours each occurrence and Total Days for all occurences
    By CARD17 in forum Excel Formulas & Functions
    Replies: 47
    Last Post: 01-18-2017, 11:02 PM
  2. [SOLVED] How to return cell value based on number of occurences
    By jeffjam in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-03-2016, 02:22 PM
  3. Replies: 2
    Last Post: 03-01-2016, 02:10 PM
  4. Count Number of occurences Across Columns based on Similar Column Headers
    By vidyuthrajesh in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-27-2013, 06:42 PM
  5. Count number occurences based on criteria Column
    By SamCrome in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2013, 04:32 PM
  6. Equation based on one cell with word & number
    By dagindi in forum Excel General
    Replies: 3
    Last Post: 02-09-2011, 05:12 PM
  7. [SOLVED] Calculating number of occurences based on dates
    By Henrik Fritsche in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-23-2006, 11:15 AM

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