1. ## 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

2. 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. 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. 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

5. 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. 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

7. 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. Thanks for the help, much appreciated

9. 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

10. Hi Zeddy