Results 1 to 10 of 10
Thread: Comparing Account numbers

20160619, 02:32 #1
 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 KNR3020 and H4 is KNR3020, 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

20160619, 02:56 #2
 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,B4J4,"VAT not claimed")
Of course it doesn't allow for white space and capitalization differences.
cheers, Paul

20160619, 04:24 #3
 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

20160619, 09:36 #4
 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,B4INDEX($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,
MaudLast edited by Maudibe; 20160619 at 10:37. Reason: typo

20160619, 10:36 #5
 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
=COMPARE(A4,H4:H24,2,10)
Syntax: =COMPARE(cell being sought, search range, minuend column, subtrahend column)
Howard1.png
HTH,
Maud

20160619, 12:46 #6
 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

20160619, 13:56 #7
 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,B4INDEX($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
Maud

20160620, 02:01 #8
 Join Date
 Feb 2008
 Posts
 1,420
 Thanks
 124
 Thanked 5 Times in 5 Posts
Thanks for the help, much appreciated

20160620, 04:01 #9
 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

20160620, 12:35 #10
 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