vlook up code

[dennisli]

I have two sheets named Sheet1 and Sheet2, each with two columns, first column is policy number which is unique, second column is payments.
All the Sheet1’s policy number can be found in Sheet2. In other words, the policy record in Sheet2 is always higher than Sheet1’s. And also the policy numbers in the two sheets are not necessary in the same rankings.
I want to create a new sheet named IndiClaim based on the following conditions:
For example, for policy number 10050, Sheet2 shows payment as 230, Sheet1 shows 190, the difference is 40. Then the policy number 10050, Sheet2 column 2 value and the difference 40 will copy value only to sheet IndiClaim’s column 1, 2 and 3;
For a new policy only shown on Sheet2, but if column 2 shows zero value, do not copy. However, if column 2 shows non zero value, copy the policy number and value to sheet IndiClaim’s column 1 and 2. At the same time, copy the second column value to column 3.
All the lookups, copy and paste range begin from row 8 to unidentified last rows.
I appreciate any help.
Dennis

 

[DonkeyOte]

is there any reason why you can't just use Sheet2 rather than creating a new sheet ?

on sheet2, cell C8 (copy down)

=IF($B8=0,0,IF(ISERROR(MATCH($A8,Sheet1!$A:$A,0)),$B8,$B8-VLOOKUP($A8,Sheet1!$A:$B,2,0)))

The only difference between this and the new sheet is that those listed with 0 values on sheet2 in B will still be listed, however, you could just use AutoFilter on Sheet 2 column B to hide values with value = 0.

 

[dennisli]

that's great. thanks for help.
For some other considerations, could you help me out with a code solution?
have a good day.
dennis