Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Formua to compute correct value based on account numbers

    I have set up a macro to copy data in sheet "Inc St" from N2 to the last row containing data in Col N to A2 on sheet "Input Acc numbers" and then the macro deletes blank cells

    I have tried to set up a formula on sheet "Input Acc numbers" to look up the values on the account numbers using Index & Match


    However, if the same account number appears more than once with different values, the formula result the same value for both account numbers


    I have attached my workbook


    It would be appreciated if someone could assist me
    Attached Files Attached Files
    Last edited by HowardC; 2016-07-01 at 11:39.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Howard,

    This can be achieved with formulas using a helper column on each sheet

    On sheet Inc St, place the following formula in Q2 then copy down: =COUNTIF($N2:$N$32,N2)
    This will provide an index for the input account numbers. The first instance will have an index of 1 while the second instance will have and index of 2 and so on.

    On sheet Input Account numbers, in cell D2 place the following formula then copy down: =COUNTIF($A2:$A$50,A2)
    This will achieve the same purpose as above but now indexing the account numbers on the second sheet

    For the meat and potatoes, look for an account number match with the same index.
    In cell B2 on the Input Account numbers sheet, place the following formula then copy down:
    =SUMIFS('Inc St'!F2:F32,'Inc St'!N2:N32,'Input Acc numbers'!A2,'Inc St'!Q2:Q32,'Input Acc numbers'!D2)

    HTH,
    Maud

    Howard1.png
    Attached Files Attached Files

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    HowardC (2016-07-01)

  4. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts
    Thanks very much for the help Maud, much appreciated

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •