Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Find Last Repeated Value & Sum Value

  1. #1
    Join Date
    18th January 2005
    Posts
    64

    Find Last Repeated Value & Sum Value

    Hi,

    In the attached file column A has ID's. In the attached example i have used 2 ID's 141020061 & 151020062. I need to find the last entry of each ID and sum the value from the column F. that is the last entry for the ID 141020061 is 40500 and for 151020062 is 0 so the total should be 40500.

    Could any one of you please let me know the formula to do so??


    Thanks again.


    Always grateful to this forum members!!!!!
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    13th May 2006
    Location
    India
    Posts
    1,030

    Re: Find Last Repeated Value And Sum The Value In Another Column

    1. Keep that blank row between the data and the total
    2. sort the data according to ID
    3. then use this macro.Perhaps you get the result
    4. see comments in the macro

    VB:
    Sub lastitem() 
        Dim rng, c As Range 
        Dim x As Double 
        Set rng = Range([a2], [a2].End(xlDown).Offset(1, 0)) 
        x = 0 
        For Each c In rng 
            If c <> c.Offset(1, 0) Then 
                 'the next line is necessary because the last line
                 'is not blank but ocntains a string "$-"
                 'you can otherwise leave this cell entry as bllank
                If Cells(c.Row, "f") = "$-" Then Cells(c.Row, "f").Value = 0 
                x = x + Cells(c.Row, "f").Value 
            End If 
        Next 
        MsgBox x 
    End Sub 
    
    
    Last edited by Krishnakumar; November 7th, 2006 at 12:42.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    18th January 2005
    Posts
    64

    Re: Find Last Repeated Value And Sum The Value In Another Column

    Quote Originally Posted by venkat1926
    1. Keep that blank row between the data and the total
    2. sort the data according to ID
    3. then use this macro.Perhaps you get the result
    4. see comments in the macro

    VB:
    Sub lastitem() 
        Dim rng, c As Range 
        Dim x As Double 
        Set rng = Range([a2], [a2].End(xlDown).Offset(1, 0)) 
        x = 0 
        For Each c In rng 
            If c <> c.Offset(1, 0) Then 
                 'the next line is necessary because the last line
                 'is not blank but ocntains a string "$-"
                 'you can otherwise leave this cell entry as bllank
                If Cells(c.Row, "f") = "$-" Then Cells(c.Row, "f").Value = 0 
                x = x + Cells(c.Row, "f").Value 
            End If 
        Next 
        MsgBox x 
    End Sub 
    
    
    The above code is giving an error!!!! and also, i dont want to sort the data before calculating the values

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    25th April 2006
    Posts
    1,195

    Re: Find Last Repeated Value And Sum The Value In Another Column

    You say that you want to find the last instance of an ID and sum column F, but in your sample nothing is summed. Do you mean that you want to find the last instace of an ID and return the corresponding value in column F?
    Last edited by bryce; November 7th, 2006 at 11:33.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th January 2005
    Posts
    64

    Re: Find Last Repeated Value And Sum The Value In Another Column

    Quote Originally Posted by bryce
    You say that you want to find the last instance of an ID and sum column F, but in your sample nothing is summed. Do you mean that you want to find the last instace of an ID and return the corresponding value in column F?
    Well, the value in the cell F7 it is 40500 which is the sum of 40500(last occurance of ID 141020061) + 0 ( last occurance of ID 151020062) = 40500.

    So, i need to sum the last occurance of each ID.

    Thanks for your reply

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    25th April 2006
    Posts
    1,195

    Re: Find Last Repeated Value And Sum The Value In Another Column

    I still dont understand. Why look for an ID if you plan to sum any ID below it?

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    18th January 2005
    Posts
    64

    Re: Find Last Repeated Value And Sum The Value In Another Column

    Quote Originally Posted by bryce
    I still dont understand. Why look for an ID if you plan to sum any ID below it?
    It is summing the corresponding values of the last occurance of a particular ID. Let me try to explain you.

    for each transaction there could be multiple times the money is repaid so, each time the money is repaid a new entry is made. In the column F it show's the amount remainin of that particular transaction at that particular point. By summing i would get the total amount remaining to be repaid to me.

    I hope that explains

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Find Last Repeated Value And Sum The Value In Another Column

    Hi,

    In H2 and copied down,

    =(COUNTIF($A$2:$A$5,A2)=COUNTIF($A$2:A2,A2))+0

    In F7,

    =SUMIF(H2:H5,1,F2:F5)

    HTH

  9. #9
    Join Date
    18th January 2005
    Posts
    64

    Re: Find Last Repeated Value And Sum The Value In Another Column

    Quote Originally Posted by Krishnakumar
    Hi,

    In H2 and copied down,

    =(COUNTIF($A$2:$A$5,A2)=COUNTIF($A$2:A2,A2))+0

    In F7,

    =SUMIF(H2:H5,1,F2:F5)

    HTH
    Hi Kris,

    The formula worked for the previously attached example. but when i added one more row with the id 141020061 the formula failed. it is still showing 40500 when it should be showing 20500 as per the new row entered any other suggestions would be appreciated very much.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Find Last Repeated Value And Sum The Value In Another Column

    Just expand the range A5 to A6. If you want to make the range dynamic then define ID

    and refers to box: =Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(9.9999999999E+307,Sheet1!$A:$A))

    In H2 and copied down,

    =(COUNTIF(ID,A2)=COUNTIF($A$2:A2,A2))+0

    HTH

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Location Of Repeated Enteries
    By darylb in forum Excel General
    Replies: 1
    Last Post: March 18th, 2008, 12:41
  2. Repeated Combinations After Sorting
    By Ratiocination in forum Excel General
    Replies: 3
    Last Post: January 29th, 2008, 09:13
  3. Repeated Names in an Array
    By Kevin900 in forum Excel General
    Replies: 5
    Last Post: May 26th, 2006, 02:37
  4. Repeated equation
    By ELM in forum Excel General
    Replies: 1
    Last Post: March 19th, 2005, 00:12
  5. problem in repeated web query
    By diablo in forum Excel General
    Replies: 4
    Last Post: September 16th, 2003, 02:38

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
  •  
porno