Link to home
Start Free TrialLog in
Avatar of suvmitra
suvmitraFlag for India

asked on

Find Discripency Excel

Hi,
Please find attached the excel file i am working.

I need a macro to do the below task.

if order no (COL B) is same and Invoice No (COL I) is same then check whether Invoice Line Nos (COl J) is in order and not missing say for example it should be like 1, 2, 3, 4 etc..

i only need to check whether if it is 1, 3, 4 order or like that where one or many number is missing from the sequence. Please help.
Test.xlsx
SOLUTION
Avatar of wchh
wchh

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of suvmitra

ASKER

I have tried some samples and find ssaqibh ' solution is providing more correct result..in different situations. Thank you all.
is it possible to have a VBA code for this formula ... also I think we should take reference for B column not A column ..pls let me know. Thanks.

=IF(J2=1,"",IF(SUMPRODUCT(($A$2:$A$9=A2)*($I$2:$I$9=I2)*($J$3:$J$10=J2-1))=0,"Previous record missing",""))
Try this sub

Sub polulateformula()
lr = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
ActiveSheet.Range("O2:O" & lr).FormulaR1C1 = _
"=IF(RC[-5]=1,"""",IF(SUMPRODUCT((R2C2:R" & lr & "C2=RC[-13])*(R2C9:R" & lr & "C9=RC[-6])*(R2C10:R" & lr & "C10=RC[-5]-1))=0,""Previous record missing"",""""))"
End Sub