+ Reply to Thread
Results 1 to 18 of 18

Summing up the largest terms per year in a dictionary

  1. #1
    Registered User
    Join Date
    03-22-2018
    Location
    Cologne, Germany
    MS-Off Ver
    2016
    Posts
    8

    Summing up the largest terms per year in a dictionary

    Hi VBA experts!

    I’m a vba greenhorn and got a –in my opinion- complex problem.

    First of all: I need a makro solution, even if the Pivot table does some of the following steps

    Now to the Problem:
    In one column i got some dates in the format DD.MM.JJJJ and in the other one some terms such as „screwdriver“, „tire“ ,... for example.

    I now need a vba code, that counts the frequency of terms per year and sum up the four largest ones for every year.

    With a little help i solved the first part of the problem by using a dictionary.
    So i got an output like this

    A / B / C
    2009/ screwdriver/ 4
    2009/Tire/ 3
    2009/ pencil / 7
    2009/ ball / 9
    2009/skateboard/2
    .
    .
    2010/tire/2
    ...


    In this example case there are 4+3+7+9+2=25 terms for 2009. The sum of the largest four is 9+7+4+3=23.

    And now I want an output like

    D / E
    2009/23

    How can sum up only a part of my dictionary?
    Maybe I need a complete different method?
    I hope you understand the problem and can help me to solve this!

    The attached file contains the data in Table1 and my Dictionary Output in Table2.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Summing up the largest terms per year in a dictionary

    Is there a particular place where to put the sum 2009/23 , in which row ??
    - Battle without fear gives no glory - Just try

  3. #3
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Summing up the largest terms per year in a dictionary

    Try this for results in "D & E".
    Please Login or Register  to view this content.
    Regards Mick

  4. #4
    Registered User
    Join Date
    03-22-2018
    Location
    Cologne, Germany
    MS-Off Ver
    2016
    Posts
    8

    Re: Summing up the largest terms per year in a dictionary

    Edit: I made a mistake at my example for the ranks!

    Sorry for my late response.

    Thank you for your answers!
    And thank you MikeG. I would never have done that. I’m still figuring out, how this code is working exactly. Usually I’m quicker at this.

    One last question: is it possible in a further step to form the sum of the 4 biggest terms including repetitions? So the sum of the four largest ranks such as.

    2009 / xy / 10
    2009 / ab / 8
    2009 / cd / 8
    2009 / ef / 7
    2009 / gh / 7
    2009 / ij / 7
    2009 / kl / 6
    2009 / mn / 5
    2009 / op / 5
    ...

    In this example, my highest rank would be 10, the second highest twice the 8, therefore rank 1 is occupied and rank 2 is occupied twice, so the next rank is rank 4 with three times the 7
    The sum of my 4 highest ranks thus 10+8+8+7+7+7=47

    I thought with the other code as a basis I could program this by myself, but I I was wrong

    I have adapted the sample file to make it more useful for this code

    The place of the output does not matter. I'll get that adjusted afterwards

    Once more: Thank you so much for your help!
    Attached Files Attached Files
    Last edited by chris1123; 03-23-2018 at 07:13 AM.

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

    Re: Summing up the largest terms per year in a dictionary

    chris1123,
    This will show you formula in result based on the data in "Table2".
    Please Login or Register  to view this content.
    Edited: misreading of the question.
    Last edited by jindon; 03-22-2018 at 10:57 PM.

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Hi ! Another way …

    Quote Originally Posted by chris1123 View Post
    How can sum up only a part of my dictionary?
    Maybe I need a complete different method?
    Can be achieved without any dictionary and
    from your Table2 worksheet sample just with an easy formula :

    PHP Code: 
    Sub Demo1()
              
    Dim Rf As RangeRg As RangeS$
        
    With Tabelle2.[A2].CurrentRegion.Columns
              Set Rf 
    = .Cells(1)
            While 
    Rf.Value ""
              
    Set Rg = .Item(1).Find(Rf.Value, , xlValuesxlWhole, , xlPrevious)
                
    With Range(RfRg).Offset(, 2)
                    If .
    Count 4 Then S "LARGE(" & .Address ",{1,2,3,4})" Else = .Address
                End With
                  Rf
    (14).Resize(, 2).Value = Array(Rf.Value, .Parent.Evaluate("SUM(" ")"))
              
    Set Rf Rg(2)
            
    Wend
        End With
              Set Rf 
    Nothing:  Set Rg Nothing
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

  7. #7
    Registered User
    Join Date
    03-22-2018
    Location
    Cologne, Germany
    MS-Off Ver
    2016
    Posts
    8

    Re: Summing up the largest terms per year in a dictionary

    Thank you for your answers!

    And thank you Mark. This code also works perfectly and I'm able to understand it

    Is it possible to adjust the code to sum up the highest ranks instead of the highest values?
    I made an example in my last edited post.

    So if i got the values 10,9,9,7,7,7,6,5,... the sum of my four highest values in this case ist 10+9+9+7= 35.
    But my four highest ranks are:
    rank1=10
    rank2=twice the 9
    rank3: not assigned, because rank 2 is assigned twice
    rank4= three times the 7

    So in this case the sum of my four highest ranks will by rank1+rank2+rank4=10+9+9+7+7+7=49.

    I´ve been searching the internet for an answer the last couple of hours but couldn't find one yet.

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Summing up the largest terms per year in a dictionary


    Your result is odd as rank 3 is assigned in Excel !
    I already have a code through Excel rank as 10 + 9+9 + 7+7+7 + 6 = 55 …

    According to your last attachment, post your expected 4 "ranks" results for each year.
    Edit : at least in particular for 2005, 2006 & 2008 years …
    Last edited by Marc L; 03-24-2018 at 11:17 AM.

  9. #9
    Registered User
    Join Date
    03-22-2018
    Location
    Cologne, Germany
    MS-Off Ver
    2016
    Posts
    8

    Re: Summing up the largest terms per year in a dictionary

    Once again thank you for your answer.
    This code would be fine, too! I thought excel would rank like in my example.

    I have adapted my example and attached the new one to this post. Column G contains my results as a sum of 4 assigned ranks, as your example showed.
    I colored the different ranks for each year to clarify the ranks.

    Here are the years you mentioned (the others ones are calculated as a sum in column G in the file)

    2005: 8+5+5+4+2+2+2=28
    2006: 9+9+7+6+5=36
    2008: 5+3+2+2+2+2+1+1=18
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Summing up the largest terms per year in a dictionary


    Ok your results are exactly the same of my actual code …

    I asked your results of your largest 4 "ranks" in order to rewrite the rank engine in the code, so which way should I follow ?

    In case of yours, the results should be
    2005 : 8+5+5+4 = 22
    2006 : 9+9+7+6 = 31
    2008 : 5+3+2+2+2+2 = 16
    or not ?

    If I add 10 to your previous sample like 10, 10, 9, 9, 7, 7, 7, 6, 5 so which result is yours :
    1) My actual code : 10+10+9+9+7+7+7+6 = 65
    2) Like your explanation : 10+10+9+9 = 38 (this case returns exactly same result as largest Excel 4 ranks …)
    3) Other one if both previous are wrong !

    A) And what could be your expected result from 10, 10, 10, 9, 9, 7, 7, 7, 6, 5 ?
    B) And from 10, 10, 10, 10, 9, 9, 7, 7, 7, 6, 5 ?
    Last edited by Marc L; 03-25-2018 at 01:32 PM.

  11. #11
    Registered User
    Join Date
    03-22-2018
    Location
    Cologne, Germany
    MS-Off Ver
    2016
    Posts
    8

    Re: Summing up the largest terms per year in a dictionary

    My example was "wrong", which i figuered out when i read your post from yesterday.
    While i read something about the ranks i found some sources who said, that excel would skip the next rank, if the previous one is asigned twice. That's why I build the example that way.

    But your way is more meaningful!

    So my result should be 1). (your code)

    My result from A) should be 10+10+10+9+9+7+7+7+6=75
    My result from B) shoud be 10+10+10+10+9+9+7+7+7+6=85

    PS: In case of 2), my previous (wrong) example, the result would have differed from the largest four method, if the last rank is assigned twice or more. So in case 10,10,9,7,7,7,6,5,.. The sum of the largest four would be 10+10+9+7=36, but the sum of my first example with the four biggest ranks would be 10+10+9+7+7+7=50...But the right way -your way- would give the result 10+10+9+7+7+7+6=56

  12. #12
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this !


    Still without any dictionary, just with Excel basics like advanced filters, worksheet functions, sorts
    but this time as a starter all inclusive, from Table1 source worksheet to Table2 result worksheet :

    PHP Code: 
    Sub Demo2()
           
    Dim R&, S$, C%, L%, Rw As RangeP%
        
    With Tabelle2
              
    .UsedRange.Clear
               Application
    .ScreenUpdating False
          With Tabelle1
    .[A1].CurrentRegion
              
    .Columns(2).Copy Tabelle2.[A2]
               
    = .Rows.Count
               S 
    "'" & .Parent.Name "'!"
          
    End With
              
    .[A1].Value "YEAR"
              
    .[A1].CurrentRegion.AdvancedFilter xlFilterCopy, , .[C1], True
               C 
    = .[C1].CurrentRegion.Rows.Count 1
               L 
    Application.Min(4C)
              .[
    D1].Resize(, C).Value Application.Transpose(.[C2].Resize(C))
              .[
    D1].Resize(, C).Sort .[D1], xlAscendingOrientation:=xlLeftToRight
              
    .[A2].Resize(R).Formula "=VALUE(RIGHT(" "A1,4))"
              
    .[A1].CurrentRegion.AdvancedFilter xlFilterCopy, , .[C1], True
              
    .[C1].CurrentRegion.Columns(1).Sort .[C1], xlAscendingHeader:=xlYesOrientation:=xlTopToBottom
              
    .[A2].Resize(R).Formula "=RIGHT(" "A1,4)&" "B1"
         
    With .[D2].Resize(.[C1].End(xlDown)(0).RowC)
              .
    Formula "=COUNTIF($A$2:$A$" ",$C2&D$1)"
              
    .Formula = .Value
              
    .Parent.[A1].CurrentRegion.Clear
          
    For Each Rw In .Rows
               S 
    "SUM(LARGE(" Rw.Address(External:=True) & ",ROW(1:"
               
    0
           
    For 1 To L
               P 
    Application.CountIf(RwApplication.Large(Rw1))
            If 
    C Then Exit For
           
    Next
               Rw
    .Cells(1, -2).Resize(, 2).Value = Array(Evaluate(")))"), Evaluate(")))"))
          
    Next
         End With
         With 
    .UsedRange.Rows
              
    .VerticalAlignment xlCenter
              
    .Item(1).HorizontalAlignment xlCenter
         With 
    .Item("2:" & .Count):  .HorizontalAlignment xlRight:  .IndentLevel 2:  End With
         End With
            
    .[A1:B1].Value Split(Replace("# Values,# Ranks""#""Largest" vbLf L), ",")
        
    End With
               Application
    .ScreenUpdating True
    End Sub 
    Maybe a classic Excel inner feature like a dynamic cross table may do the job in a shorter way
    but I met an issue on my old tests computer & an old Excel version …

  13. #13
    Registered User
    Join Date
    03-22-2018
    Location
    Cologne, Germany
    MS-Off Ver
    2016
    Posts
    8

    Re: Summing up the largest terms per year in a dictionary

    Hi Marc,

    thank you! This works perfectly! You're a Lifesaver

    I only got problems, if there are many many many different terms I guess. (Maybe the Row was getting to long?) So i got "runtime error '1004': Application-defined or Object-defined error" at the point .[D1].Resize(,C).Value=Application.Transpose(.[C2].Resize(C)

    But I didn't want to torment you again, so I was searching for an answer using your code.
    That's the reason my answer took so long. I had to understand (most of) your second code.
    So I combined your two codes and used the ranges, you defined in your first code, as an input for your 'summing up code' in your second one and after some time and failures it worked!
    Now I still need my dictionary to get the input Data for this code, but this is ok. I adjusted the Cells, where the results are written down, too. Now they stand directly among each other in column G.

    The code now is:

    Please Login or Register  to view this content.
    Once again thank you so much for your time and your help!!! Sadly I couldn't give any more reputation to you :D

  14. #14
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Summing up the largest terms per year in a dictionary


    Thanks !

    Did you try first my code with your post #9 attachment ? As it rocks on my side ! …

    When the error occurs, what is the variable C value ?

  15. #15
    Registered User
    Join Date
    03-22-2018
    Location
    Cologne, Germany
    MS-Off Ver
    2016
    Posts
    8

    Re: Summing up the largest terms per year in a dictionary

    Hi Marc!
    Yes I tried your code with my attachment from post #9 and it worked perfectly!

    I guess my other file is to large, but you couldn't know that.
    When the error occurs the value from C is 866!

  16. #16
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Summing up the largest terms per year in a dictionary


    I tried something closed under Excel 2010 with C=900 and I met no issue …

    In Table2 column C is there any error in a cell ?

  17. #17
    Registered User
    Join Date
    03-22-2018
    Location
    Cologne, Germany
    MS-Off Ver
    2016
    Posts
    8

    Re: Summing up the largest terms per year in a dictionary

    Strange. I use Excel 2016 (Office 365).

    I looked up every cell in column C and they all look alright. But some terms in column C are very long, 200 signs or even more. But if i copy this long terms in my example file from post #9 it works.

    But as the combination of your two codes works, it's not that bad, that the other one won't work with my file...even if it would by interesting, why the runtime error occurs.

  18. #18
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb Max 255 chars …


    I reproduced your issue and it just seems to be a well-known Excel functions limitation …
    So if truncating up to 255 characters is allowable :

    PHP Code: 
    Sub Demo2a()
           
    Dim R&, S$, C%, L%, Rw As RangeP%
               
    Tabelle1.[A1].CurrentRegion.Rows.Count
               S 
    "'" Tabelle1.Name "'!"
        
    With Tabelle2
              
    .UsedRange.Clear
               Application
    .ScreenUpdating False
              
    .[A2].Resize(R).Formula "=LEFT(" "B1,251)"
              
    .[A1].Value "YEAR"
              
    .[A1].CurrentRegion.AdvancedFilter xlFilterCopy, , .[C1], True
               C 
    = .[C1].CurrentRegion.Rows.Count 1
               L 
    Application.Min(4C)
              .[
    D1].Resize(, C).Value Application.Transpose(.[C2].Resize(C))
              .[
    D1].Resize(, C).Sort .[D1], xlAscendingOrientation:=xlLeftToRight
              
    .[A2].Resize(R).Formula "=VALUE(RIGHT(" "A1,4))"
              
    .[A1].CurrentRegion.AdvancedFilter xlFilterCopy, , .[C1], True
              
    .[C1].CurrentRegion.Columns(1).Sort .[C1], xlAscendingHeader:=xlYesOrientation:=xlTopToBottom
              
    .[A2].Resize(R).Formula "=RIGHT(" "A1,4)&LEFT(" "B1,251)"
         
    With .[D2].Resize(.[C1].End(xlDown)(0).RowC)
              .
    Formula "=COUNTIF($A$2:$A$" ",$C2&D$1)"
              
    .Formula = .Value
              
    .Parent.[A1].CurrentRegion.Clear
          
    For Each Rw In .Rows
               S 
    "SUM(LARGE(" Rw.Address(External:=True) & ",ROW(1:"
               
    0
           
    For 1 To L
               P 
    Application.CountIf(RwApplication.Large(Rw1))
            If 
    C Then Exit For
           
    Next
               Rw
    .Cells(1, -2).Resize(, 2).Value = Array(Evaluate(")))"), Evaluate(")))"))
          
    Next
         End With
         With 
    .UsedRange.Rows
              
    .VerticalAlignment xlCenter
              
    .Item(1).HorizontalAlignment xlCenter
         With 
    .Item("2:" & .Count):  .HorizontalAlignment xlRight:  .IndentLevel 2:  End With
         End With
            
    .[A1:B1].Value Split(Replace("# Values,# Ranks""#""Largest" vbLf L), ",")
        
    End With
               Application
    .ScreenUpdating True
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

+ 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] Summing Largest 50 values based on criteria
    By Scooby5 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-27-2014, 10:38 PM
  2. Summing largest value per individual
    By domnisignis18 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-22-2012, 12:10 PM
  3. Matching Dictionary Terms with Definitions
    By luu980 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-22-2009, 02:08 PM
  4. Matching Dictionary Terms with Definitions
    By luu980 in forum Excel General
    Replies: 2
    Last Post: 03-09-2009, 02:56 PM
  5. Matching Dictionary Terms with Definitions
    By luu980 in forum Access Tables & Databases
    Replies: 0
    Last Post: 03-09-2009, 12:45 PM
  6. Create dictionary of terms, create first time user site
    By Solitaire Jane Austin in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-19-2006, 05:55 PM
  7. Summing the N Largest Values in a Range
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-28-2005, 11:18 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1