SOLVED

Formulas

Microsoft

We have a range where each categories has notes.

 

Please find the below condition where 1, 2 are the volumes.

CM- Account Credit (contl) category should be 1-Credit or 2-hold or 1-rejected and it has to throw error if the notes are not matching.

 

 Category Notes
CM- Account Credit (contl) 1-Credit or 1-hold or 1-rejected
CM - Account Transfer (contl) 2-transfer or 2- rejected
CM - Account Closed (util) Closed, Dormant
13 Replies

@resham1985  you can use "Data Validation" and choose "custom" and enter a formula like the following assuming B is category and C is note and you start validation in row 2:

=OR(AND(B2="category 1",OR(C2="comment 1",C2="comment 2",C2="comment 3")), AND(B2="category 2",OR(C2="comment 4",C2="comment 5")), AND(B2="category 3",OR(C2="comment 6",C2="comment 7")))

 

hopefully you can substitute the proper text in each case you want.

Thank you @mtarler for the response.

But the problem is notes or comments will not be same as it changes as per the volume

Notes for CM- Account Credit (contl) will be
volume processed-Credit
volume processed-rejected
volume processed-hold

Formula should be number-notes

@resham1985 

Please attach your sample file with data and desired results therein.

@resham1985 

You can use LOOKUP function and make this dynamic array.

LOOKUP(field to search, first column where search is being done, what to return when search is matched).

Try this and confirm if this is feasible solution.

@ShishirKumar 

 

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

@resham1985 

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.

@Sergei Baklan - There is space after hyphen, so it is error.

CM- Account Credit (contl) 1- credit Error

@resham1985  So if I understand correctly the note should have a number followed by "-Credit" or "-rejected" etc...  

I would recommend that you split those into 2 separate columns to make your life easier and the sheet more functional.  If you need a column with that specific format you can "build" it.  For example you have 1 col that is # and another column that is action (e.g. credit vs rejected etc...).  You can have specific controls on each of those columns.  The a 3rd col that would TEXTJOIN those 2 columns with an "-" or in the last case just where there are no numbers just use the later col.

 

That said, if you still want to limit the entry to only that format you can make the validation formula more complicated.  Here is an example where I changed the Data Validation formula above using the search function to make sure at least the non-numbered part is correct:

 

=OR(AND(B2="category 1",OR(search("comment 1",C2),search("comment 2",C2),search("comment 3",C2))), AND(B2="category 2",OR(search("comment 4",C2),search("comment 5",C2))), AND(B2="category 3",OR(search("comment 6",C2),search("comment 7",C2))))

 

 

best response confirmed by resham1985 (Microsoft)
Solution

@resham1985 

I'd also recommend to re-design right table a bit as

image.png

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

image.png

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")

@Twifoo 

 

Please find the attached file for your reference.

 

 Category Notes
CM- Account Credit (contl) 1-Credit
  1-hold 
  1-Debit
   
CM - Account Transfer (contl) 2-transfer or 2- rejected
CM - Account Closed (util) Closed, Dormant

 

For CM- Account Credit (contl) and CM - Account Transfer (contl)- notes should be number-Notes without spaces.

CM - Account Closed (util)- Notes should be without number(Closed or Dormant)

@resham1985  in the attached I have 5 'helper' columns of which 3 are hidden.  I have a # col and a comment type column and then a "Notes" column that compiles the # with the type.  The 3 hidden columns create a list of potential allowed comment types based on the category.  I added conditional formatting on the "Notes" column to check if something is missing and data validation on the # col and the type cols.  

This can all get merged into a single col but it just gets more complicated.

Thank you it works for me.

@resham1985 , you are welcome

1 best response

Accepted Solutions
best response confirmed by resham1985 (Microsoft)
Solution

@resham1985 

I'd also recommend to re-design right table a bit as

image.png

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

image.png

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")

View solution in original post