Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Comparing Account numbers

    I need to compare the account numbers in Col A to Col H


    1) where the account numbers are the same, then subtract the values in Col B from Col J in the same row for eg if A4 is KNR-3020 and H4 is KNR-3020, then subtract B4- J4

    2) If there is no match , then formula to return “Vat not claimed”

    I have attached sample data

    Your assistance is most appreciated
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,162
    Thanks
    47
    Thanked 976 Times in 906 Posts
    I expect entering this in a blank column on row 4 would do what you want.
    =if(A4=H4,B4-J4,"VAT not claimed")
    Of course it doesn't allow for white space and capitalization differences.

    cheers, Paul

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for the reply Paul. It would work if the account number in A4 is in the same row as H4

    However, the account numbers are not always in the same row as per my sample data, so the formula will not work in all situations

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Howard,

    Try this in D4 then copy down

    =IFERROR(IF(MATCH(LEFT(A4,8),$H$4:$H$24,0)>0,B4-INDEX($A$4:$J$24,MATCH(LEFT(A4,8),$H$4:$H$24,0),10 )),"Vat not claimed")

    You may have to format the column as a number.

    HTH,
    Maud
    Last edited by Maudibe; 2016-06-19 at 10:37. Reason: typo

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Howard,

    Here is a VBA approach using a UDF which offers greater flexibility using different columns

    In a standard module enter the following code:
    Code:
    Public Function COMPARE(comp1 As Range, comp2 As Range, col1 As Integer, col2 As Integer)
    Dim cell As Range, status As Boolean
    status = False
    For Each cell In comp2
        If Left(comp1, 8) = cell Then
            COMPARE = Cells(comp1.Row, col1) - Cells(cell.Row, col2)
            status = True
        End If
    Next cell
    If status = False Then COMPARE = "Vat not claimed"
    End Function
    In Cell D2 enter the following formula then copy down

    =COMPARE(A4,H4:H24,2,10)

    Syntax: =COMPARE(cell being sought, search range, minuend column, subtrahend column)

    Howard1.png

    HTH,
    Maud

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Maud

    Thanks for the help

    If the account in Col H are numbers , kindly amend your formula as well as your code

    See attached sample
    Attached Files Attached Files

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Howard<

    Amended formula by looking for the number prior to the first "-" in column A and converting from text to number:

    =IFERROR(IF(MATCH(VALUE(LEFT(A4,FIND("-",A4,1)-1)),$H$4:$H$24,0)>0,B4-INDEX($A$4:$J$24,MATCH(VALUE(LEFT(A4,FIND("-",A4,1)-1)),$H$4:$H$24,0),10 )),"Vat not claimed")

    Amended in line 5 converting text to number:
    Code:
    Public Function COMPARE(comp1 As Range, comp2 As Range, col1 As Integer, col2 As Integer)
    Dim cell As Range, status As Boolean
    status = False
    For Each cell In comp2
        If Val(Left(comp1, 6)) = cell Then
            COMPARE = Cells(comp1.Row, col1) - Cells(cell.Row, col2)
            status = True
        End If
    Next cell
    If status = False Then COMPARE = "Vat not claimed"
    End Function
    HTH,
    Maud

  8. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for the help, much appreciated

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Howard

    see attached file, based on your file in post#6

    Your Account codes in column [A] have a prefix ' apostrophe character.
    The formula assumes the account number is 6 digits.

    zeddy
    ..as posted from hospital bed
    Attached Files Attached Files

  10. #10
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for your help

    Hope you make a speedy recovery

    Howard

Posting Permissions

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