suvmitra
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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$1 0=J2-1))=0 ,"Previous record missing",""))
=IF(J2=1,"",IF(SUMPRODUCT(
Try this sub
Sub polulateformula()
lr = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).En d(xlUp).Ro w
ActiveSheet.Range("O2:O" & lr).FormulaR1C1 = _
"=IF(RC[-5]=1,"""",IF(SUMP RODUCT((R2 C2:R" & lr & "C2=RC[-13])*(R2C9:R" & lr & "C9=RC[-6])*(R2C10:R" & lr & "C10=RC[-5]-1))=0,""Previo us record missing"",""""))"
End Sub
Sub polulateformula()
lr = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).En
ActiveSheet.Range("O2:O" & lr).FormulaR1C1 = _
"=IF(RC[-5]=1,"""",IF(SUMP
End Sub
ASKER