Forum Discussion
Formulas
I'd also recommend to re-design right table a bit as
with Helper column calculated as
=TRIM(IFERROR( RIGHT( $H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100)), LEN($H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100))) - FIND("-",$H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100)))), $H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100))))Status could be calculated as
by formula
=IF(COUNTIFS( $G$3:INDEX($G$3:$G$100,COUNTA($G$3:$G$100)), $A2, $I$3:$I$9, IFERROR(RIGHT($B2,LEN($B2)-FIND("-",$B2)),$B2) ),"No Error", "Error")
Please find the attached sample file and help him to get the desired result.
Column A- Category
Column B- Notes
Column C- Manually I checked whether Category matching with the notes or not
Column D- Please add the formula to check whether the correct notes selected for correct category
Note: CM- Account Credit (contl) and CM - Account Transfer (contl)----Number-Notes( No Spaces)
For Category CM - Account Closed (contl)- notes should be without number ie Closed or Dormant
Could you please clarify the logic a bit more. For example, in left table
| CM- Account Credit (contl) | 1- credit | Error |
having 1-credit for this category in right table, but
| CM- Account Credit (contl) | 3-credit | No Error |
has no error. If we ignore numbers, both shall return No Error. If not, it shall be an opposite.
SergeiBaklan - There is space after hyphen, so it is error.
CM- Account Credit (contl) 1- credit Error I'd also recommend to re-design right table a bit as
with Helper column calculated as
=TRIM(IFERROR( RIGHT( $H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100)), LEN($H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100))) - FIND("-",$H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100)))), $H$3:INDEX($H$3:$H$100,COUNTA($H$3:$H$100))))Status could be calculated as
by formula
=IF(COUNTIFS( $G$3:INDEX($G$3:$G$100,COUNTA($G$3:$G$100)), $A2, $I$3:$I$9, IFERROR(RIGHT($B2,LEN($B2)-FIND("-",$B2)),$B2) ),"No Error", "Error")Thank you it works for me.