Announcement

Collapse
No announcement yet.

Find Last Repeated Value & Sum Value

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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

    Code:
    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, 12:42.
    I am not an expert. better solutions may be available. [email protected]$$$gmail.com

    Comment


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

      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

      Code:
      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

      Comment


      • #4
        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, 11:33.

        Comment


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

          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

          Comment


          • #6
            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?

            Comment


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

              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

              Comment


              • #8
                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
                Kris

                ExcelFox

                Comment


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

                  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

                  Comment


                  • #10
                    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
                    Kris

                    ExcelFox

                    Comment


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

                      Originally posted by Krishnakumar
                      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
                      That was A SWIFT RESPONSE AND A GREAT HELP!!!

                      Comment

                      Working...
                      X